Qore OracleSqlUtil Module Reference  1.4
OracleSqlUtil Module

Table of Contents

OracleSqlUtil Module Introduction

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

Note
This module requires the oracle binary module for communication with Oracle databases

SQL Operations on Oracle

Select With Paging on Oracle

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:

my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));

the following SQL is generated for Oracle with a pre 12c server:

$ds.vselectRows("select * from (select /*+ first_rows(100) */ a.*, rownum rnum from (select * from schema.table where type = %v order by type) a where rownum <= %v) where rnum > %v", ("user", 300, 200));

note that the following simpler SQL is generated for Oracle 12c+ servers:

$ds.vselectRows("select * from schema.table where type = %v order by type offset %v rows fetch next %v rows only", ("user", 200, 100));

IN Operator on Oracle

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:

my *hash $q = $table.select(("where": ("col1": op_in(1, 2, 3, 4))));

Results in the equivalent of the following SQL:

my *hash $q = $ds.select("select * from schema.table where col1 in (select column_value from table(%v))", bindOracleCollection("SYS.ODCIVARCHAR2LIST", (1,2,3,4)));

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

Partition Support in Selects

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:

my *list $rows = $table.selectRows(("created": op_gt(2012-05-01), "partition": "p1"));

Which generates an SQL command like the following:

my *list $rows = $ds.vselectRows("select * from schema.table partition(p1) where created > %v", (2012-05-01));

Partition Support in Joins

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:

my *list $rows = $table.selectRows(("join": join_inner($table2, "t2", ("id": "altid"), NOTHING, ("partition": "p2"))));

Which generates an SQL command like the following:

my *list $rows = $ds.vselectRows("select * from schema.table inner join schema.table2 partition(p2) t2 on (schema.table.id = t2.altid)");

Schema Management on Oracle

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.

Type Mapping

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

Additional Object Types Supported

The following additional schema objects can be managed with OracleSqlUtil:

Materialized Views

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 view

The "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:

hash schema = (
"driver": (
"oracle": (
"materialized_views": (
"example_mv": (
"logging": False,
"use_index": False,
"src": "select type, count(1) total_count from table group by type",
),
),
),
),
);

Packages

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 package

The "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:

hash schema = (
"driver": (
"oracle": (
"packages": (
"example_pkg": (
"src": "types as
type cursorType is ref cursor;
MYSTATCOMPLETE constant order_status.orderstatus%type := 'C';
MYSTATERROR constant order_status.orderstatus%type := 'E';
",
),
),
),
),
);

Types

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:

hash schema = (
"driver": (
"oracle": (
"types": (
"num_array": "table of number",
"str_array": "table of varchar2(240)",
),
),
),
);
See also
OracleSqlUtil::OracleDatabase::OracleSchemaDescriptionOptions for a list of Oracle-specific schema description hash keys.

Release Notes

OracleSqlUtil v1.4

OracleSqlUtil v1.3.1

  • fixed quoting of reserved words in column names in table alignment (issue 3400)

OracleSqlUtil v1.3

OracleSqlUtil v1.2.5

  • worked around an Oracle bug in materialized view creation where when the schema user is missing the 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)

OracleSqlUtil v1.2.4

  • implemented support for custom column operators (issue 2314)
  • implemented support for chained synonyms (issue 2408)
  • allow to use DBA_* views instead of ALL_* if possible (issue 2418)

OracleSqlUtil v1.2.3

OracleSqlUtil v1.2.2

  • fixed a bug in the force option (i.e. cascade) for dropping types (issue 1683)

OracleSqlUtil v1.2.1

  • implemented the force option (i.e. cascade) for dropping code objects (issue 1314)
  • worked around ORA-22165 from op_in() caused by Oracle's limit on number of collection elements (issue 1660)

OracleSqlUtil v1.2

OracleSqlUtil v1.1

  • fixed selects with "limit" but no "offset"
  • convert date/time values to timestamps with microseconds resolution instead of dates with second resolution when dynamically inserting values as strings in SQL (binding by value not affected)
  • fixed schema information classes when the "string-numbers" driver option is enabled

OracleSqlUtil v1.0

  • initial release