OracleSqlUtilBase Module Introduction

The OracleSqlUtilBase 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 OracleSqlUtilBase" in your code.

All the public symbols in the module are defined in the OracleSqlUtilBase 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:

*list<auto> 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 OracleSqlUtilBase module uses bind by value with the SQL IN operator.

For example, the following query:

*hash<auto> q = table.select({"where": {"col1": op_in(1, 2, 3, 4)}});

Results in the equivalent of the following SQL:

*hash<auto> 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 OracleSqlUtilBase; 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:

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

Which generates an SQL command like the following:

*list<auto> 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 OracleSqlUtilBase; the join option "partition" is supported to specify the partition to join on as in the following example:

*list<auto> $rows = table.selectRows({"join": join_inner($table2, "t2", ("id": "altid"), NOTHING,

{"partition": "p2"})});

Which generates an SQL command like the following:

*list<auto> $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 OracleSqlUtilBase:

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: OracleSqlUtilBase::OracleDatabase::OracleSchemaDescriptionOptions for a list of Oracle-specific schema description hash keys.

Release Notes

OracleSqlUtilBase v1.6

fixed a bug in the IN operator (issue 4901)
fixed space management on Oracle (issue 4897)
reintroduced as the base module for all Oracle SqlUtil modules

OracleSqlUtilBase v1.5

implemented support for generating queries based on generic expressions using the DataProvider module's generic expression support (issue 4538)

OracleSqlUtilBase v1.4.4

allow a VARCHAR column to be created without a size, assume a default size (issue 4399)

OracleSqlUtilBase v1.4.3

added APIs to enable duplicate record creation errors to be detected (issue 4308)

OracleSqlUtilBase v1.4.2

fixed a bug where keywords were not properly quoted in merge / upsert statements (issue 4297)

OracleSqlUtilBase v1.4.1

fixed a bug where pseudocolumn references were used ambiguously in joins (issue 4214)
fixed a bug where pseudocolumn names were quoted in queries leading to errors (issue 4210)
fixed a bug generating queries with limit and forupdate (issue 4206)

OracleSqlUtilBase v1.4

added support for the DataProvider module (issue 3545)

OracleSqlUtilBase v1.3.1

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

OracleSqlUtilBase v1.3

added support for serializing and deserializing AbstractTable objects (issue 2663)

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

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

OracleSqlUtilBase v1.2.3

fixed a bug in character_semantics for standalone column (issue 1688)
implemented cop_trunc_date() operator (issue 2032)

OracleSqlUtilBase v1.2.2

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

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

OracleSqlUtilBase v1.2

implemented support for the "returning" clause as an insert option
implemented support for views for DML in the OracleSqlUtilBase::OracleTable class
implemented OracleSqlUtilBase::OracleTable::getBaseType()
implemented support for Oracle pseudocolumns in queries
implemented support for SqlUtil::cop_cast() operator
fixed bugs in SqlUtil::cop_seq() and SqlUtil::cop_seq_currval() (issue 624)
return lists from Oracle's data dictionary ordered
implemented OracleSqlUtilBase::OracleTable::bindEmptyStringsAsNull()
implemented high-performance "upsert" (merge) support also supporting bulk DML
implemented SqlUtil::cop_substr() and SqlUtil::uop_substr() operators (issue 801)
implemented SqlUtil::op_substr() where operator (issue 883)

OracleSqlUtilBase 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

OracleSqlUtilBase v1.0

initial release

Table of Contents