def oracle(request):
connectstring = os.environ.get("LL_ORASQL_TEST_CONNECT")
if connectstring:
from ll import orasql
db = orasql.connect(connectstring, readlobs=True)
cursor = db.cursor()
def run(code):
cursor.execute("""
create or replace function ul4ontest
return clob
as
c_out clob;
begin
{}
return c_out;
end;
""".format(code))
cursor.execute("select ul4ontest from dual")
dump = cursor.fetchone().ul4ontest
return ul4on.loads(dump)
return run
else:
return None
def _make(self):
self._objects = {}
db = orasql.connect(self.dbname)
# get all definitions
# (this tests that :meth:`objects`, :meth:`references` and :meth:`referencedby` run to completion)
for obj in db.objects(None):
if obj.owner is None and "$" not in obj.name:
self._objects[obj] = Info(obj)
def _make(self):
self._objects = {}
db = orasql.connect(self.dbname)
# get all definitions
# (this tests that :meth:`objects`, :meth:`references` and :meth:`referencedby` run to completion)
for obj in db.objects(None):
if obj.owner is None and "$" not in obj.name:
self._objects[obj] = Info(obj)
def _getconnection(self):
if self.pool is not None:
return self.pool.acquire()
elif self._connection is None:
self._connection = orasql.connect(self.connectstring,
threaded=True,
**self.kwargs)
return self._connection
def main(args=None):
p = argparse.ArgumentParser(description="Recreate/rebuild all indexes/unique constraints in an Oracle database schema", epilog="For more info see http://python.livinglogic.de/orasql_scripts_orareindex.html")
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
p.add_argument("-r", "--rebuild", dest="rebuild", help="Rebuild indexes instead of recreating them? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-x", "--execute", dest="execute", action=misc.FlagAction, help="immediately execute the commands instead of printing them? (default %(default)s)")
p.add_argument( "--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql")
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection = orasql.connect(args.connectstring)
cursor = connection.cursor()
cs = s4connectstring(connection.connectstring())
for (i, obj) in enumerate(itertools.chain(orasql.Index.objects(connection, owner=None), orasql.UniqueConstraint.objects(connection, owner=None))):
rebuild = args.rebuild and isinstance(obj, orasql.Index)
# Progress report
if args.verbose:
stderr.writeln("orareindex.py: ", cs, f": {'Rebuilding' if rebuild else 'Recreating'} #{i+1:,} ", s4object(str(obj)))
if rebuild:
if args.execute:
cursor.execute(obj.rebuildsql(term=False))
else:
stdout.writeln(obj.rebuildsql(term=True).strip())
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
stdout.writeln()
else:
if args.execute:
sql = obj.createsql(term=False)
cursor.execute(obj.dropsql(term=False))
cursor.execute(sql)
else:
stdout.writeln(obj.dropsql(term=True).strip())
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
stdout.writeln()
stdout.writeln(obj.createsql(term=True).strip())
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
stdout.writeln()
def main(args=None):
p = argparse.ArgumentParser(description="Search for a string in all fields of all tables in an Oracle database schema", epilog="For more info see http://python.livinglogic.de/orasql_scripts_orafind.html")
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("searchstring", help="String to search for")
p.add_argument("tables", metavar="table", nargs="*", help="Limit search to those tables")
p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default: %(default)s)", action=misc.FlagAction, default=False)
p.add_argument("-c", "--color", dest="color", help="Color output (default: %(default)s)", default="auto", choices=("yes", "no", "auto"))
p.add_argument("-i", "--ignore-case", dest="ignorecase", help="Ignore case distinctions? (default: %(default)s)", action=misc.FlagAction, default=False)
p.add_argument("-r", "--read-lobs", dest="readlobs", help="Read CLOBs when printing records? (default: %(default)s)", action=misc.FlagAction, default=False)
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
searchstring = args.searchstring
if args.ignorecase:
searchstring = searchstring.lower()
searchstring = searchstring.replace("%", "%%")
searchstring = f"%{searchstring}%"
tablenames = [name.lower() for name in args.tables]
connection = orasql.connect(args.connectstring, readlobs=args.readlobs)
c = connection.cursor()
tables = list(connection.tables(None))
for (i, table) in enumerate(tables):
skip = tablenames and table.name.lower() not in tablenames
if args.verbose:
msg = "skipped" if skip else "searching"
stderr.writeln("orafind.py: ", df(table), " #", str(i+1), "/", str(len(tables)), ": ", msg)
if not skip:
where = []
for col in table.columns():
datatype = col.datatype()
if datatype == "clob" or datatype.startswith("varchar2"):
if args.ignorecase:
where.append(f"lower({col.name}) like :searchstring")
else:
where.append(f"{col.name} like :searchstring")
if not where:
continue # no string columns
where = " or ".join(where)
query = f"select * from {table.name} where {where}"
c.execute(query, searchstring=searchstring)
for r in c:
stdout.writeln("orafind.py: in ", df(table), ": ", repr(r))
return 0
def cleanup():
with orasql.connect(dbname) as db:
c = db.cursor()
try:
c.execute("drop table pysql_test_table")
except cx_Oracle.DatabaseError:
pass
try:
c.execute("drop sequence pysql_test_sequence")
except cx_Oracle.DatabaseError:
pass
def oracle(self, connectstring):
"""
Return an :class:`OracleConnection` object for the Oracle connect string
passed in::
<?code db = globals.oracle("user/password@database")?>
<?for row in db.query("select sysdate as sd from dual")?>
<?print row.sd?>
<?end for?>
"""
from ll import orasql
return OracleConnection(orasql.connect(connectstring, readlobs=True))
def oracle(self, connectstring):
"""
Return an :class:`OracleConnection` object for the Oracle connect string
passed in::
<?code db = globals.oracle("user/password@database")?>
<?for row in db.query("select sysdate as sd from dual")?>
<?print row.sd?>
<?end for?>
"""
from ll import orasql
return OracleConnection(orasql.connect(connectstring, readlobs=True))
def test_fetch(db_data):
for obj in db_data.objects():
if isinstance(obj, orasql.Table):
# fetch only a few records
db = orasql.connect(dbname)
c = db.cursor()
c.execute(f"select * from {obj.name}")
c.readlobs = False
c.fetchone()
c.execute(f"select * from {obj.name}")
c.readlobs = True
c.fetchone()
break
def test_callprocedure():
if dbname:
db = orasql.connect(dbname)
proc = db.getobject("orasql_testprocedure")
result = proc(db.cursor(readlobs=True), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert result.p_in == "abcäöü"
assert result.p_out == "ABCÄÖÜ"
assert result.p_inout == "ABC"*10000 + "abcäöü"
result = proc(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert result.p_in == "abcäöü"
assert result.p_out == "ABCÄÖÜ"
assert readlob(result.p_inout, 8192) == "ABC"*10000 + "abcäöü"
def test_clob_fromprocedure():
if dbname:
db = orasql.connect(dbname)
proc = db.getobject("orasql_testprocedure")
def check(sizearg):
result = proc(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert readlob(result.p_inout, sizearg) == "ABC"*10000 + "abcäöü"
assert result.p_inout.read() == ""
check(8192)
check(0)
check(None)
def test_callprocedure():
if dbname:
db = orasql.connect(dbname)
proc = db.getobject("orasql_testprocedure")
result = proc(db.cursor(readlobs=True), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert result.p_in == "abcäöü"
assert result.p_out == "ABCÄÖÜ"
assert result.p_inout == "ABC"*10000 + "abcäöü"
result = proc(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert result.p_in == "abcäöü"
assert result.p_out == "ABCÄÖÜ"
assert readlob(result.p_inout, 8192) == "ABC"*10000 + "abcäöü"
def test_fetch(db_data):
for obj in db_data.objects():
if isinstance(obj, orasql.Table):
# fetch only a few records
db = orasql.connect(dbname)
c = db.cursor()
c.execute(f"select * from {obj.name}")
c.readlobs = False
c.fetchone()
c.execute(f"select * from {obj.name}")
c.readlobs = True
c.fetchone()
break
def test_clob_fromprocedure():
if dbname:
db = orasql.connect(dbname)
proc = db.getobject("orasql_testprocedure")
def check(sizearg):
result = proc(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert readlob(result.p_inout, sizearg) == "ABC"*10000 + "abcäöü"
assert result.p_inout.read() == ""
check(8192)
check(0)
check(None)
def test_callfunction():
if dbname:
db = orasql.connect(dbname)
func = db.getobject("orasql_testfunction")
(result, args) = func(db.cursor(readlobs=True), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert result == "ABCÄÖÜ"
assert args.p_in == "abcäöü"
assert args.p_out == "ABCÄÖÜ"
assert args.p_inout == "ABC"*10000 + "abcäöü"
(result, args) = func(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert result == "ABCÄÖÜ"
assert args.p_in == "abcäöü"
assert args.p_out == "ABCÄÖÜ"
assert readlob(args.p_inout, 8192) == "ABC"*10000 + "abcäöü"
def test_callfunction():
if dbname:
db = orasql.connect(dbname)
func = db.getobject("orasql_testfunction")
(result, args) = func(db.cursor(readlobs=True), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert result == "ABCÄÖÜ"
assert args.p_in == "abcäöü"
assert args.p_out == "ABCÄÖÜ"
assert args.p_inout == "ABC"*10000 + "abcäöü"
(result, args) = func(db.cursor(readlobs=False), c_user="******", p_in="abcäöü", p_inout="abc"*10000)
assert result == "ABCÄÖÜ"
assert args.p_in == "abcäöü"
assert args.p_out == "ABCÄÖÜ"
assert readlob(args.p_inout, 8192) == "ABC"*10000 + "abcäöü"
def test_decimal():
if dbname:
db = orasql.connect(dbname, decimal=True)
c = db.cursor()
c.execute("select 42 from dual")
r = c.fetchone()
assert type(r[0]) is decimal.Decimal
c.execute("select 42.5 from dual")
r = c.fetchone()
assert type(r[0]) is decimal.Decimal
c.execute("select cast(42 as integer) from dual")
r = c.fetchone()
assert type(r[0]) is int
def test_decimal():
if dbname:
db = orasql.connect(dbname, decimal=True)
c = db.cursor()
c.execute("select 42 from dual")
r = c.fetchone()
assert type(r[0]) is decimal.Decimal
c.execute("select 42.5 from dual")
r = c.fetchone()
assert type(r[0]) is decimal.Decimal
c.execute("select cast(42 as integer) from dual")
r = c.fetchone()
assert type(r[0]) is int
def oracle_ul4onbuffer(code):
"""
A test fixture that will execute the PL/SQL code passed in as a parameter.
This PL/SQL code must output an UL4ON dump into the PL/SQL variable ``c_out``
by using the ``UL4ONBUFFER_PKG`` package. The package name is available as
the function attribute ``pkg``.
:func:`oracle_ul4onbuffer` returns the deserialized object dump as a Python
object.
For example::
oracle('''
{oracle.pkg}.begindict(c_out);
{oracle.pkg}.enddict(c_out);
''')
should return a empty dictionary.
Note that call to ``UL4ONBUFFER_PKG.INIT()`` and ``UL4ONBUFFER_PKG.FLUSH()``
are not required in the code passed in (this makes it possible to call
:func:`oracle_ul4on` and :func:`oracle_ul4onbuffer` with the code).
"""
connectstring = os.environ.get("LL_ORASQL_TEST_CONNECT")
if connectstring:
from ll import orasql
db = orasql.connect(connectstring, readlobs=True)
cursor = db.cursor()
cursor.execute(f"""
create or replace function ul4ontest
return clob
as
c_out clob;
begin
ul4onbuffer_pkg.init(c_out);
{code}
ul4onbuffer_pkg.flush(c_out);
return c_out;
end;
""")
cursor.execute("select ul4ontest from dual")
dump = cursor.fetchone().ul4ontest
return ul4on.loads(dump)
else:
return None
def oracle_ul4onbuffer(code):
"""
A test fixture that will execute the PL/SQL code passed in as a parameter.
This PL/SQL code must output an UL4ON dump into the PL/SQL variable ``c_out``
by using the ``UL4ONBUFFER_PKG`` package. The package name is available as
the function attribute ``pkg``.
:func:`oracle_ul4onbuffer` returns the deserialized object dump as a Python
object.
For example::
oracle('''
{oracle.pkg}.begindict(c_out);
{oracle.pkg}.enddict(c_out);
''')
should return a empty dictionary.
Note that call to ``UL4ONBUFFER_PKG.INIT()`` and ``UL4ONBUFFER_PKG.FLUSH()``
are not required in the code passed in (this makes it possible to call
:func:`oracle_ul4on` and :func:`oracle_ul4onbuffer` with the code).
"""
connectstring = os.environ.get("LL_ORASQL_TEST_CONNECT")
if connectstring:
from ll import orasql
db = orasql.connect(connectstring, readlobs=True)
cursor = db.cursor()
cursor.execute(f"""
create or replace function ul4ontest
return clob
as
c_out clob;
begin
ul4onbuffer_pkg.init(c_out);
{code}
ul4onbuffer_pkg.flush(c_out);
return c_out;
end;
""")
cursor.execute("select ul4ontest from dual")
dump = cursor.fetchone().ul4ontest
return ul4on.loads(dump)
else:
return None
def oracle_ul4on(code):
"""
A test fixture that will execute the PL/SQL code passed in as a parameter.
This PL/SQL code must output an UL4ON dump into the PL/SQL variable ``c_out``
by using the ``UL4ON_PKG`` package. The package name is available as the
function attribute ``pkg``.
:func:`oracle_ul4on` returns the deserialized object dump as a Python object.
For example::
oracle('''
{oracle.pkg}.begindict(c_out);
{oracle.pkg}.enddict(c_out);
''')
should return a empty dictionary.
"""
connectstring = os.environ.get("LL_ORASQL_TEST_CONNECT")
if connectstring:
from ll import orasql
db = orasql.connect(connectstring, readlobs=True)
cursor = db.cursor()
print(code)
cursor.execute(f"""
create or replace function ul4ontest
return clob
as
c_out clob;
begin
{code}
return c_out;
end;
""")
cursor.execute("select ul4ontest from dual")
dump = cursor.fetchone().ul4ontest
return ul4on.loads(dump)
else:
return None
def oracle_ul4on(code):
"""
A test fixture that will execute the PL/SQL code passed in as a parameter.
This PL/SQL code must output an UL4ON dump into the PL/SQL variable ``c_out``
by using the ``UL4ON_PKG`` package. The package name is available as the
function attribute ``pkg``.
:func:`oracle_ul4on` returns the deserialized object dump as a Python object.
For example::
oracle('''
{oracle.pkg}.begindict(c_out);
{oracle.pkg}.enddict(c_out);
''')
should return a empty dictionary.
"""
connectstring = os.environ.get("LL_ORASQL_TEST_CONNECT")
if connectstring:
from ll import orasql
db = orasql.connect(connectstring, readlobs=True)
cursor = db.cursor()
print(code)
cursor.execute(f"""
create or replace function ul4ontest
return clob
as
c_out clob;
begin
{code}
return c_out;
end;
""")
cursor.execute("select ul4ontest from dual")
dump = cursor.fetchone().ul4ontest
return ul4on.loads(dump)
else:
return None
def test_pysql(tmpdir):
cleanup()
execute_commands(commands, f"{tmpdir}/")
with orasql.connect(dbname) as db:
c = db.cursor()
c.execute("select odtt_id from pysql_test_table order by odtt_id")
data = [int(r.odtt_id) for r in c]
assert data == [1, 101]
c.execute("select pysql_test_sequence.nextval as nv from dual")
data = c.fetchone().nv
assert data == 111
f = tmpdir.join("gurk_file.txt")
assert f.read() == "gurk_file"
stat = os.stat(str(f))
assert stat.st_mode & 0o777 == 0o644
f2 = tmpdir.join("gurk_scp.txt")
assert f2.read() == "gurk_scp"
cleanup()
def test_procedure_nonexistant():
if dbname:
db = orasql.connect(dbname)
with pytest.raises(orasql.SQLObjectNotFoundError):
orasql.Procedure("DOESNOTEXIST")(db.cursor())
def test_connection_users(): db = orasql.connect(dbname) list(db.users())
def test_connection_privileges(): db = orasql.connect(dbname) list(db.privileges(None))
def test_exists():
if dbname:
db = orasql.connect(dbname)
assert orasql.Procedure("ORASQL_TESTPROCEDURE").exists(db)
assert not orasql.Procedure("ORASQL_NOTTESTPROCEDURE").exists(db)
def test_exists():
if dbname:
db = orasql.connect(dbname)
assert orasql.Procedure("ORASQL_TESTPROCEDURE").exists(db)
assert not orasql.Procedure("ORASQL_NOTTESTPROCEDURE").exists(db)
def main(args=None):
p = argparse.ArgumentParser(description="Print (or execute) drop statements for all objects in an Oracle database schema", epilog="For more info see http://python.livinglogic.de/orasql_scripts_oradrop.html")
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", action=misc.FlagAction, default=False)
p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
p.add_argument("-f", "--fks", dest="fks", help="How should foreign keys from other schemas be treated? (default %(default)s)", default="disable", choices=("keep", "disable", "drop"))
p.add_argument("-x", "--execute", dest="execute", help="immediately execute the commands instead of printing them? (default %(default)s)", action=misc.FlagAction, default=False)
p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' in their name? (default %(default)s)", action=misc.FlagAction, default=False)
p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument( "--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql")
p.add_argument( "--include", dest="include", metavar="REGEXP", help="Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
p.add_argument( "--exclude", dest="exclude", metavar="REGEXP", help="Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection = orasql.connect(args.connectstring)
term = not args.execute
cs = s4connectstring(connection.connectstring())
def keep(obj):
if obj.owner is not None and not isinstance(obj, orasql.ForeignKey):
return False
if ("$" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_")) and not args.keepjunk:
return False
if args.include is not None and args.include.search(obj.name) is None:
return False
if args.exclude is not None and args.exclude.search(obj.name) is not None:
return False
return True
sqls = []
for (i, obj) in enumerate(connection.objects(owner=None, mode="drop")):
keepdef = keep(obj)
# Get SQL
sql = ""
action = "skipped"
if obj.owner is not None:
if isinstance(obj, orasql.ForeignKey):
if args.fks == "disable":
sql = obj.disablesql(connection, term)
action = "disabled"
elif args.fks == "drop":
sql = obj.dropsql(connection, term)
action = None
elif keepdef:
sql = obj.dropsql(connection, term)
action = None
# Progress report
if args.verbose:
msg = astyle.style_default("oradrop.py: ", cs, f": fetching #{i+1:,} ", s4object(str(obj)))
if action is not None:
msg = astyle.style_default(msg, " ", s4warning(f"({action})"))
stderr.writeln(msg)
if sql:
# Print or execute sql
if args.execute:
sqls.append((obj, sql))
else:
stdout.writeln(sql.strip())
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
stdout.writeln()
# Execute SQL
if args.execute:
cursor = connection.cursor()
for (i, (obj, sql)) in enumerate(sqls):
if args.verbose:
stderr.writeln("oradrop.py: ", cs, f": dropping #{i+1:,}/{len(sqls):,} ", s4object(str(obj)))
try:
cursor.execute(sql)
except orasql.DatabaseError as exc:
if not args.ignore or "ORA-01013" in str(exc):
raise
stderr.writeln("oradrop.py: ", s4error(f"{exc.__class__}: {str(exc).strip()}"))
def test_connection_privileges(): db = orasql.connect(dbname) list(db.privileges(None))
def main(args=None):
p = argparse.ArgumentParser(description="compare two Oracle database schemas", epilog="For more info see http://python.livinglogic.de/orasql_scripts_oradiff.html")
p.add_argument("connectstring1", help="First schema")
p.add_argument("connectstring2", help="Second schema")
p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
p.add_argument("-m", "--mode", dest="mode", help="Output mode (default %(default)s)", default="udiff", choices=("brief", "udiff", "full"))
p.add_argument( "--format", dest="format", help="The output format for 'full' mode (default %(default)s)", choices=("sql", "pysql"), default="sql")
p.add_argument("-n", "--context", dest="context", help="Number of context lines (default %(default)s)", type=int, default=2)
p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-b", "--blank", dest="blank", help="How to treat whitespace (default %(default)s)", default="literal", choices=("literal", "trail", "lead", "both", "collapse"))
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection1 = orasql.connect(args.connectstring1)
connection2 = orasql.connect(args.connectstring2)
def fetch(connection, name, mode="flat"):
objectset = set()
objectlist = []
def keep(obj):
if obj.owner is not None:
return False
if args.keepjunk:
return True
if "$" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_"):
return False
return True
for (i, obj) in enumerate(connection.objects(owner=None, mode=mode)):
keepdef = keep(obj)
if args.verbose:
msg = astyle.style_default("oradiff.py: ", cs(connection), connid(name), f": fetching #{i+1:,} ", df(obj))
if not keepdef:
msg = astyle.style_default(msg, " ", s4warning("(skipped)"))
stderr.writeln(msg)
if keepdef:
objectset.add(obj)
objectlist.append(obj)
return (objectset, objectlist)
(objectset1, objectlist1) = fetch(connection1, 1, mode="drop")
(objectset2, objectlist2) = fetch(connection2, 2, mode="create")
# If we output in full mode the resulting SQL script should be usable, so
# we try to iterate the object in the appropriate order
allobjects = objectset1 | objectset2
count = 1
# Objects in database 2
for obj in objectlist2:
# Objects only in database 2
if obj not in objectset1:
if args.verbose:
stderr.writeln("oradiff.py: only in ", cs(connection2), f" #{count:,}/{len(allobjects):,} ", df(obj))
if args.mode == "brief":
stdout.writeln(df(obj), ": only in ", cs(connection2))
elif args.mode == "full":
stdout.writeln(comment(df(obj), ": only in ", cs(connection2)))
sql = obj.createsql(connection2, term=True).strip()
if sql:
stdout.writeln(sql)
stdout.writeln()
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
elif args.mode == "udiff":
sql = getcanonicalsql(obj.createsql(connection2), args.blank)
showudiff(stdout, obj, [], sql, connection1, connection2, args.context)
else:
if args.verbose:
stderr.writeln(f"oradiff.py: diffing #{count:,}/{len(allobjects):,} ", df(obj))
sql1 = obj.createsql(connection1)
sql2 = obj.createsql(connection2)
sql1c = getcanonicalsql(sql1, args.blank)
sql2c = getcanonicalsql(sql2, args.blank)
if sql1c != sql2c:
if args.mode == "brief":
stdout.writeln(df(obj), ": different")
elif args.mode == "full":
stdout.writeln(comment(df(obj), ": different"))
sql = obj.createsql(connection2).strip()
stdout.writeln(sql)
stdout.writeln()
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
elif args.mode == "udiff":
showudiff(stdout, obj, sql1c, sql2c, connection1, connection2, args.context)
count += 1
# Objects only in database 1
for obj in objectlist1:
if obj not in objectset2:
if args.verbose:
stderr.writeln("oradiff.py: only in ", cs(connection1), f" #{count:,}/{len(allobjects):,} ", df(obj))
if args.mode == "brief":
stdout.writeln(df(obj), ": only in ", cs(connection1))
elif args.mode == "full":
stdout.writeln(comment(df(obj), ": only in ", cs(connection1)))
sql = obj.dropsql(connection1, term=True).strip()
if sql:
stdout.writeln(sql)
stdout.writeln()
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
elif args.mode == "udiff":
sql = getcanonicalsql(obj.createsql(connection1), args.blank)
showudiff(stdout, obj, sql, [], connection1, connection2, args.context)
count += 1
def test_connection_fks(): db = orasql.connect(dbname) list(db.fks(None))
def test_connection_tables(): db = orasql.connect(dbname) list(db.tables(None))
def test_connect(): db = orasql.connect(dbname) assert isinstance(db, orasql.Connection)
def main(args=None):
p = argparse.ArgumentParser(
description=
"Search for a string in all fields of all tables in an Oracle database schema",
epilog=
"For more info see http://python.livinglogic.de/orasql_scripts_orafind.html"
)
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("searchstring", help="String to search for")
p.add_argument("tables",
metavar="table",
nargs="*",
help="Limit search to those tables")
p.add_argument("-v",
"--verbose",
dest="verbose",
help="Give a progress report? (default: %(default)s)",
action=misc.FlagAction,
default=False)
p.add_argument("-c",
"--color",
dest="color",
help="Color output (default: %(default)s)",
default="auto",
choices=("yes", "no", "auto"))
p.add_argument("-i",
"--ignore-case",
dest="ignorecase",
help="Ignore case distinctions? (default: %(default)s)",
action=misc.FlagAction,
default=False)
p.add_argument(
"-r",
"--read-lobs",
dest="readlobs",
help="Read CLOBs when printing records? (default: %(default)s)",
action=misc.FlagAction,
default=False)
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
searchstring = args.searchstring
if args.ignorecase:
searchstring = searchstring.lower()
searchstring = searchstring.replace("%", "%%")
searchstring = f"%{searchstring}%"
tablenames = [name.lower() for name in args.tables]
connection = orasql.connect(args.connectstring, readlobs=args.readlobs)
c = connection.cursor()
tables = list(connection.tables(None))
for (i, table) in enumerate(tables):
skip = tablenames and table.name.lower() not in tablenames
if args.verbose:
msg = "skipped" if skip else "searching"
stderr.writeln("orafind.py: ", df(table), " #", str(i + 1), "/",
str(len(tables)), ": ", msg)
if not skip:
where = []
for col in table.columns():
datatype = col.datatype()
if datatype == "clob" or datatype.startswith("varchar2"):
if args.ignorecase:
where.append(f"lower({col.name}) like :searchstring")
else:
where.append(f"{col.name} like :searchstring")
if not where:
continue # no string columns
where = " or ".join(where)
query = f"select * from {table.name} where {where}"
c.execute(query, searchstring=searchstring)
for r in c:
stdout.writeln("orafind.py: in ", df(table), ": ", repr(r))
return 0
def test_job_classes(): if dbname: db = orasql.connect(dbname) for obj in orasql.JobClass.objects(db): obj.createsql() obj.dropsql()
def test_job_classes(): if dbname: db = orasql.connect(dbname) for obj in orasql.JobClass.objects(db): obj.createsql() obj.dropsql()
def test_connection_users(): db = orasql.connect(dbname) list(db.users())
def test_procedure_nonexistent():
if dbname:
db = orasql.connect(dbname)
with pytest.raises(orasql.SQLObjectNotFoundError):
orasql.Procedure("DOESNOTEXIST")(db.cursor())
def main(args=None):
p = argparse.ArgumentParser(description="Print (or execute) the SQL of all objects in an Oracle database schema", epilog="For more info see http://python.livinglogic.de/orasql_scripts_oracreate.html")
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
p.add_argument("-s", "--seqcopy", dest="seqcopy", help="copy sequence values? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-x", "--execute", metavar="CONNECTSTRING2", dest="execute", help="Execute in target database")
p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument( "--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql")
p.add_argument( "--include", dest="include", metavar="REGEXP", help="Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
p.add_argument( "--exclude", dest="exclude", metavar="REGEXP", help="Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection = orasql.connect(args.connectstring)
if args.execute:
connection2 = orasql.connect(args.execute)
cursor2 = connection2.cursor()
term = False
else:
term = True
cs1 = s4connectstring(connection.connectstring())
if args.execute:
cs2 = s4connectstring(connection2.connectstring())
def keep(obj):
if obj.owner is not None:
return False
# output pk, fks etc. only when they belong to a table we do output
if isinstance(obj, (orasql.Constraint, orasql.Index)):
obj = obj.table()
if ("$" in obj.name or "/" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_")) and not args.keepjunk:
return False
if args.include is not None and args.include.search(obj.name) is None:
return False
if args.exclude is not None and args.exclude.search(obj.name) is not None:
return False
return True
for (i, obj) in enumerate(connection.objects(owner=None, mode="create")):
keepobj = keep(obj)
if args.verbose:
if args.execute:
msg = astyle.style_default("oracreate.py: ", cs1, " -> ", cs2, f": fetching/creating #{i+1:,}")
else:
msg = astyle.style_default("oracreate.py: ", cs1, f" fetching #{i+1:,}")
msg = astyle.style_default(msg, " ", s4object(str(obj)))
if not keepobj:
msg = astyle.style_default(msg, " ", s4warning("(skipped)"))
stderr.writeln(msg)
if keepobj:
if isinstance(obj, orasql.Sequence) and args.seqcopy:
sql = obj.createsqlcopy(connection, term)
else:
sql = obj.createsql(connection, term)
if sql:
if args.execute:
try:
cursor2.execute(sql)
except orasql.DatabaseError as exc:
if not args.ignore or "ORA-01013" in str(exc):
raise
stderr.writeln("oracreate.py: ", s4error(misc.format_exception(exc)))
else:
stdout.writeln(sql.strip())
stdout.writeln()
if args.format == "pysql":
stdout.writeln("-- @@@")
stdout.writeln()
def main(args=None):
p = argparse.ArgumentParser(
description=
"Recreate/rebuild all indexes/unique constraints in an Oracle database schema",
epilog=
"For more info see http://www.livinglogic.de/Python/orasql_scripts_orareindex.html"
)
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("-v",
"--verbose",
dest="verbose",
help="Give a progress report? (default %(default)s)",
default=False,
action=misc.FlagAction)
p.add_argument("-c",
"--color",
dest="color",
help="Color output (default %(default)s)",
default="auto",
choices=("yes", "no", "auto"))
p.add_argument(
"-r",
"--rebuild",
dest="rebuild",
help=
"Rebuild indexes instead of recreating them? (default %(default)s)",
default=False,
action=misc.FlagAction)
p.add_argument(
"-x",
"--execute",
dest="execute",
action=misc.FlagAction,
help=
"immediately execute the commands instead of printing them? (default %(default)s)"
)
p.add_argument("--format",
dest="format",
help="The output format (default %(default)s)",
choices=("sql", "pysql"),
default="sql")
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection = orasql.connect(args.connectstring)
cursor = connection.cursor()
cs = s4connectstring(connection.connectstring())
for (i, obj) in enumerate(
itertools.chain(
orasql.Index.objects(connection, owner=None),
orasql.UniqueConstraint.objects(connection, owner=None))):
rebuild = args.rebuild and isinstance(obj, orasql.Index)
# Progress report
if args.verbose:
stderr.writeln(
"orareindex.py: ", cs,
": {} #{:,} ".format("Rebuilding" if rebuild else "Recreating",
i + 1), s4object(str(obj)))
if rebuild:
if args.execute:
cursor.execute(obj.rebuildsql(term=False))
else:
stdout.writeln(obj.rebuildsql(term=True).strip())
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
stdout.writeln()
else:
if args.execute:
sql = obj.createsql(term=False)
cursor.execute(obj.dropsql(term=False))
cursor.execute(sql)
else:
stdout.writeln(obj.dropsql(term=True).strip())
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
stdout.writeln()
stdout.writeln(obj.createsql(term=True).strip())
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
stdout.writeln()
def main(args=None):
# Merge changes between oldsource and newsource into destination
p = argparse.ArgumentParser(description="output info for merging the changes between two Oracle database schemas into a third")
p.add_argument("connectstring1", help="Old version of database schema")
p.add_argument("connectstring2", help="New version of database schema")
p.add_argument("connectstring3", help="Schema into which changes should be merged")
p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default: %(default)s)", action=misc.FlagAction, default=False)
p.add_argument("-c", "--color", dest="color", help="Color output (default: %(default)s)", default="auto", choices=("yes", "no", "auto"))
p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' in their name? (default: %(default)s)", action=misc.FlagAction, default=False)
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection1 = orasql.connect(args.connectstring1)
connection2 = orasql.connect(args.connectstring2)
connection3 = orasql.connect(args.connectstring3)
def fetch(connection, name):
objects = set()
for (i, obj) in enumerate(connection.objects(owner=None, mode="flat")):
keep = ("$" not in obj.name and not obj.name.startswith("SYS_EXPORT_SCHEMA_")) or args.keepjunk
if args.verbose:
msg = astyle.style_default("oramerge.py: ", cs(connection), connid(name), f" fetching #{i+1:,} ", df(obj))
if not keep:
msg += s4error(" (skipped)")
stderr.writeln(msg)
if keep:
objects.add(obj)
return objects
def write(file, data):
try:
file.write(data)
file.write("\n")
finally:
file.close()
objects1 = fetch(connection1, 1)
objects2 = fetch(connection2, 2)
objects3 = fetch(connection3, 3)
retcode = 0
def inmesg(flag, name):
if flag:
return astyle.style_default("in ", connid(name))
else:
return astyle.style_default("not in ", connid(name))
countcreate = 0
countdrop = 0
countmerge = 0
countcollision = 0
countmergeconflict = 0
allobjects = objects1 | objects2 | objects3
for (i, obj) in enumerate(allobjects):
action = None
in1 = obj in objects1
in2 = obj in objects2
in3 = obj in objects3
if args.verbose:
stderr.write("oramerge.py: ", df(obj), " #", str(i+1), "/", str(len(allobjects)), ": ")
first = True
for (nr, flag) in enumerate((in1, in2, in3)):
if flag:
if first:
stderr.write("in ")
first = False
else:
stderr.write("+")
stderr.write(connid(nr+1))
comm = s4comment("-- ", df(obj), " ")
if in1 != in2: # ignore changes from in2 to in3, because only if something changed in the sources we have to do something
if not in1 and in2 and not in3: # added in in2 => copy it to db3
if args.verbose:
stderr.writeln(" => ", s4action("new (create it)"))
countcreate += 1
action = "create"
elif not in1 and in2 and in3: # added in both in2 and in3 => collision?
if obj.createsql(connection2) != obj.createsql(connection3):
if args.verbose:
stderr.writeln(" => ", s4error("collision"))
countcollision += 1
action = "collision"
retcode = 2
else:
if args.verbose:
stderr.writeln(" => already created (keep it)")
elif in1 and not in2 and not in3: # removed in in2 and in3 => not needed
if args.verbose:
stderr.writeln(" => removed (not needed)")
elif in1 and not in2 and in3: # removed in in2 => remove in db3
if args.verbose:
stderr.writeln(" => ", s4action("drop it"))
countdrop += 1
action = "drop"
else:
raise ValueError("the boolean world is about to end")
elif in1 and in2 and in3: # in all three => merge it
sql1 = obj.createsql(connection1)
sql2 = obj.createsql(connection2)
sql3 = obj.createsql(connection3)
if args.verbose:
stderr.write(" => diffing")
if sql1 != sql2: # ignore changes between sql2 and sql3 here too
# If it's a table, we do not output a merged "create table" statement, but the appropriate "alter table" statements
if isinstance(obj, orasql.Table):
fields1 = set(obj.columns(connection1))
fields2 = set(obj.columns(connection2))
fields3 = set(obj.columns(connection3))
fieldcountcreate = 0
fieldcountdrop = 0
fieldcountmerge = 0
fieldcountcollision = 0
fieldcountmergeconflict = 0
for field in fields1 | fields2 | fields3:
in1 = field in fields1
in2 = field in fields2
in3 = field in fields3
if in1 != in2: # ignore changes between in2 and in3 here too
if not in1 and in2 and not in3: # added in in2 => copy it to db3
fieldcountcreate += 1
countcreate += 1
showcomment(stdout, "add ", df(field))
stdout.writeln(field.addsql(connection2))
elif not in1 and in2 and in3: # added in both in2 and in3 => collision?
fieldcountcollision += 1
countcollision += 1
showcomment(stdout, "collision ", df(field))
stdout.writeln(conflictmarker(7*"<", "added in ", cs(connection2), " and ", cs(connection3), " with different content"))
elif in1 and not in2 and not in3: # removed in in2 and in3 => not needed
pass
elif in1 and not in2 and in3: # removed in in2 => remove in db3
fieldcountdrop += 1
countdrop += 1
showcomment(stdout, "drop ", df(field))
stdout.writeln(field.dropsql(connection3))
elif in1 and in2 and in3: # in all three => modify field
sql1 = field.addsql(connection1)
sql2 = field.addsql(connection2)
sql3 = field.addsql(connection3)
if sql1 != sql2 or sql2 != sql3:
try:
sql = field.modifysql(connection3, connection1.cursor(), connection2.cursor()) # add changes from db1 to db2
except orasql.ConflictError as exc:
fieldcountmergeconflict += 1
countmergeconflict += 1
showcomment(stdout, "merge conflict ", df(field))
stdout.writeln(conflictmarker(7*"<", str(exc)))
else:
fieldcountmerge += 1
countmerge += 1
showcomment(stdout, "merged ", df(field))
stdout.writeln(sql)
if args.verbose:
showreport(stderr, "field", fieldcountcreate, fieldcountdrop, fieldcountcollision, fieldcountmerge, fieldcountmergeconflict)
else:
if args.verbose:
stderr.write(" => merge them")
action = "merge"
else:
if args.verbose:
stderr.writeln(" => identical")
elif in3:
if args.verbose:
stderr.writeln(" => keep it")
else:
if args.verbose:
stderr.writeln(" => not needed")
if action is not None:
if action == "collision":
showcomment(stdout, "collision ", df(obj))
stdout.writeln(conflictmarker(7*"<", "added in ", cs(connection2), " and ", cs(connection3), " with different content"))
elif action == "create":
showcomment(stdout, "create ", df(obj))
stdout.writeln(obj.createsql(connection2, term=True))
elif action == "drop":
showcomment(stdout, "drop ", df(obj))
stdout.writeln(obj.dropsql(connection3, term=True))
elif action == "merge":
filename1 = tempfile.mktemp(suffix=".sql", prefix="oramerge_1_")
filename2 = tempfile.mktemp(suffix=".sql", prefix="oramerge_2_")
filename3 = tempfile.mktemp(suffix=".sql", prefix="oramerge_3_")
file1 = open(filename1, "wb")
try:
write(file1, sql1)
file2 = open(filename2, "wb")
try:
write(file2, sql2)
file3 = open(filename3, "wb")
try:
write(file3, sql3)
# do the diffing
proc = subprocess.Popen(["diff3", "-m", filename3, filename1, filename2], stdout=subprocess.PIPE)
data = []
while True:
chunk = proc.stdout.read(8192)
if chunk:
data.append(chunk)
else:
break
diffretcode = proc.returncode
if diffretcode is None:
diffretcode = proc.wait()
while True:
chunk = proc.stdout.read(8192)
if chunk:
data.append(chunk)
else:
break
data = "".join(data)
if diffretcode == 0: # no conflict
showcomment(stdout, "merge ", df(obj))
# Check if anything has changed
finalsql = data
# diff3 seems to append a "\n"
if finalsql != sql3 and (not finalsql.endswith("\n") or finalsql[:-1] != sql3):
if args.verbose:
stderr.writeln(" => ", s4action("merged"))
stdout.write(finalsql)
elif diffretcode == 1: # conflict
showcomment(stdout, "merge conflict ", df(obj))
if args.verbose:
stderr.writeln(" => ", s4error("merge conflict"))
retcode = 2
for line in data.splitlines():
line = line.rstrip("\n")
if line.startswith(7*"<") or line.startswith(7*"|") or line.startswith(7*"=") or line.startswith(7*">"):
(prefix, line) = (line[:7], line[7:])
line = line.strip()
if line == filename1:
line = conflictmarker(prefix, cs(connection1))
elif line == filename2:
line = conflictmarker(prefix, cs(connection2))
elif line == filename3:
line = conflictmarker(prefix, cs(connection3))
else:
line = conflictmarker(prefix, line)
stdout.writeln(line)
else:
raise OSError(f"Trouble from diff3: {diffretcode}")
finally:
os.remove(filename3)
finally:
os.remove(filename2)
finally:
os.remove(filename1)
if args.verbose:
stderr.write("oramerge.py: ", cs(connection3))
showreport(stderr, "object", countcreate, countdrop, countcollision, countmerge, countmergeconflict)
return retcode
def main(args=None):
p = argparse.ArgumentParser(description="Print (or execute) the SQL of all objects in an Oracle database schema", epilog="For more info see http://python.livinglogic.de/orasql_scripts_oracreate.html")
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
p.add_argument("-s", "--seqcopy", dest="seqcopy", help="copy sequence values? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-x", "--execute", metavar="CONNECTSTRING2", dest="execute", help="Execute in target database")
p.add_argument("-k", "--keepjunk", dest="keepjunk", help="Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction)
p.add_argument( "--format", dest="format", help="The output format (default %(default)s)", choices=("sql", "pysql"), default="sql")
p.add_argument( "--include", dest="include", metavar="REGEXP", help="Include only objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
p.add_argument( "--exclude", dest="exclude", metavar="REGEXP", help="Exclude objects whose name contains PATTERN (default: %(default)s)", type=re.compile)
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection = orasql.connect(args.connectstring)
if args.execute:
connection2 = orasql.connect(args.execute)
cursor2 = connection2.cursor()
term = False
else:
term = True
cs1 = s4connectstring(connection.connectstring())
if args.execute:
cs2 = s4connectstring(connection2.connectstring())
def keep(obj):
if obj.owner is not None:
return False
# output pk, fks etc. only when they belong to a table we do output
if isinstance(obj, (orasql.Constraint, orasql.Index)):
obj = obj.table()
if ("$" in obj.name or "/" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_")) and not args.keepjunk:
return False
if args.include is not None and args.include.search(obj.name) is None:
return False
if args.exclude is not None and args.exclude.search(obj.name) is not None:
return False
return True
for (i, obj) in enumerate(connection.objects(owner=None, mode="create")):
keepobj = keep(obj)
if args.verbose:
if args.execute:
msg = astyle.style_default("oracreate.py: ", cs1, " -> ", cs2, f": fetching/creating #{i+1:,}")
else:
msg = astyle.style_default("oracreate.py: ", cs1, f" fetching #{i+1:,}")
msg = astyle.style_default(msg, " ", s4object(str(obj)))
if not keepobj:
msg = astyle.style_default(msg, " ", s4warning("(skipped)"))
stderr.writeln(msg)
if keepobj:
if isinstance(obj, orasql.Sequence) and args.seqcopy:
sql = obj.createsqlcopy(connection, term)
else:
sql = obj.createsql(connection, term)
if sql:
if args.execute:
try:
cursor2.execute(sql)
except orasql.DatabaseError as exc:
if not args.ignore or "ORA-01013" in str(exc):
raise
stderr.writeln("oracreate.py: ", s4error(misc.format_exception(exc)))
else:
stdout.writeln(sql.strip())
stdout.writeln()
if args.format == "pysql":
stdout.writeln("-- @@@")
stdout.writeln()
def main(args=None):
p = argparse.ArgumentParser(
description="compare two Oracle database schemas",
epilog=
"For more info see http://www.livinglogic.de/Python/orasql_scripts_oradiff.html"
)
p.add_argument("connectstring1", help="First schema")
p.add_argument("connectstring2", help="Second schema")
p.add_argument("-v",
"--verbose",
dest="verbose",
help="Give a progress report? (default %(default)s)",
default=False,
action=misc.FlagAction)
p.add_argument("-c",
"--color",
dest="color",
help="Color output (default %(default)s)",
default="auto",
choices=("yes", "no", "auto"))
p.add_argument("-m",
"--mode",
dest="mode",
help="Output mode (default %(default)s)",
default="udiff",
choices=("brief", "udiff", "full"))
p.add_argument(
"--format",
dest="format",
help="The output format for 'full' mode (default %(default)s)",
choices=("sql", "pysql"),
default="sql")
p.add_argument("-n",
"--context",
dest="context",
help="Number of context lines (default %(default)s)",
type=int,
default=2)
p.add_argument(
"-k",
"--keepjunk",
dest="keepjunk",
help=
"Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)",
default=False,
action=misc.FlagAction)
p.add_argument("-b",
"--blank",
dest="blank",
help="How to treat whitespace (default %(default)s)",
default="literal",
choices=("literal", "trail", "lead", "both", "collapse"))
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection1 = orasql.connect(args.connectstring1)
connection2 = orasql.connect(args.connectstring2)
def fetch(connection, name, mode="flat"):
objectset = set()
objectlist = []
def keep(obj):
if obj.owner is not None:
return False
if args.keepjunk:
return True
if "$" in obj.name or obj.name.startswith("SYS_EXPORT_SCHEMA_"):
return False
return True
for (i, obj) in enumerate(connection.objects(owner=None, mode=mode)):
keepdef = keep(obj)
if args.verbose:
msg = astyle.style_default("oradiff.py: ", cs(connection),
connid(name),
": fetching #{:,} ".format(i + 1),
df(obj))
if not keepdef:
msg = astyle.style_default(msg, " ",
s4warning("(skipped)"))
stderr.writeln(msg)
if keepdef:
objectset.add(obj)
objectlist.append(obj)
return (objectset, objectlist)
(objectset1, objectlist1) = fetch(connection1, 1, mode="drop")
(objectset2, objectlist2) = fetch(connection2, 2, mode="create")
# If we output in full mode the resulting SQL script should be usable, so
# we try to iterate the object in the appropriate order
allobjects = objectset1 | objectset2
count = 1
# Objects in database 2
for obj in objectlist2:
# Objects only in database 2
if obj not in objectset1:
if args.verbose:
stderr.writeln("oradiff.py: only in ", cs(connection2),
" #{:,}/{:,} ".format(count, len(allobjects)),
df(obj))
if args.mode == "brief":
stdout.writeln(df(obj), ": only in ", cs(connection2))
elif args.mode == "full":
stdout.writeln(comment(df(obj), ": only in ", cs(connection2)))
sql = obj.createsql(connection2, term=True).strip()
if sql:
stdout.writeln(sql)
stdout.writeln()
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
elif args.mode == "udiff":
sql = getcanonicalsql(obj.createsql(connection2), args.blank)
showudiff(stdout, obj, [], sql, connection1, connection2,
args.context)
else:
if args.verbose:
stderr.writeln(
"oradiff.py: diffing #{:,}/{:,} ".format(
count, len(allobjects)), df(obj))
sql1 = obj.createsql(connection1)
sql2 = obj.createsql(connection2)
sql1c = getcanonicalsql(sql1, args.blank)
sql2c = getcanonicalsql(sql2, args.blank)
if sql1c != sql2c:
if args.mode == "brief":
stdout.writeln(df(obj), ": different")
elif args.mode == "full":
stdout.writeln(comment(df(obj), ": different"))
sql = obj.createsql(connection2).strip()
stdout.writeln(sql)
stdout.writeln()
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
elif args.mode == "udiff":
showudiff(stdout, obj, sql1c, sql2c, connection1,
connection2, args.context)
count += 1
# Objects only in database 1
for obj in objectlist1:
if obj not in objectset2:
if args.verbose:
stderr.writeln("oradiff.py: only in ", cs(connection1),
" #{:,}/{:,} ".format(count, len(allobjects)),
df(obj))
if args.mode == "brief":
stdout.writeln(df(obj), ": only in ", cs(connection1))
elif args.mode == "full":
stdout.writeln(comment(df(obj), ": only in ", cs(connection1)))
sql = obj.dropsql(connection1, term=True).strip()
if sql:
stdout.writeln(sql)
stdout.writeln()
if args.format == "pysql":
stdout.writeln()
stdout.writeln("-- @@@")
elif args.mode == "udiff":
sql = getcanonicalsql(obj.createsql(connection1), args.blank)
showudiff(stdout, obj, sql, [], connection1, connection2,
args.context)
count += 1
import sys
from ll import orasql
db = orasql.connect(sys.argv[1])
def export(f, obj):
print(obj.createsql(db, True), file=f)
with open("ul4on_pkg.sql", "w") as f:
export(f, orasql.Package("UL4ON_PKG"))
export(f, orasql.PackageBody("UL4ON_PKG"))
with open("ul4onbuffer_pkg.sql", "w") as f:
export(f, orasql.Package("UL4ONBUFFER_PKG"))
export(f, orasql.PackageBody("UL4ONBUFFER_PKG"))
with open("ul4ongen.sql", "w") as f:
export(f, orasql.Type("UL4ONGEN"))
export(f, orasql.TypeBody("UL4ONGEN"))
def test_connection_connectstring():
db = orasql.connect(dbname)
user = dbname.split("/")[0]
name = dbname.split("@")[1]
assert f"{user}@{name}" == db.connectstring()
def main(args=None):
# Merge changes between oldsource and newsource into destination
p = argparse.ArgumentParser(
description=
"output info for merging the changes between two Oracle database schemas into a third"
)
p.add_argument("connectstring1", help="Old version of database schema")
p.add_argument("connectstring2", help="New version of database schema")
p.add_argument("connectstring3",
help="Schema into which changes should be merged")
p.add_argument("-v",
"--verbose",
dest="verbose",
help="Give a progress report? (default: %(default)s)",
action=misc.FlagAction,
default=False)
p.add_argument("-c",
"--color",
dest="color",
help="Color output (default: %(default)s)",
default="auto",
choices=("yes", "no", "auto"))
p.add_argument(
"-k",
"--keepjunk",
dest="keepjunk",
help="Output objects with '$' in their name? (default: %(default)s)",
action=misc.FlagAction,
default=False)
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection1 = orasql.connect(args.connectstring1)
connection2 = orasql.connect(args.connectstring2)
connection3 = orasql.connect(args.connectstring3)
def fetch(connection, name):
objects = set()
for (i, obj) in enumerate(connection.objects(owner=None, mode="flat")):
keep = ("$" not in obj.name
and not obj.name.startswith("SYS_EXPORT_SCHEMA_")
) or args.keepjunk
if args.verbose:
msg = astyle.style_default("oramerge.py: ", cs(connection),
connid(name),
f" fetching #{i+1:,} ", df(obj))
if not keep:
msg += s4error(" (skipped)")
stderr.writeln(msg)
if keep:
objects.add(obj)
return objects
def write(file, data):
try:
file.write(data)
file.write("\n")
finally:
file.close()
objects1 = fetch(connection1, 1)
objects2 = fetch(connection2, 2)
objects3 = fetch(connection3, 3)
retcode = 0
def inmesg(flag, name):
if flag:
return astyle.style_default("in ", connid(name))
else:
return astyle.style_default("not in ", connid(name))
countcreate = 0
countdrop = 0
countmerge = 0
countcollision = 0
countmergeconflict = 0
allobjects = objects1 | objects2 | objects3
for (i, obj) in enumerate(allobjects):
action = None
in1 = obj in objects1
in2 = obj in objects2
in3 = obj in objects3
if args.verbose:
stderr.write("oramerge.py: ", df(obj), " #", str(i + 1), "/",
str(len(allobjects)), ": ")
first = True
for (nr, flag) in enumerate((in1, in2, in3)):
if flag:
if first:
stderr.write("in ")
first = False
else:
stderr.write("+")
stderr.write(connid(nr + 1))
comm = s4comment("-- ", df(obj), " ")
if in1 != in2: # ignore changes from in2 to in3, because only if something changed in the sources we have to do something
if not in1 and in2 and not in3: # added in in2 => copy it to db3
if args.verbose:
stderr.writeln(" => ", s4action("new (create it)"))
countcreate += 1
action = "create"
elif not in1 and in2 and in3: # added in both in2 and in3 => collision?
if obj.createsql(connection2) != obj.createsql(connection3):
if args.verbose:
stderr.writeln(" => ", s4error("collision"))
countcollision += 1
action = "collision"
retcode = 2
else:
if args.verbose:
stderr.writeln(" => already created (keep it)")
elif in1 and not in2 and not in3: # removed in in2 and in3 => not needed
if args.verbose:
stderr.writeln(" => removed (not needed)")
elif in1 and not in2 and in3: # removed in in2 => remove in db3
if args.verbose:
stderr.writeln(" => ", s4action("drop it"))
countdrop += 1
action = "drop"
else:
raise ValueError("the boolean world is about to end")
elif in1 and in2 and in3: # in all three => merge it
sql1 = obj.createsql(connection1)
sql2 = obj.createsql(connection2)
sql3 = obj.createsql(connection3)
if args.verbose:
stderr.write(" => diffing")
if sql1 != sql2: # ignore changes between sql2 and sql3 here too
# If it's a table, we do not output a merged "create table" statement, but the appropriate "alter table" statements
if isinstance(obj, orasql.Table):
fields1 = set(obj.columns(connection1))
fields2 = set(obj.columns(connection2))
fields3 = set(obj.columns(connection3))
fieldcountcreate = 0
fieldcountdrop = 0
fieldcountmerge = 0
fieldcountcollision = 0
fieldcountmergeconflict = 0
for field in fields1 | fields2 | fields3:
in1 = field in fields1
in2 = field in fields2
in3 = field in fields3
if in1 != in2: # ignore changes between in2 and in3 here too
if not in1 and in2 and not in3: # added in in2 => copy it to db3
fieldcountcreate += 1
countcreate += 1
showcomment(stdout, "add ", df(field))
stdout.writeln(field.addsql(connection2))
elif not in1 and in2 and in3: # added in both in2 and in3 => collision?
fieldcountcollision += 1
countcollision += 1
showcomment(stdout, "collision ", df(field))
stdout.writeln(
conflictmarker(7 * "<", "added in ",
cs(connection2), " and ",
cs(connection3),
" with different content"))
elif in1 and not in2 and not in3: # removed in in2 and in3 => not needed
pass
elif in1 and not in2 and in3: # removed in in2 => remove in db3
fieldcountdrop += 1
countdrop += 1
showcomment(stdout, "drop ", df(field))
stdout.writeln(field.dropsql(connection3))
elif in1 and in2 and in3: # in all three => modify field
sql1 = field.addsql(connection1)
sql2 = field.addsql(connection2)
sql3 = field.addsql(connection3)
if sql1 != sql2 or sql2 != sql3:
try:
sql = field.modifysql(
connection3, connection1.cursor(),
connection2.cursor()
) # add changes from db1 to db2
except orasql.ConflictError as exc:
fieldcountmergeconflict += 1
countmergeconflict += 1
showcomment(stdout, "merge conflict ",
df(field))
stdout.writeln(
conflictmarker(7 * "<", str(exc)))
else:
fieldcountmerge += 1
countmerge += 1
showcomment(stdout, "merged ", df(field))
stdout.writeln(sql)
if args.verbose:
showreport(stderr, "field", fieldcountcreate,
fieldcountdrop, fieldcountcollision,
fieldcountmerge, fieldcountmergeconflict)
else:
if args.verbose:
stderr.write(" => merge them")
action = "merge"
else:
if args.verbose:
stderr.writeln(" => identical")
elif in3:
if args.verbose:
stderr.writeln(" => keep it")
else:
if args.verbose:
stderr.writeln(" => not needed")
if action is not None:
if action == "collision":
showcomment(stdout, "collision ", df(obj))
stdout.writeln(
conflictmarker(7 * "<",
"added in ", cs(connection2), " and ",
cs(connection3), " with different content"))
elif action == "create":
showcomment(stdout, "create ", df(obj))
stdout.writeln(obj.createsql(connection2, term=True))
elif action == "drop":
showcomment(stdout, "drop ", df(obj))
stdout.writeln(obj.dropsql(connection3, term=True))
elif action == "merge":
filename1 = tempfile.mktemp(suffix=".sql",
prefix="oramerge_1_")
filename2 = tempfile.mktemp(suffix=".sql",
prefix="oramerge_2_")
filename3 = tempfile.mktemp(suffix=".sql",
prefix="oramerge_3_")
file1 = open(filename1, "wb")
try:
write(file1, sql1)
file2 = open(filename2, "wb")
try:
write(file2, sql2)
file3 = open(filename3, "wb")
try:
write(file3, sql3)
# do the diffing
proc = subprocess.Popen([
"diff3", "-m", filename3, filename1, filename2
],
stdout=subprocess.PIPE)
data = []
while True:
chunk = proc.stdout.read(8192)
if chunk:
data.append(chunk)
else:
break
diffretcode = proc.returncode
if diffretcode is None:
diffretcode = proc.wait()
while True:
chunk = proc.stdout.read(8192)
if chunk:
data.append(chunk)
else:
break
data = "".join(data)
if diffretcode == 0: # no conflict
showcomment(stdout, "merge ", df(obj))
# Check if anything has changed
finalsql = data
# diff3 seems to append a "\n"
if finalsql != sql3 and (
not finalsql.endswith("\n")
or finalsql[:-1] != sql3):
if args.verbose:
stderr.writeln(" => ",
s4action("merged"))
stdout.write(finalsql)
elif diffretcode == 1: # conflict
showcomment(stdout, "merge conflict ", df(obj))
if args.verbose:
stderr.writeln(" => ",
s4error("merge conflict"))
retcode = 2
for line in data.splitlines():
line = line.rstrip("\n")
if line.startswith(
7 * "<") or line.startswith(
7 * "|") or line.startswith(
7 *
"=") or line.startswith(
7 * ">"):
(prefix, line) = (line[:7], line[7:])
line = line.strip()
if line == filename1:
line = conflictmarker(
prefix, cs(connection1))
elif line == filename2:
line = conflictmarker(
prefix, cs(connection2))
elif line == filename3:
line = conflictmarker(
prefix, cs(connection3))
else:
line = conflictmarker(prefix, line)
stdout.writeln(line)
else:
raise OSError(
f"Trouble from diff3: {diffretcode}")
finally:
os.remove(filename3)
finally:
os.remove(filename2)
finally:
os.remove(filename1)
if args.verbose:
stderr.write("oramerge.py: ", cs(connection3))
showreport(stderr, "object", countcreate, countdrop, countcollision,
countmerge, countmergeconflict)
return retcode
def test_connection_sequences(): db = orasql.connect(dbname) list(db.sequences(None))
def test_connection_fks(): db = orasql.connect(dbname) list(db.fks(None))
def test_connect(): db = orasql.connect(dbname) assert isinstance(db, orasql.Connection)
def main(args=None):
p = argparse.ArgumentParser(
description=
"Print (or execute) SQL for deleting all records from all tables in an Oracle database schema",
epilog=
"For more info see http://www.livinglogic.de/Python/orasql_scripts_oradelete.html"
)
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("-v",
"--verbose",
dest="verbose",
help="Give a progress report? (default %(default)s)",
default=False,
action=misc.FlagAction)
p.add_argument("-c",
"--color",
dest="color",
help="Color output (default %(default)s)",
default="auto",
choices=("yes", "no", "auto"))
p.add_argument("-s",
"--sequences",
dest="sequences",
help="Reset sequences? (default %(default)s)",
default=False,
action=misc.FlagAction)
p.add_argument(
"-x",
"--execute",
dest="execute",
action=misc.FlagAction,
help=
"immediately execute the commands instead of printing them? (default %(default)s)"
)
p.add_argument(
"-k",
"--keepjunk",
dest="keepjunk",
help=
"Output objects with '$' or 'SYS_EXPORT_SCHEMA_' in their name? (default %(default)s)",
default=False,
action="store_true")
p.add_argument("-i",
"--ignore",
dest="ignore",
help="Ignore errors? (default %(default)s)",
default=False,
action=misc.FlagAction)
p.add_argument(
"-t",
"--truncate",
dest="truncate",
help="Truncate tables (instead of deleting)? (default %(default)s)",
default=False,
action=misc.FlagAction)
p.add_argument("--format",
dest="format",
help="The output format (default %(default)s)",
choices=("sql", "pysql"),
default="sql")
p.add_argument(
"--include",
dest="include",
metavar="REGEXP",
help=
"Include only objects whose name contains PATTERN (default: %(default)s)",
type=re.compile)
p.add_argument(
"--exclude",
dest="exclude",
metavar="REGEXP",
help=
"Exclude objects whose name contains PATTERN (default: %(default)s)",
type=re.compile)
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection = orasql.connect(args.connectstring)
cursor = connection.cursor()
cs = s4connectstring(connection.connectstring())
def keep(obj):
if ("$" in obj.name or "/" in obj.name
or obj.name.startswith("SYS_EXPORT_SCHEMA_")
) and not args.keepjunk:
return False
if args.include is not None and args.include.search(obj.name) is None:
return False
if args.exclude is not None and args.exclude.search(
obj.name) is not None:
return False
return True
for (i, obj) in enumerate(connection.tables(owner=None, mode="drop")):
keepobj = keep(obj)
# Progress report
if args.verbose:
msg = "truncating" if args.truncate else "deleting from"
msg = astyle.style_default("oradelete.py: ", cs,
": {} #{:,} ".format(msg, i + 1),
s4object(str(obj)))
if not keepobj:
msg = astyle.style_default(msg, " ", s4warning("(skipped)"))
stderr.writeln(msg)
if keepobj:
# Print or execute SQL
if args.execute:
try:
fmt = "truncate table {}" if args.truncate else "delete from {}"
cursor.execute(fmt.format(obj.name))
except orasql.DatabaseError as exc:
if not args.ignore or "ORA-01013" in str(exc):
raise
stderr.writeln(
"oradelete.py: ",
s4error("{}: {}".format(exc.__class__,
str(exc).strip())))
else:
if args.truncate:
sql = "truncate table {};".format(obj.name)
else:
sql = "delete from {};".format(obj.name)
stdout.writeln(sql)
stdout.writeln()
if args.format == "pysql":
stdout.writeln("-- @@@")
stdout.writeln()
if not args.truncate:
connection.commit()
if args.sequences:
for (i, obj) in enumerate(connection.sequences(owner=None)):
keepobj = keep(obj)
# Progress report
if args.verbose:
msg = astyle.style_default("oradelete.py: ", cs,
": recreating #{:,} ".format(i + 1),
s4object(str(obj)))
if not keepobj:
msg = astyle.style_default(msg, " ",
s4warning("(skipped)"))
stderr.writeln(msg)
if keepobj:
# Print or execute SQL
if args.execute:
try:
sql = obj.createsql(term=False)
cursor.execute(obj.dropsql(term=False))
cursor.execute(sql)
except orasql.DatabaseError as exc:
if not args.ignore or "ORA-01013" in str(exc):
raise
stderr.writeln(
"oradelete.py: ",
s4error("{}: {}".format(exc.__class__,
str(exc).strip())))
else:
stdout.writeln(obj.dropsql(term=True).strip())
stdout.writeln()
if args.format == "pysql":
stdout.writeln("-- @@@")
stdout.writeln()
stdout.writeln(obj.createsql(term=True).strip())
stdout.writeln()
if args.format == "pysql":
stdout.writeln("-- @@@")
stdout.writeln()
def _getconnection(self): if self.pool is not None: return self.pool.acquire() elif self._connection is None: self._connection = orasql.connect(self.connectstring, threaded=True, **self.kwargs) return self._connection
def test_connection_connectstring():
db = orasql.connect(dbname)
user = dbname.split("/")[0]
name = dbname.split("@")[1]
assert f"{user}@{name}" == db.connectstring()
def main(args=None):
p = argparse.ArgumentParser(description="Check Oracle database schema for cyclic foreign key references", epilog="For more info see http://python.livinglogic.de/orasql_scripts_oracycles.html")
p.add_argument("connectstring", help="Oracle connect string")
p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", action=misc.FlagAction, default=False)
p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
args = p.parse_args(args)
if args.color == "yes":
color = True
elif args.color == "no":
color = False
else:
color = None
stdout = astyle.Stream(sys.stdout, color)
stderr = astyle.Stream(sys.stderr, color)
connection = orasql.connect(args.connectstring)
cs = s4connectstring(connection.connectstring())
refs = {} # type: Dict[Table, Tuple[ForeignKey, Table]]
# Collect tables
tables = list(connection.tables())
# Collect foreign keys from tables
for (i, table) in enumerate(tables, 1):
if args.verbose:
stderr.writeln("oracycles.py: ", cs, f": Collecting fks from table #{i:,}/{len(tables):,} ", s4object(table.name), f" -> {len(refs):,} found")
for constraint in list(table.constraints()):
if isinstance(constraint, orasql.ForeignKey):
pk = constraint.refconstraint()
if isinstance(pk, orasql.PrimaryKey):
if table not in refs:
refs[table] = []
reftable = pk.table()
refs[table].append((constraint, reftable))
# Find cycles in foreign keys
cycles = {}
def collect(path):
for i in range(len(path)-2, 0, -1):
if path[i] == path[-1]:
cyclepath = path[i:]
pathkey = frozenset(cyclepath)
if pathkey not in cycles:
cycles[pathkey] = cyclepath
return
def findcycles(path):
table = path[-1]
if table in refs:
for (constraint, reftable) in refs[table]:
path.append(constraint)
cycle = reftable in path
path.append(reftable)
if cycle:
collect(path)
else:
findcycles(path)
path.pop()
path.pop()
for (i, table) in enumerate(tables, 1):
if args.verbose:
stderr.writeln("oracycles.py: ", cs, f": Testing table #{i:,}/{len(tables):,} ", s4object(table.name), f" for cycles -> {len(cycles):,} found")
findcycles([table])
# Sort and output result
def pathstr(path):
v = []
for obj in path:
if isinstance(obj, orasql.ForeignKey):
v.append(f"{misc.first(obj.columns()).name}({obj.name})")
else:
v.append(obj.name)
return " -> ".join(v)
cycles = sorted(cycles.values(), key=pathstr)
for path in cycles:
for (i, obj) in enumerate(path):
if i:
stdout.write(" -> ")
if isinstance(obj, orasql.ForeignKey):
stdout.write(s4object(misc.first(obj.columns()).name), "(", s4object(obj.name), ")")
else:
stdout.write(s4object(obj.name))
stdout.writeln()
def test_connection_tables(): db = orasql.connect(dbname) list(db.tables(None))
import sys
from ll import orasql
db = orasql.connect(sys.argv[1])
def export(f, obj):
print(obj.createsql(db, True), file=f)
with open("ul4on_pkg.sql", "w") as f:
export(f, orasql.Package("UL4ON_PKG"))
export(f, orasql.PackageBody("UL4ON_PKG"))
with open("ul4onbuffer_pkg.sql", "w") as f:
export(f, orasql.Package("UL4ONBUFFER_PKG"))
export(f, orasql.PackageBody("UL4ONBUFFER_PKG"))
with open("ul4onblobbuffer_pkg.sql", "w") as f:
export(f, orasql.Package("UL4ONBLOBBUFFER_PKG"))
export(f, orasql.PackageBody("UL4ONBLOBBUFFER_PKG"))
with open("ul4ongen.sql", "w") as f:
export(f, orasql.Type("UL4ONGEN"))
export(f, orasql.TypeBody("UL4ONGEN"))
def test_connection_sequences(): db = orasql.connect(dbname) list(db.sequences(None))
