Qore oracle Module  3.1
 All Classes Namespaces Functions Variables Groups
Qore oracle Module

Contents of this documentation:


The oracle module provides an Oracle driver to Qore's DBI system, allowing Qore programs to take access Oracle databases through the Qore's Datasource, DatasourcePool, and SQLStatement classes.

This module is released under the LGPL 2.1 and is tagged as such in the module's header (meaning it can be loaded unconditionally regardless of how the Qore library was initialized).

Also included with the binary oracle module:

  • OracleExtensions user module providng transparent SQL tracing support using Oracle's DBMS_APPLICATION_INFO package.

Example of creating an Oracle Datasource (note that db_encoding, host, and port are optional - using the hostname and port allows connections to be established without TNS, without these parameters TNS is used):

$db = new Datasource(SQL::DSOracle, $user, $pass, $db, $db_encoding, $host, $port);


$db = new Datasource("oracle:user/password@db%host:1522");

This driver supports the following DBI capabilities:


The Datasource::getServerVersion() and Datasource::getClientVersion() methods are implemented for this driver. Datasource::getServerVersion() returns a string giving server information similar to the following:

Oracle Database 10g Release - 64bit Production

The Datasource::getClientVersion() returns a hash giving version information in the following keys: major, minor, update, patch, port_update.

Note: There seems to be a bug in Oracle 9i and earlier in the streaming OCILobRead() function, where the LOB buffer must be at least twice as big as the LOB data to be read. This bug does not affect versions of the Qore oracle module linked with Oracle 10g libraries or later.

Object (Oracle named type) and collection support is supplied courtesy of ocilib (http://orclib.sourceforge.net/), note that ocilib was highly modified to be usable in this module, mostly due to the fact that we use a separate environment data structure for each connection to ensure maximum thread scalability - the Oracle docs say that all operations on an environment handle or any handle derived from an environment handle (i.e. statement handles, etc) must be either wrapped in a mutex (when initialized with OCI_NO_MUTEX) or will be wrapped in a mutex by oracle (with OCI_THREADED and without OCI_NO_MUTEX).

Driver Options

When compiled again Qore 0.8.6+ the oracle driver support the following DBI options on each connection:

  • "optimal-numbers": return NUMBER types as an integer if possible, if not as an arbitrary-precision number
  • "string-numbers": return NUMBER types as strings (for backwards-compatibility)
  • "numeric-numbers": return NUMBER types as arbitrary-precision number values
  • "timezone": accepts a string argument that can be either a region name (ex: "Europe/Prague") or a UTC offset (ex: "+01:00") to set the server's time zone rules; this is useful if connecting to a database server in a different time zone. If this option is not set then the server's time zone is assumed to be the same as the client's time zone; see Time Zone Support.

Options can be set in the Datasource or DatasourcePool constructors as in the following examples:

my Dataource $ds("oracle:user/pass@db{numeric-numbers,timezone=Europe/Vienna}");
my DataourcePool $dsp("oracle:user/pass@db%host.internal:1521{optimal-numbers}");

Options are stored separately for each connection.

Number Options

The number options ("optimal-numbers", "string-numbers", and "numeric-numbers") are all mutually-exclusive; setting one automatically disables the others. These options also ignore their arguments; the last one set takes effect (also note that setting it with any value sets the option, even False).

The default if no other option is explicitly set is "optimal-numbers". Note that this represents a change from previous versions where Oracle NUMBER values were returned as strings in order to avoid the loss of information. To set the old behavior, set the "string-numbers" option when creating the Datasource or DatasourcePool object.

Binding and types

When retrieving Oracle data, Oracle types are converted to Qore types as per Default Oracle to Qore Mappings.

Binding by value is supported for any statement executed through this driver; Oracle types are converted to Qore types as per Binding by Value.

Binding by placeholder is required to retrieve values from a procedure or function call. The oracle driver assumes that any placeholder values are string values unless a placeholder buffer specification is passed in the argument position corresponding to the placeholder specification in the string. For placeholder buffer specification values, see Binding by Placeholder.

Oracle TIMESTAMP data supports time resolution to the microsecond, however Qore's date/time value only supports a millisecond resolution. Any Oracle TIMESTAMP values are rounded to millisecond resolution when converted to Qore data types. See also Time Zone Support.

PL/SQL code and stored procedure and function execution is supported; the following is an example of a stored procedure call using bind by value and bind by placeholder (the v characters represent the positions for binding the arguments following the SQL string by value, and the placeholder names are prefixed by a colon):

$result = $db.exec("begin h3g_psft_order_import.insert_h3g_psft_customers(%v, %v, :status_code, :error_code, :error_description); end;",
"Customer Name", "Customer-ID", Type::Int, Type::Int);
printf("%N\n", $result);

This will bind the "Customer Name" and "Customer-ID" strings by value (as per Binding by Value these will be bound with Oracle type SQLT_STRING), and the output variables will be bound by placeholder (the first two will be bound as per Binding by Placeholder with buffers of Oracle type SQLT_INT, and the last placeholder buffer will get the default buffer type of SQLT_STRING), resulting in a hash giving the values of the output variables:

hash: (3 members)
status_code : 0
error_code : 0
error_description : <NULL>;

Time Zone Support

The driver now sets the server's time zone rules when the connection is established; this is taken from the current time zone settings of the calling Program object and can also be overridden/changed by setting the "timezone" driver option (see Driver Options).

All date/time values bound by value are converted to the server's time zone before binding to ensure that date/time values are stored correctly in the server.

When selecting date/time values, the values returned are tagged with the server's time zone.

Note that the above rules are applied when the current Program's time zone settings are different than the connection's time zone settings at the time when the write operation (bind) read operation (select) is performed. This is meant to provide consistent support to connecting to a database server in a different time zone.

Binding by Value

Argument OCI Type Description
Type::Binary SQLT_BIN For use with BLOB columns, for example.
Type::String SQLT_STR For use with string data, VARCHAR, CHAR, CLOB columns, etc
Type::Int SQLT_INT or SQLT_STR if the int > 32-bits = SQLT_STR, <= 32-bit int = SQLT_INT
Type::Boolean SQLT_INT True is bound as 1, False as 0
Type::Float SQLT_BDOUBLE For use with FLOAT, BINARY_FLOAT, BINARY_DOUBLE columns, etc
Type::Date SQLT_TIMESTAMP For use with DATE, TIMESTAMP, etc columns
Wrapped Type::Hash and Type::List SQLT_NTY For use with Named Types (Objects)

Binding by Placeholder

Argument OCI Type Description
Type::Binary SQLT_BIN For retrieving RAW data up to 65531 bytes in size.
SQL::BLOB SQLT_BLOB For retrieving BLOB data. The LOB handle is used to read the entire BLOB content into a binary object.
SQL::CLOB SQLT_CLOB For retrieving CLOB data. The LOB handle is used to read the entire CLOB content into a Qore string.
SQL::VARCHAR SQLT_STR For retrieving character data (VARCHAR2, etc). To specify a buffer size larger than 512 bytes, simply use the size in bytes as the argument. See CHAR and VARCHAR2 to Qore String
Type::Int SQLT_INT For retrieving integer numeric data up to 32 bits (for larger numbers or for non-integer numbers use SQL::VARCHAR or Type::Float.
Type::Float SQLT_BDOUBLE For retrieving data in 64-bit floating point format.
Type::Date SQLT_TIMESTAMP For retrieving dates and times.
Type::Hash SQLT_RSET For retrieving result sets from a stored procedure that returns a cursor.
Wrapped Type::Hash and Type::List SQLT_NTY For use with Named Types (Objects)

Default Oracle to Qore Mappings

Oracle Column Type Qore Type Notes
DATE Type::Date direct conversion
TIMESTAMP Type::Date (absolute) when compiled with qore 0.8.0+, microseconds are maintained
TIMESTAMP WITH TIME ZONE Type::Date (absolute) when compiled with qore 0.8.0+, time zone information and microseconds are maintained
TIMESTAMP WITH LOCAL TIME ZONE Type::Date (absolute) when compiled with qore 0.8.0+, time zone information and microseconds are maintained
INTERVAL YEAR TO MONTH Type::Date (relative) direct conversion to a relative date
INTERVAL DAY TO SECOND Type::Date (relative) direct conversion to a relative date
SMALLINT, INTEGER Type::Int direct conversion
NUMBER, NUMERIC, DECIMAL Type::String conversion to a string to avoid loss of precision
CLOB Type::String the LOB handle is used to read the entire CLOB content into a string
RAW, LONG RAW Type::Binary direct conversion
BLOB Type::Binary the LOB handle is used to read the entire BLOB content into a binary object
CURSOR (result set) Type::Hash the result set is returned as a hash of lists

CHAR and VARCHAR2 to Qore String

Direct Access to Tables/Views

Data returned from DatasourcePool::select() (and similar methods) use real string sizes:

  • full string for CHAR(n) including trailing spaces
  • full string for VARCHAR2(n). See example below for trailing spaces handling vs. column size.

Let's assume follwing data:

create table str_test (
str_char char(10),
str_var varchar2(10),
str_vars varchar2(10)
-- the spaces around string in the 3rd value are important!
insert into str_test values ('foo', 'foo', ' foo ');
printf("%N\n", $d.select("select * from str_test"));
hash: (3 members)
str_char : list: (1 element)
[0]="foo "
str_var : list: (1 element)
str_vars : list: (1 element)
[0]=" foo "

PL/SQL Wrappers

Data returned from PL/SQL code use different approach. Strings returned from this call are right-trimmed because OCI does not provide exact value size in this case by PL/SQL nature. The trimming is mandatory to avoid values with full allocated buffer (like strings with 512 spaces at the end).

-- sample PL/SQL procedure
create or replace procedure p_str_test(
o_str_char out str_test.str_char%type,
o_str_var out str_test.str_var%type,
o_str_vars out str_test.str_vars%type
select str_char, str_var, str_vars
into o_str_char, o_str_var, o_str_vars
from str_test where rownum < 2;
printf("%N\n", $d.exec("begin p_str_test(:str_char, :str_var, :str_vars); end;"));
hash: (3 members)
str_char : "foo"
str_var : "foo"
str_vars : " foo"

Named Types (Objects)

Qore Oracle driver supports Oracle objects and collections (NTY in the following text).

Special initialization of the driver is mandatory to use NTY in Qore scripts:

%requires oracle

This statement imports additional functions for NTY binding and fetching and therefore is required in Qore code that wants to use these functions - the automatic loading of DBI drivers on reference happens at run-time, therefore any references to the functions provided by this module can only be resolved at parse time if the module is explicitly required as above.

Function Description
Qore::Oracle::bindOracleObject() Binds a Qore value as a Object type typename. Hash keys are object attributes
Qore::Oracle::placeholderOracleObject() Allows fetching Object type typename. The returned hash is a plain Qore hash with keys set as the object's attributes
Qore::Oracle::bindOracleCollection() Binds value as a Collection typename
Qore::Oracle::placeholderOracleCollection() Allows fetching a Collection typename. The returned list is a plain Qore list of values with the collection's type

Type names (strings) are case insensitive.

Key names in value hashes are case sensitive and should follow Oracle uppercase naming convention.

Keys which are not found in the keys-attribute mappings are silently ignored. If there is a missing key for any attribute, an exception is thrown.

Functions can be nested so there can be for example list (collection) of objects and vice versa:

Sample named types defined in the DB:

AUTHID current_user AS OBJECT
attr_num number,
attr_varchar varchar2(20)
CREATE OR REPLACE TYPE test_collection IS TABLE OF test_object;

Example of binding NTY:

my hash $obj1 = ( "ATTR_NUM" : 1, "ATTR_VARCHAR" : "lorem ipsum" );
my hash $obj2 = ( "ATTR_NUM" : 2, "ATTR_VARCHAR" : "dolor sir amet" );
my list $l = ( bindOracleObject('test_object', $obj1),
bindOracleObject('test_object', $obj2) );
$db.exec("begin test_pkg.foo(%v); end;", bindOracleCollection('test_collection', $l));

Example of fetching NTY:

my $res = $db.exec("begin test_pkg.get_obj(:retval); end;",
printf("%N\n", $res);
# will print out
hash: (
"ATTR_NUM" : 5,
"ATTR_VARCHAR" : "foobar!"
Oracle Named Types (objects and collections) are custom data types stored directly in the Oracle system catalogue. They are real SQL types - created with CREATE [ OR REPLACE ] TYPE command. It's a common misundrestanding that PL/SQL types created for example in package specification can be used as NTY too, however this is an incorrect assumption. PL/SQL types cannot be used directly with the Oracle OCI library, however you can use custom wrappers or any other workarounds, of course.

Oracle Advanced Queueing (AQ)

Oracle Advanced Queueing (AQ) is the Oracle database's queue management feature. AQ provides a message queuing infrastructure as integral part of the Oracle server engine. It provides an API for enqueing messages to database queues. These messages can later be dequeued for asynchronous processing. Oracle AQ also provides functionality to preserve, track, document, correlate, and query messages in queues.


  • synchronous message posting
  • synchronouns message fetching
  • asynchronous event-driven listening in the DB queue

Special initialization of the driver is mandatory to use AQ in Qore scripts:

%requires oracle

Qore Oracle module contains these classes to handle AQ:

Class Description
Qore::Oracle::AQQueue Main queue handler
Qore::Oracle::AQMessage Enhanced features for message

Known Issues

Unfortunately there are some known bugs in the Oracle Module which cannot be fixed in the Qore driver right now. These are bugs in the underlying Oracle C Interface (OCI) library mostly.

Named Types Known Issues

  • Returning a collection of objects and Oracle client 10.x does not work correctly sometimes. There is only one instance of object (a first one) used for all items in the collection in some cases. Upgrade to Oracle client 11.x solves it.
  • Binding a collection of objects can lose VARCHAR2 attribute values replacing them with NULL. This behavior has been observed on Solaris SPARC architecture using Oracle CLient 11.1.x. Upgrading to 11.2.x solves the issue.
  • Oracle Metalink Bug #3604465 - bind named type by placeholder can cause ORA-21525 (attribute number or (collection element at index) s violated its constraints) if the object contains numbers with size constraint. An example:
TYPE a_object AS OBJECT (
my_num1 number(3), -- it will cause ORA-21525
my_num2 number -- it will work
my any $res = $db.exec("begin test_pkg.get_collection(:retval); end;",
# there is an exception expected: OCI-21710 but following crash appears:
OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
OCI-21710: argument is expecting a valid memory address of an object

Advaned Queueing Known Issues

  • Some versions of 11.2 client software is unable to receive change notifications/OCI Callbacks or AQ notifications against some versions of 11.2 database.

    Typically no errors result, and the observed behavior is that the notification simply does not occur.

    A row will be observed in DBA_CHANGE_NOTIFICATION_REGS, but then be removed, without notification occurring at the client.

    Note that the behavior of this problem is consistent, and no notifications at all will be received. This bug does not apply for intermittent behaviors.

    In the case of client pointing to database, the following error may occur:

    kpcerecv: incorrect iovec count=6210
    kpcest_err_handler: Max errors exceeded..Closing connection

    Behavior may be noticed as a result of upgrading either client or database software to the affected versions, or after applying a CPU that contains the fix for unpublished Bug 10065474.

    This behavior is a result of Oracle Bug 10065474, and is specific to 11.2 database.

    The fix is primarily a client side fix, however there are some database side changes that may be required once the client side fix is applied, so the fix for Oracle Bug 10065474 can actually CAUSE this behavior, if the change is not applied at both client and server.

    Solution: Apply patches or upgrade versions as applicable to obtain a working combination.

    Workaround: A workaround can be used in most cases to bypass the authentication phase, which is where the problem behavior occurs. To do that, set the following event in the database:

    alter system set events '10867 trace name context forever, level 1';
Server versionClient version failsworksworks

Release Notes

oracle Driver Version 3.1

New Features and Bug Fixes

  • fixed retrieving NUMBER types from objects/NTYs
  • added more Qore exception handling in ocilib object/NTY APIs
  • enforce DBI options on NTY retrieval with numeric values

oracle Driver Version 3.0

New Features and Bug Fixes

  • driver supports Oracle Advanced Queuing
  • driver supports selecting data from LONG columns (deprecated but used in system catalog tables for example)
  • driver now claims support for DBI_CAP_AUTORECONNECT, however the underlying functionality has been present for a long time

oracle Driver Version 2.2.1

Bug Fixes

  • fixed a bug where non-integer values were returned as integers
  • fixed a bug in returning negative integer values that were being returned as arbitrary-precision numeric values instead of integers
  • updated CLOB writing to use the streaming APIs instead of trying to write in one large block since there are bugs in some oracle client libraries related to trying to write large blocks, and using the streaming apis resolves the problem

oracle Driver Version 2.2

New Features and Bug Fixes

  • fixed bugs in named type support - when an unknown named type is accessed for the first time, ocilib keeps an invalid entry for the named type in the type list; the second time the type is accessed, a crash results (bug reported and already fixed in upstream ocilib)
  • fixed memory bugs in handling named type bind parameters created by hand where the value type is incorrect
  • now the driver throws an exception if SQLStatement::fetchRow() is called before SQLStatement::next() (before it would return garbage data)
  • supports the arbitrary-precision numeric type in qore 0.8.6+
  • supports DBI options with qore 0.8.6+; options supported:
    • "optimal-numbers": return NUMBER values as an integer if possible, if not as an arbitrary-precision number
    • "string-numbers": return NUMBER values as strings (for backwards-compatibility)
    • "numeric-numbers": return NUMBER values as arbitrary-precision number values
    • "timezone": accepts a string argument that can be either a region name (ex: "Europe/Prague") or a UTC offset (ex: "+01:00") to set the server's time zone rules; this is useful if connecting to a database server in a different time zone. If this option is not set then the server's time zone is assumed to be the same as the client's time zone; see Time Zone Support.
  • the default for the number options (see Number Options) is now "optimal-numbers" - meaning that NUMBER values are retrieved as int if possible, otherwise as a number type is returned.
    For backwards-compatibility, set the "string-numbers" option to return NUMBER values as strings