ae.db_ora

database system core layer to access Oracle databases

the class OraDb of this namespace portion is a thin layer that is extending the DbBase of the module ae.db_core to connect to an Oracle database.

Hint

this namespace portion is using the cx_Oracle package as the database driver. the cx_Oracle package has to have at least version 5 or higher.

basic usage of the oracle database layer

to create an instance of the class OraDb you first have to create a SystemBase instance. this can be done either programmatically by providing an application instance (of the class ConsoleApp or an inherited sub-class of it) plus any database parameters, like required credentials and any database configuration features/options:

app = ConsoleApp()
system = SystemBase('system-id', app, dict(User='user name', Password='password`, ...), ...)

alternatively provide all system-specific info within the ae config files and let UsedSystems load it:

system = used_systems['system-id']

finally pass the database parameters in system to create an instance of OraDb:

ora_db = OraDb(system)

then call the connect() method of this instance to connect to the Oracle database server:

error_message = ora_db.connect()
if error_message:
    print(error_message)

if the connection could not be established then connect() is returning an error message string. if the return value is an empty string then you can use all the methods provided by DbBase, like e.g. update():

error_message = ora_db.update('my_table`, {'my_col': 'new value'})
if error_message:
    print(error_message)
    error_message = ora_db.rollback()

an explicit call of rollback() is only needed if you use transactions. in this case you should also use commit() at the end of each transaction to store any data updates:

error_message = ora_db.commit()

alternatively you can use the commit argument that is provided by the DbBase DML methods: by passing a True value to this argument, the method will automatically execute a commit() call for you if no error occurred in the DML method:

error_message = ora_db.update('table`, {'column': 369}, commit=True)

finally after all database actions are done you can close the connection to the databases server with the close() method:

error_message = ora_db.close()

Classes

OraDb(system)

Oracle database class, based on DbBase

class OraDb(system)

Bases: DbBase

Oracle database class, based on DbBase

__init__(system)

create instance of oracle database object.

Parameters:

system (SystemBase) – instance of a SystemBase class.

SystemBase (defined in the module ae.sys_core) is providing the credentials and features, which get retrieved from config files, then converted by connect_params() into connection parameters to connect to the Postgres database.

if you experiencing the following unicode encoding error:

'charmap' codec can't decode byte 0x90 in position 2: character maps to <undefined>

don’t try to create a type handler like recommended in some places - still got same error after adding the following method to replace the self.conn.outputtypehandler of the database driver:

def output_type_handler(cursor, name, default_type, size, precision, scale):
    if default_type in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
        return cursor.var(cx_Oracle.NCHAR, size, cursor.arraysize)

luckily, finally found workaround by setting the following OS environment variable to the character set of the used Oracle server (here UTF8):

os.environ["NLS_LANG"] = ".AL32UTF8"

connect()

connect this instance to the database driver.

Return type:

str

prepare_ref_param(value)

prepare special Oracle reference parameter.

Parameters:

value (Union[datetime, int, float, str]) – the input value passed into the reference parameter of the called stored procedure.

Return type:

Any

Returns:

a handle to the reference variable.

the following code snippet shows how to use this method together with get_value() to retrieve the returned value of a reference parameter:

ora_db = OraDb(...)
*ref_var* = ora_db.prepare_ref_param("input_value")
err_msg = ora_db.call_proc('STORED_PROCEDURE', (*ref_var*, ...))
if not err_msg:
    output_value = ora_db.get_value(*ref_var*)

static get_value(var)

get output value from a reference variable passed into a stored procedure.

Parameters:

var – handle to a reference variable.

Return type:

Any

Returns:

output value of the reference variable.

static set_value(var, value)

set the input value of a reference variable to pass into a stored procedure.

Parameters:

• var (Any) – handle to the reference variable to set.

• value (Union[datetime, int, float, str]) – value to set as input value of the reference variable.

param_style: str

database driver bind variable/parameter style

last_err_msg: str

last system connection error message(s)