Qore oracle Module
2.2
|
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).
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):
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:
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.
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
).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:
Options are stored separately for each connection.
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.
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):
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:
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.
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) |
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) |
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 |
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:
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:
Example of binding NTY:
Example of fetching NTY:
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.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.
type
alias to CHAR
does not use original dimension. outchar : "C"
is expectedORA-21525
(attribute number or (collection element at index) s violated its constraints) if the object contains numbers with size constraint. An example:placeholderOracleObject()
used in the place where is the placeholderOracleCollection()
expected by Oracle.Bug Fixes
New Features and Bug Fixes
"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.NUMBER
values are retrieved as int
if possible, otherwise as a number
type is returned."string-numbers"
option to return NUMBER
values as strings