NAME

DBIO::Oracle::Storage - Oracle Support for DBIO

VERSION

version 0.900007

SYNOPSIS

# In your result (table) classes
use base 'DBIO::Core';
__PACKAGE__->add_columns(id => { sequence => 'mysequence', auto_nextval => 1 });
__PACKAGE__->set_primary_key('id');

# Somewhere in your Code
# add some data to a table with a hierarchical relationship
$schema->resultset('Person')->create ({
      firstname => 'foo',
      lastname => 'bar',
      children => [
          {
              firstname => 'child1',
              lastname => 'bar',
              children => [
                  {
                      firstname => 'grandchild',
                      lastname => 'bar',
                  }
              ],
          },
          {
              firstname => 'child2',
              lastname => 'bar',
          },
      ],
  });

# select from the hierarchical relationship
my $rs = $schema->resultset('Person')->search({},
  {
    'start_with' => { 'firstname' => 'foo', 'lastname' => 'bar' },
    'connect_by' => { 'parentid' => { '-prior' => { -ident => 'personid' } } },
    'order_siblings_by' => { -asc => 'firstname' },
  }
);

# this will select the whole tree starting from person "foo bar", creating
# following query:
# SELECT
#     me.personid, me.firstname, me.lastname, me.parentid
# FROM
#     person me
# START WITH
#     firstname = 'foo' and lastname = 'bar'
# CONNECT BY
#     parentid = prior personid
# ORDER SIBLINGS BY
#     firstname ASC

DESCRIPTION

This class implements base Oracle support. The subclass DBIO::Oracle::Storage::WhereJoins is for (+) joins in Oracle versions before 9.0; it is not selected automatically, set storage_type to +DBIO::Oracle::Storage::WhereJoins.

Date and timestamp formats

Every connection of this driver sets the ISO formats of datetime_setup (DBIO::Oracle::Storage::ConnectSetup) when it is made: NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS, NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF and NLS_TIMESTAMP_TZ_FORMAT YYYY-MM-DD HH24:MI:SS.FF TZHTZM, so that a DATE and a TIMESTAMP come back as YYYY-MM-DD ... strings without an on_connect_call. A format named in the environment variable of the same name is used instead. The setting is made before the application's own on_connect_call and on_connect_do, which can change it for their connection; on_connect_call => 'datetime_setup' is still accepted and sets the same formats again.

A DATE holds a date and a time of day. A date column keeps the time it is read and written with and follows the timezone of the column like a datetime ("datetime_date_carries_time" in DBIO::Storage::DBI).

Decimal character

Every connection also sets NLS_NUMERIC_CHARACTERS to ., for its session, so a number is bound and read with a decimal point in every NLS territory (NUMBER, BINARY_DOUBLE, BINARY_FLOAT), see DBIO::Oracle::Storage::ConnectSetup. The environment variable of that name is not used; an on_connect_do or on_connect_call of the application runs after and wins.

LOB length and character set

A connection sets the attributes the connect_info does not name: LongReadLen is 16 MiB (16*1024*1024), so a CLOB or BLOB is read as a string in full (DBD::Oracle's own default of 80 makes a longer value an error at the fetch), and ora_charset and ora_ncharset are AL32UTF8, so the client character set is UTF-8 whatever NLS_LANG says: a string with a wide character (the euro sign, Japanese) is sent and read back as it is, where a client without a UTF-8 NLS_LANG answers ?. A Perl string that is not flagged as characters is a sequence of Latin-1 code points, and DBD::Oracle would send it as bytes (a one-byte "\x{fc}" is no UTF-8): the storage upgrades such a bind itself, see "Strings without the UTF-8 flag". LongTruncOk stays off: a value longer than LongReadLen is an error, never silently cut. A value the connect_info names for any of these wins.

Strings without the UTF-8 flag

DBD::Oracle sends a Perl string without the UTF-8 flag as bytes, so under the client character set AL32UTF8 the Latin-1 string "\x{fc}" would be stored as U+FFFD. The storage therefore upgrades (utf8::upgrade) a bind whose column has a character type: char, varchar, varchar2, nchar, nvarchar, nvarchar2, text, clob, nclob, long (the data_type the bind carries as sqlt_datatype, the way bind_attribute_by_data_type decides). The upgrade is made on a copy; the caller's string is not changed. A binary type (blob, raw, long raw, binary) and a bind without a data type are sent unchanged, since their bytes are no characters. This holds for the bulk populate path too.

Set operations

Oracle spells set difference MINUS, not the ANSI EXCEPT that "_set_operation_keyword" in DBIO::Storage::DBI answers with by default, so this class overrides that hook (ADR 0039, which generalised ADR 0037's single-operation _except_operator to all six operations) and "except" in DBIO::ResultSet gets the right keyword without anyone comparing driver names. No version gate, and none is possible: MINUS is right on every release, having been the spelling since Oracle 8i and still being one in 23ai, whereas EXCEPT was only added alongside it in 21c and is a syntax error on everything older.

The same hook is where this driver refuses the two operations Oracle did not have until then. Before 21c the engine has exactly UNION, UNION ALL, INTERSECT and MINUS; 21c added EXCEPT, EXCEPT ALL, INTERSECT ALL and MINUS ALL (SQL Language Reference, The Set Operators, 19c and 21c). "except_all" in DBIO::ResultSet and "intersect_all" in DBIO::ResultSet used to emit EXCEPT ALL / INTERSECT ALL and come back as a syntax error from the server; they now throw at query-build time, before a single operand is rendered.

The refusal cannot ask which version it is talking to -- the hook is resolved while the query is being assembled and must not open a connection (ADR 0039 section 7) -- so it reads a class fact instead, and this driver declares the conservative one. A deployment that knows its server lifts it:

# this deployment runs Oracle 21c or newer
DBIO::Oracle::Storage->_use_set_operator_all_family(1);

Reach for the class-level form: it names the class, so it holds from load time and does not depend on any storage existing yet. The same switch can be set on a single storage, for a process that talks to more than one server. A schema declares its driver (core ADR 0067), so its storage is of this class from the start, and both per-storage forms work on a freshly connected one:

  • $schema->storage->_use_set_operator_all_family(1) -- the per-name accessor, which this class defines.

  • $schema->storage->set_use_dbms_capability('_use_set_operator_all_family', 1) -- the two-argument form underneath every _use_ accessor. It is name-independent: the setter writes the tier-1 slot, and the matching getter, $schema->storage->get_use_dbms_capability('_use_set_operator_all_family'), reads it.

Both instance forms write the same tier-1 force, and clearing is the same in either spelling: undef removes the force instead of leaving a tombstone behind, so an application that sets the switch for one storage and later turns it back gets this driver's 0 declaration again.

With the switch set, except_all renders MINUS ALL -- the same dialect as except, and equivalent to the EXCEPT ALL Oracle also accepts from 21c on -- and intersect_all inherits core's INTERSECT ALL, which is Oracle's own spelling. Left alone, both throw with a message naming the version boundary and this switch. The default is 0 because the releases this driver otherwise supports are the ones without the family at all: 11.2 and 19c are long-term releases, and a default of 1 would send them SQL the server rejects, which is the state this replaced.

Existence queries

"results_exist" in DBIO::ResultSet and its two siblings work here because this class declares the supports_exists_query capability (ADR 0037). Core defaults that capability to 0 and throws on all three methods without it, rather than degrading silently to "has_rows" in DBIO::ResultSet -- which stays the portable alternative for callers that must also run against a driver which has not declared it.

Declaring it alone would not be enough: core's base rendering of "_exists_value_sql" in DBIO::Storage::DBI is SELECT CASE WHEN EXISTS <inner> THEN 1 ELSE 0 END, a SELECT with no FROM clause, which Oracle rejects with ORA-00923. The override appends FROM DUAL and leaves the CASE spelling to core, so the statement is complete and the boolean arrives as an integer.

Random ordering

"rand" in DBIO::ResultSet asks the storage which function this engine spells "give me a random number" with ("_random_function" in DBIO::Storage::DBI) and drops the answer into an order_by literal. Core answers RANDOM(), which is right on PostgreSQL and SQLite and is not a function Oracle has at all, so the server reports it as an unknown identifier -- there is nothing for the name to resolve to. Oracle's is the supplied-package function DBMS_RANDOM.VALUE, and this class overrides the hook with it.

It is written without parentheses. DBMS_RANDOM.VALUE has a zero-argument overload and a two-argument one, and SQL has no empty argument list to write for the first, so DBMS_RANDOM.VALUE() is not the same string with optional punctuation -- it is a parse error.

Together with the rows that rand adds, $rs->rand(3) comes out as this driver's ROWNUM limit dialect wrapped around an ORDER BY DBMS_RANDOM.VALUE subquery. That nesting is the point rather than an artefact: the sort has to happen inside the subquery for the ROWNUM predicate outside it to pick three random rows instead of three arbitrary ones.

Query plans

"explain" in DBIO::ResultSet asks the storage for the whole operation of obtaining a query plan through "_explain_query" in DBIO::Storage::DBI (ADR 0040, which replaced the string-valued _explain_sql). Oracle is the engine that argument was made for, and this class overrides the hook for two reasons at once.

The first is spelling: core's inherited operation is EXPLAIN <query>, and Oracle has no EXPLAIN statement. It has EXPLAIN PLAN, and the bare form is a syntax error, ORA-00900.

The second is shape, and it is the larger half. EXPLAIN PLAN FOR does not answer with the plan: it writes the plan into PLAN_TABLE and returns no rows at all. The plan is read back by a second statement, SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY), which with no arguments displays the most recently explained statement of the session -- the one the first statement just wrote. No rewriting of a single statement string could have expressed that, which is why the seam is a method returning rows.

What comes back is DBMS_XPLAN's formatted report: one column, PLAN_TABLE_OUTPUT, one row per line, dashed rules and blank lines included. It is text meant to be read, not a table of plan attributes -- which is no more than ADR 0040 section 2 already says about every driver's plan rows, whose contents are engine-specific and which nothing in DBIO normalises.

Both statements go through "_execute_rendered" in DBIO::Storage::DBI, which the contract requires without engine-named exceptions. That is what keeps tracing and therefore "redact_bind_value" in DBIO::Storage::DBI (ADR 0025) over the query's own bind values, dbh_do retry over a dropped connection, DBIO::Storage::DBI::NoBindVars interpolation, and -- on a DBIO::Replicated::Storage -- the read handler instead of the master's.

What the session needs, and what happens when it does not have it

PLAN_TABLE has to be visible to the session. Since 10g the database ships one: SYS.PLAN_TABLE$, a global temporary table with a public synonym PLAN_TABLE, so a default installation needs no setup at all. Where that synonym has been dropped, or a schema carries an older private plan table left over from an earlier release, EXPLAIN PLAN FOR fails -- ORA-02404 for a missing plan table, ORA-00942 where the name does not resolve, ORA-01031 where it resolves but the session may not write to it. The exception surfaces from the first statement and propagates out of $rs->explain: this driver does not swallow it, and there is no degraded mode in which explain answers with an empty plan. The fix is a plan table of the session's own, created by @?/rdbms/admin/utlxplan.sql.

Two consequences of EXPLAIN PLAN being a statement that writes. The rows it inserts are part of the current transaction, so an explain inside one leaves it non-empty, and a rollback takes those rows with it -- harmless, because the plan has already been read by then. And because a global temporary table is session-private, the two statements must run on the same connection. They do: core delegates the whole _explain_query, not the individual statements inside it, so a DBIO::Replicated::Storage picks one replicant for the operation and both statements land on it.

What of this is verified

The seam is verified against core's source: the hook name and signature, that _explain_sql is gone rather than deprecated, that "explain" in DBIO::ResultSet strips as_query's parentheses before handing the statement over, and that DBIO::Replicated::Balancer forwards _explain_query as a whole (the previous paragraph's claim about one replicant).

Oracle's half is checked live: the live part of t/36-explain-query.t runs EXPLAIN PLAN, DBMS_XPLAN.DISPLAY and DBMS_RANDOM.VALUE against Oracle 23ai Free. No other release has been run. The offline part of the file pins what this driver puts on the wire and that it goes through the storage's statement path.

METHODS

insert

An insert of no values, $rs->create({}), cannot be written as the standard INSERT INTO t DEFAULT VALUES: Oracle does not know it (ORA-00926). It is sent as INSERT INTO t (col) VALUES (DEFAULT) for one column -- the first auto-increment column, else the first column of the source -- which leaves every column at its default, the named one too. That column is not a value of the row: it is not in what the insert hands back, unless the server returned it (a key the sequence and trigger made, read with RETURNING).

_insert_bulk

$rs->populate in void context inserts all rows with one array execution, which DBD::Oracle does not do for a LOB ("LOBs not supported for array operation"). A populate that writes a LOB column (CLOB, BLOB, any type "is_lob_type" in DBIO::Oracle::Type names) is therefore inserted row by row with "insert", in one transaction; every other populate is core's array execution.

_ddl_sql_dialect

Names the SQL dialect the install and upgrade DDL is split under: oracle, where a PL/SQL unit (a CREATE TRIGGER, a block) is one statement that keeps the semicolon after its END.

_mask_error_values

my $text = $storage->_mask_error_values($text, \@values, $statement);

Oracle 23ai writes the value of a statement into its error text, also when it was a bind: the first character of a string in ORA-01722, invalid string value in ORA-03302, the key of the row in ORA-03301, the value of ORA-01438, ORA-61800 and ORA-01428. The Oracle rules of "mask_error_details" in DBIO::Oracle::Util replace each of these values with *** whatever its length, and the code, the type, the column, the constraint and the table stay. The values bound to the statement are masked where they appear elsewhere in the text, and the statement the DBD appends to a message stays whole, literals included.

The Oracle rules run first and the core's masking after them (next::method): the rules read the key texts of the server as they are, which the core's masking of a bound value would cut into pieces. Core calls this wherever engine text reaches an exception (ADR 0075). The method never connects and never throws.

_connect_params_dsn

The DSN seam of "_connect_params_dsn" in DBIO::Storage::DBI (ADR 0057), overridden because DBD::Oracle takes nothing of core's default form dbi:Oracle:dbname=...;host=...;port=.... What follows dbi:Oracle: is a database identifier, and a ; in it switches DBD::Oracle to its keyed form (host=...;sid=...;port=...), which has no dbname key and refuses the default outright. The identifier has two shapes that dbname, host and port can fill, and which one the connection parameters mean is decided by host:

  • by name -- no host

    dbi:Oracle:<dbname>

    dbname is a name the Oracle client resolves, and what it means is the client's to say: a net service name from tnsnames.ora first, and a name that file does not define is read as an Easy Connect string (host[:port][/service_name]). The SID of an instance on the same machine is the third meaning the DBD::Oracle documentation gives. A name that is none of them does not connect (ORA-12154). Without dbname the identifier is empty, dbi:Oracle:, and the client takes the database from TWO_TASK or, for a local instance, ORACLE_SID; with neither set that does not connect either (ORA-12162).

  • Easy Connect -- with host

    dbi:Oracle://<host>[:<port>][/<dbname>]

    naming the server itself, no tnsnames.ora involved. Here dbname is the service name, not a SID: that is what the last part of an Easy Connect identifier is (//host[:port][/service_name] in the DBD::Oracle documentation), and it is the meaning that reaches every database -- a pluggable database is addressed by service name only. The keyed form would have carried the same three parts as host=...;sid=...;port=..., and would have pinned dbname to the SID.

A part not given is left out; nothing is put into the DSN that the parameters do not say:

  • host without port gives //<host>/<dbname>, no port. There is a documented default -- "If port name is not specified, 1521 is the default" -- and it is the Oracle client's to apply, so it is not written into the DSN here.

  • host without dbname gives //<host>[:<port>]. DBD::Oracle documents the service name as optional ("If service name is not specified, the hostname will be used as a service name"). What the client was seen to send is an empty service name, not the host name; which service a listener hands to a connection that names none is the server side's business, and none is made up here. It need not be the database the user is in: a listener in front of a container database answered a user of its pluggable database with ORA-01017, the error of a wrong password.

One combination refuses, the error naming the ways out:

  • port without host. A name the client resolves has no place for a port -- tnsnames.ora or the local instance holds it. Dropping the one given would connect to wherever they say, silently.

host goes into the DSN as given. An IPv6 literal therefore has to be given in brackets (host => '[2001:db8::1]'): the Oracle client does not parse one without them, and they are not added here.

Anything these three parts cannot say -- a SID on a named host, a full TNS descriptor, a TCPS address -- goes in as the whole DSN: as dsn, or as a dbname that starts with dbi:Oracle: (DBIO::AccessBroker::Static passes it on), which core takes as the DSN before this seam is asked.

DBIO::Oracle::Storage::WhereJoins inherits this method and builds the same DSN.

Verified against a server in t/38-connect-params-live.t, with Oracle Database 23ai Free, DBD::Oracle 1.95 and Instant Client 23: a listener hands out a session in the service named for //host:port/service; without a port the client goes to 1521; a bracketed IPv6 host is taken as the address and an unbracketed one is not; dbi:Oracle:<dbname> resolves through tnsnames.ora; a reconnect follows a host the broker changed. Two things were not: the SID of a local instance (the test machine has none), and a listener that itself answers on port 1521 or over IPv6 -- for both a relay on the test machine stood in front of the real listener, so what is shown is where the client goes. That DBD::Oracle refuses core's default form is observed offline (t/37-connect-params-dsn.t).

_bind_host_into_dsn

The host seam of "_bind_host_into_dsn" in DBIO::Storage::DBI (core ADR 0013, addendum 2026-10-09): what a DBIO::AccessBroker::HostBound view (a replicant of DBIO::Replicated) does with a DSN in the answer it wraps. Core's default knows the host=/port= key form only and declines the Easy Connect and TNS forms DBD::Oracle users write. This storage translates what has one unambiguous host and declines the rest:

  • Easy Connect, dbi:Oracle://host[:port][/service]: the view's host replaces the answer's, the service stays. The view's port replaces the port; a view without a port keeps the answer's, and when neither has one none is written (the client's default 1521 is its to apply). An IPv6 host is written in brackets, as the Oracle client needs it. dbi:Oracle(attr): prefixes are kept.

  • the key form, dbi:Oracle:host=...;sid=...;port=...: bound by core's default, as before.

  • refused, naming the form and never the DSN: a name the Oracle client resolves (dbi:Oracle:<alias>, or empty) -- a tnsnames.ora alias or a local SID has no host in the DSN, the client decides where it goes; a TNS descriptor ((DESCRIPTION=...) -- it may hold several ADDRESS entries, and which one the host replaces is ambiguous; and an Easy Connect whose host part cannot be read.

Offline pinned in t/69-hostbound-dsn.t; DBIO::Oracle::Storage::WhereJoins inherits it.

relname_to_table_alias

DBIO uses DBIO::Relationship names as table aliases in queries.

Unfortunately, Oracle doesn't support identifiers over 30 chars in length, so the DBIO::Relationship name is shortened and appended with half of an MD5 hash.

See "relname_to_table_alias" in DBIO::Storage::DBI.

connect_by or connect_by_nocycle

Value: \%connect_by

A hashref of conditions used to specify the relationship between parent rows and child rows of the hierarchy.

connect_by => { parentid => { -prior => { -ident => 'personid' } } }

# adds a connect by statement to the query:
# SELECT
#     me.personid, me.firstname, me.lastname, me.parentid
# FROM
#     person me
# CONNECT BY
#     parentid = PRIOR personid


connect_by_nocycle => { parentid => { -prior => { -ident => 'personid' } } }

# adds a connect by statement to the query:
# SELECT
#     me.personid, me.firstname, me.lastname, me.parentid
# FROM
#     person me
# CONNECT BY NOCYCLE
#     parentid = PRIOR personid

start_with

Value: \%condition

A hashref of conditions which specify the root row(s) of the hierarchy.

It uses the same syntax as "search" in DBIO::ResultSet

start_with => { firstname => 'Foo', lastname => 'Bar' }

# SELECT
#     me.persionid me.firstname, me.lastname, me.parentid
# FROM
#     person me
# START WITH
#     firstname = 'foo' and lastname = 'bar'
# CONNECT BY
#     parentid = prior persionid

order_siblings_by

Value: ($order_siblings_by | \@order_siblings_by)

Which column(s) to order the siblings by.

It uses the same syntax as "order_by" in DBIO::ResultSet

'order_siblings_by' => 'firstname ASC'

# SELECT
#     me.persionid me.firstname, me.lastname, me.parentid
# FROM
#     person me
# CONNECT BY
#     parentid = prior persionid
# ORDER SIBLINGS BY
#     firstname ASC

ATTRIBUTES

Following additional attributes can be used in resultsets.

SEE ALSO

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.