Qore oracle Module

Table of Contents

• Introduction
• Driver Options
  • Number Options
• Binding and types
  • Time Zone Support
• Binding by Value
• Binding by Placeholder
• Default Oracle to Qore Mappings
• Named Types (Objects)
• Known Issues
  • Named Types Known Issues
• Release Notes
  • oracle Driver Version 2.2

Contents of this documentation:

• Introduction
• Driver Options
• Binding and types
• Binding by Value
• Binding by Placeholder
• Default Oracle to Qore Mappings
• Named Types (Objects)
• Known Issues
• Release Notes

Introduction

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).

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);

This driver supports the following DBI capabilities:

• DBI_CAP_TRANSACTION_MANAGEMENT
• DBI_CAP_STORED_PROCEDURES
• DBI_CAP_CHARSET_SUPPORT
• DBI_CAP_LOB_SUPPORT
• DBI_CAP_BIND_BY_VALUE
• DBI_CAP_BIND_BY_PLACEHOLDER
• DBI_CAP_HAS_EXECRAW
• DBI_CAP_HAS_STATEMENT
• DBI_CAP_HAS_SELECT_ROW
• DBI_CAP_HAS_NUMBER_SUPPORT
• DBI_CAP_HAS_OPTION_SUPPORT
• DBI_CAP_SERVER_TIME_ZONE

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 10.2.0.1.0 - 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.

Note

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 (VARCHAR, etc). To specify a buffer size larger than 512 bytes, simply use the size in bytes as the argument.
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
REAL, FLOAT, DOUBLE PRECISION, BINARY_FLOAT, BINARY_DOUBLE	Type::Float	direct conversion
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

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:

CREATE OR REPLACE TYPE test_object
    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;",
                   placeholderOracleObject("TEST_OBJECT"));
printf("%N\n", $res);

# will print out
hash: (
    "ATTR_NUM" : 5,
    "ATTR_VARCHAR" : "foobar!"
)

Warning

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.

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.

• Bind by placeholder using PL/SQL's type alias to CHAR does not use original dimension.

  CREATE TABLE t1 ( c CHAR(1) );
  INSERT INTO t1 VALUES ('C');
  COMMIT;
  
  create or replace procedure foo(tmp out t1.c%type)
  is
  begin
      select c into tmp from t1 where rownum < 2;
  end;
  /

  my Datasource $d("oracle", "test", "test", "test");
  on_exit $d.rollback();
  my any $r = $d.exec("begin foo(:outchar); end;", Type::String);
  printf("%N\n", $r);
  
  Result:
  hash: (1 member)
  outchar: "C                                                                
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                            "

  while outchar : "C" is expected

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
)

• A crash when is the placeholderOracleObject() used in the place where is the placeholderOracleCollection() expected by Oracle.

my any $res = $db.exec("begin test_pkg.get_collection(:retval); end;",
                       placeholderOracleObject("test_object"));

# 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

Release Notes

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