Qore OracleSqlUtil Module Reference
1.4.4
|
The OracleSqlUtil module provides a high level API for working with Oracle database objects.
Usually this module is loaded on demand from the SqlUtil module, however to use this module directly, use "%requires OracleSqlUtil"
in your code.
All the public symbols in the module are defined in the OracleSqlUtil namespace
Select with paging on Oracle is more complicated than on some databases due to the lack of direct limit
and offset
support (applies to Oracle pre 12c).
For example, for the following select:
the following SQL is generated for Oracle with a pre 12c server:
note that the following simpler SQL is generated for Oracle 12c+ servers:
In order to avoid dynamic SQL and better manage the server's shared pool (in particular, the number of parsed statements), the OracleSqlUtil module uses bind by value with the SQL IN
operator.
For example, the following query:
Results in the equivalent of the following SQL:
Qore Type | Oracle Collection |
Date | SYS.ODCIDATELIST |
Float | SYS.ODCINUMBERLIST |
Integer | SYS.ODCINUMBERLIST |
Number | SYS.ODCINUMBERLIST |
String | SYS.ODCIVARCHAR2LIST |
There universal collections are limited to 32767 elements. And SYS.ODCIVARCHAR2LIST
element size is VARCHAR2(4000)
.
It's possible to select from a particular partition of a table with OracleSqlUtil; OracleSelectOptions defines the "partition"
key which can be added to a select option hash to specify the partition to select from as in the following example:
Which generates an SQL command like the following:
It's possible to perform a join on a particular partition of a table with OracleSqlUtil; the join option "partition"
is supported to specify the partition to join on as in the following example:
Which generates an SQL command like the following:
Note that when getting an object description from an Oracle database, if the object cannot be found in the connection schema, then if a synonym of the same type exists and the target object is accessible, then the target object is read automatically and the owning schema name is also set to the actual owner of the object.
Column types are mapped from Qore types as follows:
Oracle Column Type Mappings
Generic Type Name | Oracle Type Used |
float | number |
integer | number |
number | number |
string | varchar2 |
date | timestamp(6) |
binary | blob |
SqlUtil::BLOB | blob |
SqlUtil::CHAR | char |
SqlUtil::CLOB | clob |
SqlUtil::NUMERIC | number |
SqlUtil::VARCHAR | varchar2 |
To use other types, use the "native_type"
column description option with the native Oracle type name instead (under the "driver"
and "oracle"
keys for schemas supporting multiple databases).
The following additional schema objects can be managed with OracleSqlUtil:
The Schema Description Hash takes an optional key, "materialized_views"
that allows materialized views in Oracle schemas to be managed along with other objects.
The "materialized_views"
should be assigned to a hash, each key name is the name of the materialized view, and the values are hashes with the following keys:
"logging"
: (bool) if the materialized view should be logged"use_index"
: (bool) if the materialized view should be indexed"src"
: (bool) the source of the materialized viewThe "materialized_views"
key can go in the top level of the Schema Description Hash for Oracle-only schemas, or, for schemas targeting multiple database types, under the "driver"
and "oracle"
keys as in the following example:
The Schema Description Hash takes an optional key, "packages"
that allows packages in Oracle schemas to be managed along with other objects.
The "packages"
should be assigned to a hash, each key name is the name of the package, and the values are hashes with the following key:
"src"
: (bool) the source of the packageThe "packages"
key can go in the top level of the Schema Description Hash for Oracle-only schemas, or, for schemas targeting multiple database types, under the "driver"
and "oracle"
keys as in the following example:
The Schema Description Hash takes an optional key, "types"
that allows types in Oracle schemas to be managed along with other objects.
The "types"
should be assigned to a hash, each key name is the name of the type, and the values are strings giving the type definition.
The "types"
key can go in the top level of the Schema Description Hash for Oracle-only schemas, or, for schemas targeting multiple database types, under the "driver"
and "oracle"
keys as in the following example:
VARCHAR
column to be created without a size, assume a default size (issue 4399)limit
and forupdate
(issue 4206)CREATE MATERIALIZED VIEW
grant the table backing the view is created but the materialized view itself is not created causing future creation actions to fail (issue 2643)character_semantics
for standalone column (issue 1688)"returning"
clause as an insert option