The OracleExtensions module provides functionality for advanced Oracle DBMS operations or tools.

To use this module, use "%requires OracleExtensions" in your code.

All the public symbols in the module are defined in the OracleExtensions namespace.

Note: Standard Qore oracle module is required to be installed.

Code Instrumentation

Application Info

To provide functionality of DBMS_APPLICATION_INFO package (exec grant required) for Datasource and DatasourcePool objects.

Application developers or DBAs can use this functionality together with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules and debugging.

Values provided to OracleExtensions::OracleApplicationInfo are used in V$SESSION or V$SQLAREA views for DBA tasks.

Application info is composed of three components in following hierarchy of logical depencency:

Client, the client info of the session
Module, the name of the module that is currently running
Action, the name of the current action within the current Module

Note: All application info components are optional and using eg. only one of them is valid approach.

Relevant Classes:

OracleDatasource and OracleDatasourcePool includes Qore::Datasource and Qore::DatasourcePool inside and all overriden methods keep their original properties: transaction handling, return data, optional arguments...

Application Info Scope

Persistent: it stays until trnsaction exists.
- beginTransaction, setAppInfo
One-time: used with select fanily mathods outside of the transaction.
Automatic: see Automatic Application Info

Automatic Application Info

There is an optional argument in all OracleExtensions::OracleDatasource and OracleExtensions::OracleDatasourcePool constructors: userAutoInfo, type code.

This user code allows to specify function (or closure) to define automatic Application Info for method calls where there is no explicit info used.

Note: User code used for Automatic Application Info obtaining should return NOTHING or OracleExtensions::OracleApplicationInfo instance. A type cast exception is raised otherwise.

Examples

%new-style
 
OracleExtensions::OracleDatasource ds("oracle", "user", "pass", "db");
OracleExtensions::OracleApplicationInfo audit("my client", "my module", "a action");
hash res1 = ds.select(a, "select * from a_very_huge_table where fullscan_column = %v", 1);
...
hash res2 = ds.select("select * from a_very_huge_table where fullscan_column = %v", 1);

The code above has some important characteristics:

V$SESSION will hold audit's values in appropriate columns while the statement runs
Transaction handling remains the same as in Qore::Datasource case
Data returned from select method are the same as in Qore::Datasource
The default application info will be used (if found) in 2nd case - without specified OracleExtensions::OracleApplicationInfo instance.

Next example explains how to use Automatic Application Info

# user code to get default/automatic Application Info.
*OracleExtensions::OracleApplicationInfo sub getAutoInfo()
{
    printf("getAutoInfo: called\n");
    return new OracleExtensions::OracleApplicationInfo("user module", "auto info", "auto info");
}
 
# a constructor with standard arguments - ellipsed with "..." in this example
# The function \c getAutoInfo is registered in OracleDatasource as @ref call-reference
OracleExtensions::OracleDatasource dd(..., \getAutoInfo());
dd.select("select * from huge_table where fullscan = 1");

Long Operations

This functionality can be used to show the progress of long operations by inserting rows in the V$SESSION_LONGOPS view. For it to work effectively, you must know the total amount of work you are planning to do and how much work you have done so far.

Relevant Classes:

Examples

%new-style
 
OracleDatasource ds("oracle", "user", "pass", "db");
 
int max = 10;
OracleExtensions::OracleLongOps lo('running potatoes counting', 'FOOBAR table', 'potatoes', max);
for (int i = 1; i <= max; i++) {
    lo.sofar = i;
    lo.op_context = i * 1000;
    db.setLongOpsProgress(lo);
    sleep(1);
}

it will result in this line in V$SESSION_LONGOPS:

...	OPNAME	TARGET	TARGET_DESC	SOFAR	TOTALWORK	UNITS	...	MESSAGE	...
...	running potatoes counting	{null}	FOOBAR table	10	10	potatoes	...	running potatoes counting: FOOBAR table : 10 out of 10 potatoes done	...

DBMS_OUTPUT Wrapper

The DBMS_OUTPUT package wrappers (exec grant required) enable you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

Relevant Classes:

Examples

%new-style
 
OracleExtensions::OracleDatasource ds("oracle", "user", "pass", "db");
 
ds.outputEnable();
# qore client side
ds.outputPutLine('foo');
ds.outputPutLine(111);
ds.outputPutLine(now());
# call any PL/SQL unit on server with DBMS_OUTPUT.PUT... inits body
ds.exec("begin dbms_output.put_line(%v); end;", "hey, it's server here");
 
my *string res = o.outputGet();
printf("   RESULT: %N\n", res);
ds.outputDisable();

will print out:

   RESULT: "foo
111
20130301151736
hey, it's server here
"