DBD::Oracle(3pm)
💖 DBD::Oracle — the manual, but make it make sense 💖
User Contributed Perl Documentation · DBD::Oracle(3pm)
okay bestie, sit down, hydrate, because we're reading the whole Oracle driver manual together and honestly? it's giving ✨powerful✨. this is DBD::Oracle, the Oracle database driver for the DBI module. no cap.
✨ VERSION ✨
version 1.83 💅
🦋 SYNOPSIS 🦋
here's the vibe, literally just the whole thing:
use DBI;
$dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);
$dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $passwd);
# See the DBI module documentation for full details
# for some advanced uses you may need Oracle type values:
use DBD::Oracle qw(:ora_types);like, that's it, that's the whole entrance. so clean. 🧚
🌸 DESCRIPTION 🌸
okay so DBD::Oracle is a Perl module that teams up with the DBI module to give you access to Oracle databases. iconic duo behavior honestly.
this documentation is describing the driver specific behavior and restrictions. it is not supposed to be your only reference, bestie. in any case, consult the DBI documentation first! fr fr. 🙈
💜 CONSTANTS 💜
:ora_session_modes
ORA_SYSDBA ORA_SYSOPER ORA_SYSASM ORA_SYSBACKUP ORA_SYSDG ORA_SYSKM:ora_types
ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB
ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI
SQLT_CHR SQLT_BINSQLCS_IMPLICIT, SQLCS_NCHAR
SQLCS_IMPLICIT and SQLCS_NCHAR are character set form values. see the notes about Unicode elsewhere in this document. 📝
SQLT_INT, SQLT_FLT
these types are used only internally, and may be specified as internal bind type for ORA_NUMBER_TABLE. see the notes about ORA_NUMBER_TABLE elsewhere in this document. 👀
ORA_OCI
Oracle doesn't provide a formal API for figuring out the exact version number of the OCI client library in use, so DBD::Oracle has to go digging (and sometimes has to more or less guess). the ORA_OCI constant holds the result of that whole detective arc. 🔍
- in string context,
ORA_OCIreturns the full"A.B.C.D"version string. - in numeric context,
ORA_OCIreturns the major.minor version number (8.1,9.2,10.0etc). but bestie, note that version numbers are not actually floating point, so if Oracle ever makes a release with a two digit minor version, like9.10, it will have a lower numeric value than the preceding9.9release. so use with care. 💀
the contents and format of ORA_OCI are subject to change (it may, for example, become a version object in later releases). the author recommends that you avoid checking for exact values. 🙅
:ora_fetch_orient
OCI_FETCH_CURRENT OCI_FETCH_NEXT OCI_FETCH_FIRST OCI_FETCH_LAST
OCI_FETCH_PRIOR OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVEthese constants are used to set the orientation of a fetch on a scrollable cursor. 🌀
:ora_exe_modes
OCI_STMT_SCROLLABLE_READONLY:ora_fail_over
OCI_FO_END OCI_FO_ABORT OCI_FO_REAUTH OCI_FO_BEGIN OCI_FO_ERROR
OCI_FO_NONE OCI_FO_SESSION OCI_FO_SELECT OCI_FO_TXNAL OCI_FO_RETRY👑 DBI CLASS METHODS 👑
🎀 connect
this method creates a database handle by connecting to a database, and it's the DBI equivalent of the "new" method. to open a connection to an Oracle database you need to specify a database connection string (URL), username and password. the whole trio. 💫
the connection string is always of the form: "dbi:Oracle:<db identifier>". there are several ways to identify a database:
- if the database is local, specifying the SID or service name will be enough.
- if the database is defined in a
TNSNAMES.ORAfile, you can use the service name given in the file. - to connect without a
TNSNAMES.ORAfile, you can use an EZCONNECT url, of the form://host[:port][/service_name]
if port name is not specified, 1521 is the default. if service name is not specified, the hostname will be used as a service name.
the following examples show several ways a connection can be created:
$dbh = DBI->connect('dbi:Oracle:DB','username','password');
$dbh = DBI->connect('dbi:Oracle:DB','username/password','');
$dbh = DBI->connect('dbi:Oracle:','username@DB','password');
$dbh = DBI->connect('dbi:Oracle:host=foobar;sid=DB;port=1521', 'scott/tiger', '');
$dbh = DBI->connect("dbi:Oracle://myhost:1522/ORCL",'username', 'password');🦋 OS authentication
to connect to a local database with a user which has been set up to authenticate via the OS (ALTER USER username IDENTIFIED EXTERNALLY):
$dbh = DBI->connect('dbi:Oracle:','/','');note the lack of a connection name (use the ORACLE_SID environment variable). if an explicit SID is used you will probably get an ORA-01004 error. 😭
that only works for local databases. (authentication to remote Oracle databases using your Unix login name without a password is possible but it is not secure and not recommended so not documented here.) 🙈
🌈 Oracle Environment Variables
to use DBD::ORACLE to connect to an Oracle database, the ORACLE_HOME environment variable should be set correctly. in general, the value used should match the version of Oracle that was used to build DBD::Oracle. if using dynamic linking then ORACLE_HOME should match the version of Oracle that will be used to load in the Oracle client libraries (via LD_LIBRARY_PATH, ldconfig, or similar on Unix).
Oracle can use two environment variables to specify default connections: ORACLE_SID and TWO_TASK.
to use them, specify either a local SID or service name, or a service name that is specified in the TNSNAMES.ORA file.
note that if you have both local and remote databases, and you have ORACLE_SID and TWO_TASK set, and you don't specify a fully qualified connect string on the command line, TWO_TASK takes precedence over ORACLE_SID (i.e. you get connected to remote system). 😳
it is highly recommended not to rely on environment variables and to always explicitly specify the SID in the connection string. this can prevent serious mistakes such as dropping a schema in the wrong database, and generally makes debugging and troubleshooting easier. 🚨
also remember that depending on the operating system you are using the differing "ORACLE" environment variables may be case sensitive, so if you are not connecting as you should, double check the case of both the variable and its value. 🔤
💜 Timezones
if the query is run through SQL*Net (mostly queries that are executed on remote servers), Oracle will return the time zone based on the setting of the UNIX environment variable "TZ" for the user who started the listener.
if the query is run locally, Oracle will return the time zone based on the "TZ" environment variable setting of the user running the query.
with local queries, you can change the time zone for a particular user by simply changing the setting of "TZ". to check the current setting, issue the UNIX "date" command. 🕐
🍓 Oracle DRCP
DBD::Oracle supports DRCP (Database Resident Connection Pool) so if you have an 11.2 database and DRCP is enabled you can direct all of your connections to it by adding ':POOLED' to the SID, or setting a connection attribute of ora_drcp, or set the SERVER=POOLED when using a TNSENTRY style connection, or even by setting an environment variable ORA_DRCP. all of which are demonstrated below;
$dbh = DBI->connect('dbi:Oracle:DB:POOLED','username','password')
$dbh = DBI->connect('dbi:Oracle:','username@DB:POOLED','password')
$dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1})
$dbh = DBI->connect('dbi:Oracle:DB','username','password',{ora_drcp=>1,
ora_drcp_class=>'my_app',
ora_drcp_min =>10})
$dbh = DBI->connect('dbi:Oracle:host=foobar;sid=ORCL;port=1521;SERVER=POOLED', 'scott/tiger', '')
$dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
(CONNECT_DATA=(SID=ORCL)(SERVER=POOLED)))}, "")
if the ORA_DRCP environment variable is set then just this
$dbh = DBI->connect('dbi:Oracle:DB','username','password')you can find a white paper on setting up DRCP and its advantages at <http://www.oracle.com/technetwork/articles/oracledrcp11g-1-133381.pdf>. 📄
please note that DRCP support in DBD::Oracle is relatively new so the mechanics or its implementation are subject to change. ⚠️
🧚 TAF (Transparent Application Failover)
Transparent Application Failover (TAF) is the feature in OCI that allows for clients to automatically reconnect to an instance in the event of a failure of the instance. the reconnect happens automatically from within the OCI (Oracle Call Interface) library. DBD::Oracle now supports a callback function that will fire when a TAF event takes place. you may use the callback to inform the user a failover is taking place, or to setup the session again once the failover has succeeded. 🔥
you will have to set up TAF on your instance before you can use this callback. you can test your instance to see if you can use TAF callback with
$dbh->ora_can_taf();if you try to set up a callback without it being enabled, DBD::Oracle will croak. 😤
NOTE: currently, you must enable TAF during DBI's connect. however once enabled you can change the TAF settings.
it is outside the scope of this document to go through all of the possible TAF situations you might want to set up but here is a simple example:
the TNS entry for the instance has had the following added to the CONNECT_DATA section
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic)
(RETRIES=10)
(DELAY=10))you will also have to create your own perl function that will be called from the client. you can name it anything you want and it will always be passed two parameters, the failover event value and the failover type. you can also set a sleep value in case of failover error and the OCI client will sleep for the specified seconds before it attempts another event. 😴
use DBD::Oracle(qw(:ora_fail_over));
#import the ora fail over constants
#set up TAF on the connection
# NOTE since DBD::Oracle uses call_pv you may need to pass a full
# name space as the function e.g., 'main::handle_taf'
# NOTE from 1.49_00 ora_taf_function can accept a code ref as well
# as a sub name as it now uses call_sv
my $dbh = DBI->connect('dbi:Oracle:XE', 'hr', 'hr',
{ora_taf_function => 'main::handle_taf'});
#create the perl TAF event function
sub handle_taf {
# NOTE from 1.49_00 the $dbh handle was passed to your callback
my ($fo_event,$fo_type, $dbh) = @_;
if ($fo_event == OCI_FO_BEGIN){ print " Instance Unavailable Please stand by!! \n";
printf(" Your TAF type is %s \n",
(($fo_type==OCI_FO_NONE) ? "NONE"
:($fo_type==OCI_FO_SESSION) ? "SESSION"
:($fo_type==OCI_FO_SELECT) ? "SELECT"
: "UNKNOWN!"));
}
elsif ($fo_event == OCI_FO_ABORT){
print " Failover aborted. Failover will not take place.\n";
}
elsif ($fo_event == OCI_FO_END){
printf(" Failover ended ...Resuming your %s\n",(($fo_type==OCI_FO_NONE) ? "NONE"
:($fo_type==OCI_FO_SESSION) ? "SESSION"
:($fo_type==OCI_FO_SELECT) ? "SELECT"
: "UNKNOWN!"));
}
elsif ($fo_event == OCI_FO_REAUTH){
print " Failed over user. Resuming services\n";
}
elsif ($fo_event == OCI_FO_ERROR){
print " Failover error ...\n";
sleep 5; # sleep before having another go
return OCI_FO_RETRY;
}
else {
printf(" Bad Failover Event: %d.\n", $fo_event);
}
return 0;
}okay so those are your TAF event constants in action, and here's what each one actually means bestie 💅
The TAF types are as follows
OCI_FO_SESSION— the user has requested only session failover.OCI_FO_SELECT— the user has requested select failover.OCI_FO_NONE— the user has not requested a failover type.OCI_FO_TXNAL— the user has requested transaction failover.
The TAF events are as follows
OCI_FO_BEGIN— indicates that failover has detected a lost connection and failover is starting.OCI_FO_END— indicates successful completion of failover. slay.OCI_FO_ABORT— indicates that failover was unsuccessful, and there is no option of retrying. it's giving dead end 😭OCI_FO_ERROR— also indicates that failover was unsuccessful, but it gives the application the opportunity to handle the error and retry failover.OCI_FO_REAUTH— indicates that you have multiple authentication handles and failover has occurred after the original authentication. It indicates that a user handle has been re-authenticated. To find out which, the application checks theOCI_ATTR_SESSIONattribute of the service context handle (which is the first parameter).
✨ Connect Attributes ✨
ora_ncs_buff_mtpl
You can customize the size of the buffer when selecting LOBs with the built-in AUTO Lob. The default value is 4 which is probably excessive for most situations but is needed for backward compatibility. If you not converting between a NCS on the DB and the Client then you might want to set this to 1 to reduce memory usage.
This value can also be specified with the ORA_DBD_NCS_BUFFER environment variable in which case it sets the value at the connect stage.
ora_drcp
For Oracle 11.2 or greater.
Set to 1 to enable DRCP. Can also be set via the ORA_DRCP environment variable.
ora_drcp_class
If you are using DRCP, you can set a CONNECTION_CLASS for your pools as well. As sessions from a DRCP cannot be shared by users, you can use this setting to identify the same user across different applications. OCI will ensure that sessions belonging to a 'class' are not shared outside the class'.
The values for ora_drcp_class cannot contain a '*' and must be less than 1024 characters.
This value can be also be specified with the ORA_DRCP_CLASS environment variable.
Note that a connection class must be specified in order to enable inter-process sharing of server side sessions.
ora_drcp_min
This optional value specifies the minimum number of sessions that are initially allocated for the application process. New sessions are only allocated after this value has been reached.
The default value is 0 and any value greater than or equal to 0 is valid.
For multi-process applications, it is recommended to leave the value at 0. This ensures that each process is only occupying a server session while the process is doing database work.
For multi-threaded applications, the value could be set to the number of concurrent statements the application is planning or expecting to run. Please note that DRCP has not been tested with multi-threading.
This value can also be specified with the ORA_DRCP_MIN environment variable.
ora_drcp_max
This optional value specifies the maximum number of sessions that can be open at one time. Once reached no more sessions can be opened until one becomes free. The default value is 40 and any value above 0 is valid. You should not set this value lower than ora_drcp_min as that will just waste resources.
This value can also be specified with the ORA_DRCP_MAX environment variable.
ora_drcp_incr
This optional value specifies the next increment for sessions to be started if the current number of sessions are less than ora_drcp_max. The default value is 1 and any value above 0 is valid as long as the value of ora_drcp_min + ora_drcp_incr is not greater than ora_drcp_max.
This value can also be specified with the ORA_DRCP_INCR environment variable.
ora_drcp_rlb
This optional value controls whether run-time connection load balancing is used for Oracle RAC. The default value is 0, which disables the feature. Set the value to 1 to enable the feature.
This value can also be specified with the ORA_DRCP_RLB environment variable.
ora_taf
This attribute was removed in 1.49_00 as it was redundant. To enable TAF simply set ora_taf_function.
ora_taf_function
If your Oracle instance has been configured to use TAF events you can enable the TAF callback by setting this option.
The name of the Perl subroutine (or a code ref from 1.49_00) that will be called from OCI when a TAF event occurs. You must supply a perl function to use the callback and it will always receive at least two parameters; the failover event value and the failover type. From 1.49_00 the dbh is passed as the third argument. Below is an example of a TAF function
sub taf_event{
# NOTE from 1.49_00 the $dbh handle is passed to the callback
my ($event, $type, $dbh) = @_;
print "My TAF event=$event\n";
print "My TAF type=$type\n";
return;
}Note if passing a sub name you will probably have to use the full name space when setting the TAF function e.g.,'main::my_taf_function'and not just'my_taf_function'.
ora_taf_sleep
This attribute was removed in 1.49_00 as it was redundant. If you want to sleep between retries simple add a sleep to your callback sub.
ora_session_mode
The ora_session_mode attribute can be used to connect with SYSDBA, SYSOPER, ORA_SYSASM, ORA_SYSBACKUP, ORA_SYSKM and ORA_SYSDG authorization. The ORA_SYSDBA, ORA_SYSOPER, ORA_SYSASM, ORA_SYSBACKUP, ORA_SYSKM and ORA_SYSDG constants can be imported using
use DBD::Oracle qw(:ora_session_modes);This is one case where setting ORACLE_SID may be useful since connecting as SYSDBA or SYSOPER via SQL*Net is frequently disabled for security reasons.
Example:
$dsn = "dbi:Oracle:"; # no dbname here
$ENV{ORACLE_SID} = "orcl"; # set ORACLE_SID as needed
delete $ENV{TWO_TASK}; # make sure TWO_TASK isn't set
$dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA });It has been reported that this only works if $dsn does not contain a SID so that Oracle then uses the value of ORACLE_SID (not TWO_TASK) environment variable to connect to a local instance. Also the username and password should be empty, and the user executing the script needs to be part of the dba group or osdba group.
Note that this does not work with DRCP.
ora_oratab_orahome
Passing a true value for the ora_oratab_orahome attribute will make DBD::Oracle change $ENV{ORACLE_HOME} to make the Oracle home directory that specified in the /etc/oratab file if the database to connect to is specified as a SID that exists in the oratab file, and DBD::Oracle was built to use the Oracle 7 OCI API (not Oracle 8+).
ora_module_name
After connecting to the database the value of this attribute is passed to the SET_MODULE() function in the DBMS_APPLICATION_INFO PL/SQL package. This can be used to identify the application to the DBA for monitoring and performance tuning purposes. For example:
my $dbh = DBI->connect($dsn, $user, $passwd, { ora_module_name => $0 });
$dbh->{ora_module_name} = $y;The maximum size is 48 bytes.
NOTE: You will need an Oracle client 10.1 or later to use this.
ora_driver_name
For 11g and later you can now set the name of the driver layer using OCI. Perl, Perl5, ApachePerl so on. Names starting with "ORA" are reserved. You can enter up to 8 characters. If none is enter then this will default to DBDOxxxx where xxxx is the current version number. This value can be retrieved on the server side using V$SESSION_CONNECT_INFO or GV$SESSION_CONNECT_INFO
my $dbh = DBI->connect($dsn, $user, $passwd, { ora_driver_name => 'ModPerl_1' });
$dbh->{ora_driver_name} = $q;ora_client_info
Allows you to add any value (up to 64 bytes) to your session and it can be retrieved on the server side from the V$SESSION view.
my $dbh = DBI->connect($dsn, $user, $passwd, { ora_client_info => 'Remote2' });
$dbh->{ora_client_info} = "Remote2";NOTE: You will need an Oracle client 10.1 or later to use this.
ora_client_identifier
Allows you to specify the user identifier in the session handle.
Most useful for web applications as it can pass in the session user name which might be different to the connection user name. Can be up to 64 bytes long but do not to include the password for security reasons and the first character of the identifier should not be ':'. This value can be retrieved on the server side using V$SESSION view.
my $dbh = DBI->connect($dsn, $user, $passwd, { ora_client_identifier => $some_web_user });
$dbh->{ora_client_identifier} = $local_user;ora_action
Allows you to specify any string up to 32 bytes which may be retrieved on the server side using V$SESSION view.
my $dbh = DBI->connect($dsn, $user, $passwd, { ora_action => "Login"});
$dbh->{ora_action} = "New Long Query 22";NOTE: You will need an Oracle client 10.1 or later to use this.
ora_dbh_share
Requires at least Perl 5.8.0 compiled with ithreads (interpreter-based threads).
Allows you to share database connections between threads. The first connect will make the connection, all following calls to connect with the same ora_dbh_share attribute will use the same database connection. The value must be a reference to a already shared scalar which is initialized to an empty string.
our $orashr : shared = '' ;$dbh = DBI->connect ($dsn, $user, $passwd, {ora_dbh_share => \$orashr}) ;So like, with ithreads DBD::Oracle will leak handles, because it literally cannot tell when the last driver handle gets destroyed. 💀
And pls bestie, keep in mind that ithreads are officially discouraged. That's not me being dramatic, that's just facts. 🙈
🌸 ora_events 🌸
Set this attribute to 1 to enable Oracle Fast Application Notification (FAN) in a new OCI environment. Can also be set via the ORA_EVENTS environment variable. ✨
🌸 ora_envhp 🌸
The very first time a connection is made, a new OCI 'environment' gets created by DBD::Oracle and stashed in the driver handle. After that, subsequent connects reuse (share) that same OCI environment by default. iykyk.
The ora_envhp attribute can be used to disable the reuse of the OCI environment from a previous connect. If the value is 0 then a fresh new OCI environment is allocated and used for this connection. 🆕
The OCI environment holds info about the client side context, like the local NLS environment. So by altering %ENV and setting ora_envhp to 0 you can create connections with different NLS settings. This is most useful for testing. 🧪
Note that for DRCP, setting "ora_envhp = 0" has no effect. Here, a new session pool is created, using the current NLS environment, for each new combination of dbname, uid/pwd, connection class, and charset/ncharset.
🌸 ora_charset, ora_ncharset 🌸
For oracle versions >= 9.2 you can specify the client charset and ncharset with the ora_charset and ora_ncharset attributes. You still need to pass "ora_envhp = 0" for all but the first connect. 💅
These attributes override the settings from environment variables.
$dbh = DBI->connect ($dsn, $user, $passwd,
{ora_charset => 'AL32UTF8'});🌸 ora_verbose 🌸
Use this value to enable DBD::Oracle only tracing. Simply either set the ora_verbose attribute on the connect() method to the trace level you desire like this:
my $dbh = DBI->connect($dsn, "", "", {ora_verbose=>6});or set it directly on the DB handle like this:
$dbh->{ora_verbose} =6;In both cases the DBD::Oracle trace level is set to 6, which is the highest level tracing most of the calls to OCI. 🔥
NOTE: In future versions of DBD::Oracleora_verbosewill be changed so that it is simply a switch to turn DBI's DBD tracing on or off. A true value will turn it on and a false value will turn it off. DBI's"DBD"tracing was not available whenora_verbosewas created andora_verboseadds an additional test to every trace test.
🌸 ora_oci_success_warn 🌸
Use this value to print otherwise silent OCI warnings that may happen when an execute or fetch returns "Success With Info" or when you want to tune RowCaching and LOB Reads 👀
$dbh->{ora_oci_success_warn} = 1;🌸 ora_objects 🌸
Use this value to enable extended embedded oracle objects mode. In extended:
- Embedded objects are returned as
<DBD::Oracle::Object>instance (including type-name etc.) instead of simpleARRAY. - Determine object type for each instance. All object attributes are returned (not only super-type's attributes).
$dbh->{ora_objects} = 1;🌸 ora_ph_type 🌸
The default placeholder datatype for the database session. The "TYPE" or "ora_type" attributes to "bind_param" in DBI and "bind_param_inout" in DBI override the datatype for individual placeholders. The most frequent reason for using this attribute is to permit trailing spaces in values passed by placeholders.
Constants for the values allowed for this attribute can be imported using:
use DBD::Oracle qw(:ora_types);Only the following values are permitted for this attribute. 👇
💖 ORA_VARCHAR2 💖
Oracle clients using OCI 8 will strip trailing spaces and allow embedded \0 bytes. Oracle clients using OCI 9.2 do not strip trailing spaces and allow embedded \0 bytes. This is the normal default placeholder type. ⭐
💖 ORA_STRING 💖
Do not strip trailing spaces and end the string at the first \0.
💖 ORA_CHAR 💖
Do not strip trailing spaces and allow embedded \0. Force 'blank-padded comparison semantics'.
For example:
use DBD::Oracle qw(:ora_types);
$SQL="select username from all_users where username = ?";
#username is a char(8)
$sth=$dbh->prepare($SQL)";
$sth->bind_param(1,'bloggs',{ ora_type => ORA_CHAR});Will pad bloggs out to 8 characters and return the username. slay. 💅
🌸 ora_parse_error_offset 🌸
If the previous error was from a failed "prepare" due to a syntax error, this attribute gives the offset into the "Statement" attribute where the error was found. 📍
🌸 ora_array_chunk_size 🌸
Due to OCI limitations, DBD::Oracle needs to buffer up rows of bind values in its "execute_for_fetch" implementation. This attribute sets the number of rows to buffer at a time (default value is 1000).
The "execute_for_fetch" function will collect (at most) this many rows in an array, send them off to the DB for execution, then go back to collect the next chunk of rows and so on. This attribute can be used to limit or extend the number of rows processed at a time. 🍓
Note that this attribute also applies to"execute_array", since that method is implemented using"execute_for_fetch".
🌸 ora_connect_with_default_signals 🌸
Sometimes the Oracle client seems to change some of the signal handlers of the process during the connect phase. For instance, some users have observed Perl's default $SIG{INT} handler being ignored after connecting to an Oracle database. 😭 If this causes problems in your application, set this attribute to an array reference of signals you would like to be localized during the connect process. Once the connect is complete, the signal handlers should be returned to their previous state.
For example:
$dbh = DBI->connect ($dsn, $user, $passwd,
{ora_connect_with_default_signals => [ 'INT' ] });NOTE disabling the signal handlers the OCI library sets up may affect functionality in the OCI library.
NOTE If you are usingconnect_cachedthen the above example will lead to DBI thinking each connection is different as an anonymous array reference is being used. To avoid this when usingconnect_cachedyou are advised to use:
my @ora_default_signals = (...);
$dbh = DBI->connect($dsn, $user, $passwd,
{ora_connect_with_default_signals => \@ora_default_signals});In more recent Perl versions you could possibly make use of new state variables. ✨
🦋 connect_cached 🦋
Implemented by DBI, no driver-specific impact. Please note that connect_cached has not been tested with DRCP. 🙈
🦋 data_sources 🦋
@data_sources = DBI->data_sources('Oracle');
@data_sources = $dbh->data_sources();Returns a list of available databases. You will have to set either the 'ORACLE_HOME' or 'TNS_ADMIN' environment value to retrieve this list. It will read these values from TNSNAMES.ORA file entries. 👑
✨ METHODS COMMON TO ALL HANDLES ✨
Okay bestie, so for every single method in this whole section, $h can be a database handle ($dbh) or a statement handle ($sth) — whichever one you're vibing with. And like, $dbh and $sth are just the names people usually use, but you can literally name your variables whatever you want, no cap. The other one you'll see floating around everywhere is $rv, which stands for "return value" 🫶
🐛 err
$rv = $h->err;Returns the error code from the last method called. That's it, that's the whole vibe.
😭 errstr
$str = $h->errstr;Returns the last error that Oracle reported to us. It starts with the "ORA-00000" code and then the error message follows right after. So like, the code first, the tea second 💅
🧚 state
$str = $h->state;So here's the thing — Oracle has not supported SQLSTATE since the early versions of OCI. So this is giving... empty string when the command succeeds, and 'S1000' (that's General Error) for literally every other error. Not super informative, but it's there ✨
And while you can call this as either $sth->state or $dbh->state, it's usually clearer to just always use $dbh->state. Just saying 👑
👑 trace
Implemented by DBI, no driver-specific impact.
👑 trace_msg
Implemented by DBI, no driver-specific impact.
👑 parse_trace_flag and parse_trace_flags
Implemented by DBI, no driver-specific impact.
🎀 func
DBD::Oracle uses the "func" method to support a whole bunch of functions. It's the ✨ versatile ✨ one.
🔒 Private database handle functions
Some of these functions are called through the method func() — which is described over in the DBI documentation, go check it out. And here's a lil tip: any function that begins with ora_ can be called directly, no func() wrapper needed 💖
📜 plsql_errstr
This one returns a string describing the errors from the most recent PL/SQL function, procedure, package, or package body compile. The format is similar to what you'd get out of the SQL*Plus command 'show errors'. Iconic, honestly.
It returns undef if the error string couldn't be retrieved because of a database error. In that case, look in $dbh->errstr to find out why it failed 👀
And if there are, like, no compile errors? You get an empty string back. Slay.
Example:
# Show the errors if CREATE PROCEDURE fails
$dbh->{RaiseError} = 0;
if ( $dbh->do( q{
CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh->err ) { die $dbh->errstr; } # Utter failure
else {
my $msg = $dbh->func( 'plsql_errstr' );
die $dbh->errstr if ! defined $msg;
die $msg if $msg;
}📤 dbms_output_enable / dbms_output_put / dbms_output_get
These three besties use the PL/SQL DBMS_OUTPUT package to store and retrieve text using the DBMS_OUTPUT buffer. Text that gets stored in this buffer by dbms_output_put — or by any PL/SQL block, honestly — can be retrieved by dbms_output_get or by any PL/SQL block connected to the same database session. Teamwork 💜
Stored text is not available until after dbms_output_put or the PL/SQL block that saved it finishes executing. Which means you CAN NOT use these functions to monitor long-running PL/SQL procedures. Sorry bestie, that's just the rules 😭
Example 1:
# Enable DBMS_OUTPUT and set the buffer size
$dbh->{RaiseError} = 1;
$dbh->func( 1000000, 'dbms_output_enable' );
# Put text in the buffer . . .
$dbh->func( @text, 'dbms_output_put' );
# . . . and retrieve it later
@text = $dbh->func( 'dbms_output_get' );Example 2:
$dbh->{RaiseError} = 1;
$sth = $dbh->prepare(q{
DECLARE tmp VARCHAR2(50);
BEGIN
SELECT SYSDATE INTO tmp FROM DUAL;
dbms_output.put_line('The date is '||tmp);
END;
});
$sth->execute;
# retrieve the string
$date_string = $dbh->func( 'dbms_output_get' );🌸 dbms_output_enable ( [ buffer_size ] )
This function calls DBMS_OUTPUT.ENABLE to enable calls to the package DBMS_OUTPUT procedures GET, GET_LINE, PUT, and PUT_LINE. And here's the catch — calls to these procedures are ignored unless DBMS_OUTPUT.ENABLE has been called first. So like, turn it on before you use it, obviously 💅
The buffer_size is the maximum amount of text that can be saved in the buffer, and it must be between 2000 and 1,000,000. If you don't pass a buffer_size, the default is 20,000 bytes. Nice and roomy ✨
🌈 dbms_output_put ( [ @lines ] )
This one calls DBMS_OUTPUT.PUT_LINE to add lines to the buffer.
If every single line got saved successfully, the function returns 1. Depending on the context, an empty list or undef is returned when it fails 😔
And if any line pushes things past buffer_size, a buffer overflow error gets raised and the whole function call fails. Fair warning: some of the text might already be sitting in the buffer. Messy 💀
🦋 dbms_output_get
This one calls DBMS_OUTPUT.GET_LINE to retrieve lines of text from the buffer.
In an array context, all complete lines are removed from the buffer and returned as a list. And if there are no complete lines? Empty list, babes.
In a scalar context, the first complete line gets removed from the buffer and returned. If there are no complete lines, undef is what you get.
Heads up: any text still in the buffer after a call toDBMS_OUTPUT.GET_LINEorDBMS_OUTPUT.GETgets discarded by the next call toDBMS_OUTPUT.PUT_LINE,DBMS_OUTPUT.PUT, orDBMS_OUTPUT.NEW_LINE. So get it while it's there, bestie 🙈
🔑 reauthenticate ( $username, $password )
Starts a new session against the current database using the credentials you supply. Note that this does not work with DRCP. That's the whole tea ☕
🎀 private_attribute_info
$hashref = $dbh->private_attribute_info();
$hashref = $sth->private_attribute_info();Returns a hash of all private attributes used by DBD::Oracle — for either a database or a statement handle. Currently, all the hash values are undef. That's it, that's the function 💖
💜 ATTRIBUTES COMMON TO ALL HANDLES 💜
👑 InactiveDestroy (boolean)
Implemented by DBI, no driver-specific impact.
🔥 RaiseError (boolean, inherited)
Forces errors to always raise an exception. It defaults to off, but honestly? It's recommended that you turn this on. Because the alternative is manually checking the return value of every method (prepare, execute, fetch, etc.) — and bestie, that is so easy to forget to do. Turn it on. Do it for me 🫶
⚠️ PrintError (boolean, inherited)
Forces database errors to also generate warnings, which you can then filter with methods like locally redefining $SIG{__WARN__} or by using modules such as "CGI::Carp". This attribute is on by default.
📌 ShowErrorStatement (boolean, inherited)
Appends info about the current statement to error messages. And if placeholder information is available, it adds that too. Defaults to true.
🚨 Warn (boolean, inherited)
Enables warnings. This is on by default, and should only be turned off in a local block, for a short amount of time, and only when absolutely needed. Like, emergencies only 😤
⚡ Executed (boolean, read-only)
Indicates whether a handle has been executed. For database handles, this value is true after the "do" method has been called, or when one of the child statement handles has issued an "execute". And issuing a "commit" or "rollback" always resets the attribute to false for database handles. For statement handles, any call to "execute" or its variants flips the value to true for the entire lifetime of the statement handle. Periodt.
🔍 TraceLevel (integer, inherited)
Sets the trace level, similar to the "trace" method. Peep the sections on "trace" and "parse_trace_flag" for more details ✨
🌟 Active (boolean, read-only)
Indicates if a handle is active or not. For database handles, this tells you if the database has been disconnected or not. For statement handles, it tells you if all the data has been fetched yet or not. Use of this attribute is not encouraged. Just saying 👀
👶 Kids (integer, read-only)
Returns the number of child processes created for each handle type. For a driver handle, it indicates the number of database handles created. For a database handle, it indicates the number of statement handles created. For statement handles, it always returns zero — because statement handles don't create kids. They're the babies of the family 🍼
👶 ActiveKids (integer, read-only)
Same as "Kids", but only returns the ones that are active. Only the ones out here living their best life 💅
💾 CachedKids (hash ref)
Returns a hashref of handles. If called on a database handle, it returns all statement handles created by use of the "prepare_cached" method. If called on a driver handle, it returns all database handles created by the "connect_cached" method.
🧺 ChildHandles (array ref)
Implemented by DBI, no driver-specific impact.
🔔 PrintWarn (boolean, inherited)
Implemented by DBI, no driver-specific impact.
🛠️ HandleError (boolean, inherited)
Implemented by DBI, no driver-specific impact.
🛠️ HandleSetErr (code ref, inherited)
Implemented by DBI, no driver-specific impact.
🔢 ErrCount (unsigned integer)
Implemented by DBI, no driver-specific impact.
🔤 FetchHashKeyName (string, inherited)
Implemented by DBI, no driver-specific impact.
✂️ ChopBlanks (boolean, inherited)
Implemented by DBI, no driver-specific impact.
🧼 Taint (boolean, inherited)
Implemented by DBI, no driver-specific impact.
🧼 TaintIn (boolean, inherited)
Implemented by DBI, no driver-specific impact.
🧼 TaintOut (boolean, inherited)
Implemented by DBI, no driver-specific impact.
📊 Profile (inherited)
Implemented by DBI, no driver-specific impact.
🏷️ Type (scalar)
Returns "dr" for a driver handle, "db" for a database handle, and "st" for a statement handle. Should be rarely needed. Like, rarely rarely.
📏 LongReadLen
The maximum size of long or longraw columns to retrieve. If one of these columns is longer than LongReadLen, then either a data truncation error will be raised (when LongTrunkOk is false) or the column will be silently truncated (when LongTruncOk is true). Choose your fighter 😬
DBI currently defaults this to 80.
✂️ LongTruncOk
Implemented by DBI, no driver-specific impact.
🔀 CompatMode
Type: boolean, inherited
The CompatMode attribute is used by emulation layers (such as Oraperl) to enable compatible behaviour in the underlying driver (e.g., DBD::Oracle) for this handle. Not normally set by application code, so like, don't touch it unless you know what you're doing 💅
It also has the effect of disabling the 'quick FETCH' of attribute values from the handle's attribute cache. So all attribute values get handled by the driver's own FETCH method. This makes them slightly slower, but it's useful for special-purpose drivers like DBD::Multiplex. Trade-offs, bestie ✨
🔮 ORACLE-SPECIFIC DATABASE HANDLE METHODS 🔮
🌐 ora_can_unicode ( [ $refresh ] )
Returns a number indicating whether either of the database character sets is a Unicode encoding. It calls ora_nls_parameters() and passes the optional $refresh parameter to it.
0= Neither character set is a Unicode encoding.1= National character set is a Unicode encoding.2= Database character set is a Unicode encoding.3= Both character sets are Unicode encodings.
Pick your number, bestie, and slay ✨
📡 ora_can_taf
Returns true if the current connection supports TAF events. False otherwise. That's the whole thing 💖
🌍 ora_nls_parameters ( [ $refresh ] )
Returns a hash reference containing the current NLS parameters, as given by the v$nls_parameters view. The values fetched are cached between calls. To cause the latest values to be fetched, pass a true value to the function. Fresh data, just for you 🌸
🧙 ORACLE-SPECIFIC DATABASE FUNCTIONS 🧙
🏷️ ora_server_version
$versions = $dbh->func('ora_server_version');Returns an array reference of server version strings, e.g.
[11,2,0,2,0]That's your version info, served up as an array ref. Iconic 👑
💖 DATABASE HANDLE METHODS 💖
✨ selectall_arrayref ✨
$ary_ref = $dbh->selectall_arrayref($sql);
$ary_ref = $dbh->selectall_arrayref($sql, \%attr);
$ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);okay so this one hands you back a ref to an array stuffed with all the rows that came out of preparing and executing that SQL string. bestie, it's literally doing the prepare + execute + fetch-all combo for you. full details? the DBI documentation has you, go look 💅
🌸 selectall_hashref 🌸
$hash_ref = $dbh->selectall_hashref($sql, $key_field);same energy, different shape: gives you a ref to a hash holding the rows from preparing and executing the SQL string. again, DBI documentation for the full tea 💖
🦋 selectcol_arrayref 🦋
$ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);this cutie returns a ref to an array with just the first column from each of the rows that preparing + executing the SQL string gave back. and yes you can specify exactly which columns you want back. DBI documentation, full details, go ✨
💅 prepare 💅
$sth = $dbh->prepare($statement, \%attr);gets a statement all prepped and ready for the database engine to execute later, and hands you back a reference to a statement handle object. main character of the whole workflow ngl.
✨ Prepare Attributes ✨
these attrs can go in the \%attr parameter of the prepare in DBI database handle method, iykyk:
ora_placeholders— set this to false and placeholder processing is off. used mainly for loading a PL/SQL package that's been wrapped with Oracle'swraputility.ora_auto_lob— if true (and true is the default), fetching grabs the actual contents of the CLOB or BLOB column in most circumstances. if false, fetching instead grabs the Oracle "LOB Locator" of the CLOB or BLOB value. → more in "LOBS AND LONGS". also check the LOB tests in05dbi.tofOracle::OCIfor examples of how to use LOB Locators.ora_pers_lob— if true, the "Simple Fetch for CLOBs and BLOBs" method for the "Data Interface for Persistent LOBs" gets used for LOBs instead of the default "Data Interface for LOB Locators" method.ora_clbk_lob— if true, the "Piecewise Fetch with Callback" method for the "Data Interface for Persistent LOBs" gets used for LOBs.ora_piece_lob— if true, the "Piecewise Fetch with Polling" method for the "Data Interface for Persistent LOBs" gets used for LOBs.ora_piece_size— this is the max piece size for the "Piecewise Fetch with Callback" and "Piecewise Fetch with Polling" methods, in chars for CLOBS, and bytes for BLOBS.ora_check_sql— if 1 (the default), it forces SELECT statements to be described inprepare(). if 0, it lets SELECT statements defer that describe untilexecute(). → see "Prepare Postponed Till Execute" for more.ora_exe_mode— sets the execute mode of the current statement. presently only one mode is supported:
OCI_STMT_SCROLLABLE_READONLY - make result set scrollable→ see "SCROLLABLE CURSORS" for more details.
ora_prefetch_rows— sets the number of rows to be prefetched. if you don't set it, the default value is 1. → see "Row Prefetching" for more.ora_prefetch_memory— sets the memory level for rows to be prefetched. the application then fetches as many rows as will fit into that much memory. → see "Row Prefetching" for more.ora_row_cache_off— by default DBD::Oracle uses a row cache when fetching to cut down the number of round trips to the server. if you do not want an array fetch, set this to any value other than 0. → see "Row Prefetching" for more.
🌈 Placeholders 🌈
there are three types of placeholders you can use in DBD::Oracle, bestie, buckle up:
- the "question mark" type — each placeholder is one single question mark character. this is the one DBI recommends and it's the most portable. internally each question mark gets replaced by a "dollar sign number" in the order they show up in the query (and that matters when you're using
bind_param). - the "named parameters" type, written like
:foo— this is the one Oracle prefers, obviously 💅
$dbh->{RaiseError} = 1; # save having to check each method call
$sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE :name");
$sth->bind_param(':name', "John%");
$sth->execute;
DBI::dump_results($sth);heads up: when you call bind_param with named parameters you must include that leading colon. the glow-up here is that you can use the same placeholder more than once in the same SQL statement and you only have to bind it once. obsessed.
- a variation of the two above where you name each placeholder
:N(where N is a number). just like the named ones, you can reuse the same placeholder multiple times in the SQL — but when you callbind_paramyou only pass the N (so for:1you usebind_param(1,...)and notbind_param(':1',...)).
⚠️ you cannot mix the different types of placeholders within a statement. you can use different ones for each statement handle you have, but honestly that's confusing at best, so stick to one style within your program, periodt.
🎀 prepare_cached 🎀
$sth = $dbh->prepare_cached($statement, \%attr);implemented by DBI, so no driver-specific impact here. most useful when you're running the same query over and over, because it cuts down round trips to the server. iconic behavior honestly 🔥
⭐ do ⭐
$rv = $dbh->do($statement);
$rv = $dbh->do($statement, \%attr);
$rv = $dbh->do($statement, \%attr, @bind_values);prepare + execute a single statement, one and done. returns the number of rows affected if the query worked, returns undef if an error happened, and returns -1 if the number of rows is unknown or just not available. and listen: it returns 0E0 instead of 0 for "no rows were affected", so it always gives you a true value when there was no error. sneaky but slay 💅
💜 last_insert_id 💜
Oracle does not implement auto_increment of serial type columns. it uses predefined sequences where the id numbers are either selected before insert, at insert time with a trigger, or as part of the query.
below is an example of how you use that last one, with the SQL returning clause, to get the ID number back on insert with the bind_param_inout method. .
$dbh->do('CREATE SEQUENCE lii_seq START 1');
$dbh->do(q{CREATE TABLE lii (
foobar INTEGER NOT NULL UNIQUE,
baz VARCHAR)});
$SQL = "INSERT INTO lii (foobar,baz) VALUES (lii_seq.nextval,'XX') returning foobar into :p_new_id";";
$sth = $dbh->prepare($SQL);
my $p_new_id='-1';
$sth->bind_param_inout(":p_new_id",\$p_new_id,38);
$sth->execute();
$db->commit();🔥 commit 🔥
$rv = $dbh->commit;sends a COMMIT to the server, which means the current transaction is done and every change you made will be visible to other processes. if AutoCommit is enabled, you get a warning and no COMMIT is issued at all. returns true on success, false on error. ✨
🫶 rollback 🫶
$rv = $dbh->rollback;sends a ROLLBACK to the server, which throws away any changes made in the current transaction. if AutoCommit is enabled, you get a warning and no ROLLBACK is issued. returns true on success, and false on error. (the "nvm we don't talk about that" button 🫠)
💀 begin_work 💀
this method turns on transactions until the next call to commit or rollback, but only if AutoCommit is currently enabled. if it's not enabled, calling begin_work issues an error. and note that the transaction doesn't actually begin until the first statement after begin_work is called. so like, it's warming up, not going yet 🙈
🍓 disconnect 🍓
$rv = $dbh->disconnect;disconnects from the Oracle database. any uncommitted changes will be rolled back on disconnection. good policy: always explicitly call commit or rollback at some point before disconnecting instead of just trusting the default rollback behavior.
if the script exits before disconnect is called (or, more precisely, if the database handle is no longer referenced by anything), then the database handle's DESTROY method calls the rollback() and disconnect() methods automatically. still best to explicitly disconnect rather than rely on this behavior 💅
🧚 ping 🧚
$rv = $dbh->ping;this ping method checks whether a database handle is still valid. the value it returns is either 0 — connection is no longer valid — or 1 — connection is valid. this function does 1 round trip to the Oracle Server. cute and quick.
🌸 get_info() 🌸
$value = $dbh->get_info($info_type);DBD::Oracle supports get_info(), but (currently) only a few info types. short and sweet, no cap.
👑 table_info() 👑
DBD::Oracle supports attributes for table_info().
in Oracle, the concept of user and schema is (currently) the same. because database objects are owned by a user, the owner names in the data dictionary views correspond to schema names. Oracle does not support catalogues, so TABLE_CAT is ignored as a selection criterion.
search patterns are supported for TABLE_SCHEM and TABLE_NAME.
TABLE_TYPE may contain a comma-separated list of table types. the following table types are supported:
TABLE
VIEW
SYNONYM
SEQUENCEthe result set is ordered by TABLE_TYPE, TABLE_SCHEM, TABLE_NAME.
the special enumerations of catalogues, schemas and table types are supported. however, TABLE_CAT is always NULL.
an identifier is passed as is, i.e. as the user provides it or Oracle returns it. table_info() performs a case-sensitive search. so a selection criterion should respect upper and lower case. normally an identifier is case-insensitive; Oracle stores and returns it in upper case. sometimes database objects get created with quoted identifiers (for reserved words, mixed case, special characters, ...). such an identifier is case-sensitive (unless it's all upper case). Oracle stores and returns it as given. table_info() has no special quote handling — it neither adds nor removes quotes. so mind your vibes, bestie 💅
🦋 primary_key_info() 🦋
Oracle doesn't support catalogues, so TABLE_CAT is ignored as a selection criterion. the TABLE_CAT field of a fetched row is always NULL (undef). see table_info() for more detailed information.
if the primary key constraint was created without an identifier, PK_NAME contains a system generated name with the form SYS_Cn.
the result set is ordered by TABLE_SCHEM, TABLE_NAME, KEY_SEQ.
an identifier is passed as is, i.e. as the user provides it or Oracle returns it. see table_info() for more detailed information.
🌈 foreign_key_info() 🌈
this method (currently) supports the extended behaviour of SQL/CLI, i.e. the result set contains foreign keys that refer to primary and alternate keys. the field UNIQUE_OR_PRIMARY distinguishes these keys. iconic distinction honestly ✨
Oracle does not support catalogues, so $pk_catalog and $fk_catalog are literally just ignored as selection criteria (in the new style interface). The UK_TABLE_CAT and FK_TABLE_CAT fields of a fetched row are always NULL (undef). See "table_info()" for more detailed information. 💅
If the primary or foreign key constraints were created without an identifier, UK_NAME or FK_NAME contains a system generated name with the form SYS_Cn.
The UPDATE_RULE field is always 3 ('NO ACTION'), because Oracle (currently) does not support other actions. 😭
The DELETE_RULE field may contain wrong values. This is a known Bug (#1271663) in Oracle's data dictionary views. Currently (as of 8.1.7), 'RESTRICT' and 'SET DEFAULT' are not supported, 'CASCADE' is mapped correctly and all other actions (incl. 'SET NULL') appear as 'NO ACTION'. Not us, bestie, that's Oracle's dictionary being messy 🫠
The DEFERABILITY field is always NULL, because this columns is not present in the ALL_CONSTRAINTS view of older Oracle releases.
The result set is ordered by UK_TABLE_SCHEM, UK_TABLE_NAME, FK_TABLE_SCHEM, FK_TABLE_NAME, ORDINAL_POSITION.
An identifier is passed as is, i.e. as the user provides or Oracle returns it. See "table_info()" for more detailed information.
column_info()
Oracle does not support catalogues so TABLE_CAT is ignored as selection criterion. The TABLE_CAT field of a fetched row is always NULL (undef). See "table_info()" for more detailed information.
The CHAR_OCTET_LENGTH field is (currently) always NULL (undef). 💀
Don't rely on the values of the BUFFER_LENGTH field! Especially the length of FLOATs may be wrong. 🚩
Datatype codes for non-standard types are subject to change.
Attention! TheDATA_DEFAULT(COLUMN_DEF) column is of typeLONGso you may have to setLongReadLenon the connection handle before callingcolumn_infoif you have a large default column. After DBD::Oracle 1.40LongReadLenis set automatically to1Mbwhen callingcolumn_infoand reset aftwerwards. (yes that typo is in the original, we're keeping it, no cap 🎀)
The result set is ordered by TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION.
An identifier is passed as is, i.e. as the user provides or Oracle returns it. See "table_info()" for more detailed information.
It is possible with Oracle to make the names of the various DB objects (table,column,index etc) case sensitive.
alter table bloggind add ("Bla_BLA" NUMBER)So in the example the exact case "Bla_BLA" must be used to get it info on the column. While this
alter table bloggind add (Bla_BLA NUMBER)any case can be used to get info on the column. The way that quotes change EVERYTHING 😤 iconic honestly
statistics_info()
Oracle does not support catalogues so TABLE_CAT is ignored as selection criterion. The TABLE_CAT field of a fetched row is always NULL (undef). See "table_info()" for more detailed information.
The INDEX_QUALIFIER field of a fetched row is always NULL (undef), for the same reason as for TABLE_CAT.
If an index was created without an identifier (e.g. in the course of a PK creation), INDEX_NAME contains a system generated name with the form SYS_.
COLUMN_NAME may contain a system generated name (e.g. for function-based indexes).
For the TYPE column, a simple mapping is used:
NORMAL btree
CLUSTER clustered
... otherThe $quick parameter is currently ignored. The method uses the dictionary with the gathered statistics, thus cannot ensure that the values for CARDINALITY and PAGES are current.
The result set is ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, ORDINAL_POSITION.
An identifier is passed as is, i.e. as the user provides or Oracle returns it. See "table_info()" for more detailed information.
selectrow_array
@row_ary = $dbh->selectrow_array($sql);
@row_ary = $dbh->selectrow_array($sql, \%attr);
@row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);Returns an array of row information after preparing and executing the provided SQL string. The rows are returned by calling "fetchrow_array". The string can also be a statement handle generated by a previous prepare. Note that only the first row of data is returned. If called in a scalar context, only the first column of the first row is returned. Because this is not portable, it is not recommended that you use this method in that way. 🙈
selectrow_arrayref
$ary_ref = $dbh->selectrow_arrayref($statement);
$ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
$ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);Exactly the same as "selectrow_array", except that it returns a reference to an array, by internal use of the "fetchrow_arrayref" method. ✨
selectrow_hashref
$hash_ref = $dbh->selectrow_hashref($sql);
$hash_ref = $dbh->selectrow_hashref($sql, \%attr);
$hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);Exactly the same as "selectrow_array", except that it returns a reference to an hash, by internal use of the "fetchrow_hashref" method. 🦋
clone
$other_dbh = $dbh->clone();Creates a copy of the database handle by connecting with the same parameters as the original handle, then trying to merge the attributes. See the DBI documentation for complete usage.
DATABASE HANDLE ATTRIBUTES 💖
AutoCommit (boolean)
Supported by DBD::Oracle as proposed by DBI.The default of AutoCommit is on, but this may change in the future, so it is highly recommended that you explicitly set it when calling "connect". lowkey just always set it, bestie 🫶
ReadOnly (boolean)
$dbh->{ReadOnly} = 1;Specifies if the current database connection should be in read-only mode or not.
Please not that this method is not foolproof: there are still ways to update the database. Consider this a safety net to catch applications that should not be issuing commands such as INSERT, UPDATE, or DELETE. 🕸️
This method method requires DBI version 1.55 or better.
Name (string, read-only)
Returns the name of the current database. This is the same as the DSN, without the "dbi:Oracle:" part.
Username (string, read-only)
Returns the name of the user connected to the database.
Driver (handle, read-only)
Holds the handle of the parent driver. The only recommended use for this is to find the name of the driver using:
$dbh->{Driver}->{Name}RowCacheSize
DBD::Oracle supports both Server pre-fetch and Client side row caching. By default both are turned on to give optimum performance. Most of the time one can just let DBD::Oracle figure out the best optimization. it's giving ✨main character optimization✨
Row Caching
Row caching occurs on the client side and the object of it is to cut down the number of round trips made to the server when fetching rows. At each fetch a set number of rows will be retrieved from the server and stored locally. Further calls the server are made only when the end of the local buffer(cache) is reached.
Rows up to the specified top level row count "RowCacheSize" are fetched if it occupies no more than the specified memory usage limit. The default value is 0, which means that memory size is not included in computing the number of rows to prefetch. If the "RowCacheSize" value is set to a negative number then the positive value of RowCacheSize is used to compute the number of rows to prefetch.
By default "RowCacheSize" is automatically set. If you want to totally turn off prefetching set this to 1.
For any SQL statement that contains a LOB, Long or Object Type Row Caching will be turned off. However server side caching still works. If you are only selecting a LOB Locator then Row Caching will still work.
Row Prefetching
Row prefetching occurs on the server side and uses the DBI database handle attribute "RowCacheSize" and or the Prepare Attribute 'ora_prefetch_memory'. Tweaking these values may yield improved performance. 💅
$dbh->{RowCacheSize} = 100;
$sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY,ora_prefetch_memory=>10000});In the above example 10 rows will be prefetched up to a maximum of 10000 bytes of data. The Oracle Call Interface Programmer's Guide, suggests a good row cache value for a scrollable cursor is about 20% of expected size of the record set.
The prefetch settings tell the DBD::Oracle to grab x rows (or x-bytes) when it needs to get new rows. This happens on the first fetch that sets the current position to any value other than 0. In the above example if we do a OCI_FETCH_FIRST the first 10 rows are loaded into the buffer and DBD::Oracle will not have to go back to the server for more rows. When record 11 is fetched DBD::Oracle fetches and returns this row and the next 9 rows are loaded into the buffer. In this case if you fetch backwards from 10 to 1 no server round trips are made. slay, zero round trips 🔥
With large record sets it is best not to attempt to go to the last record as this may take some time, A large buffer size might even slow down the fetch. If you must get the number of rows in a large record set you might try using an few large OCI_FETCH_ABSOLUTEs and then an OCI_FETCH_LAST, this might save some time. So if you had a record set of 10000 rows and you set the buffer to 5000 and did a OCI_FETCH_LAST one would fetch the first 5000 rows into the buffer then the next 5000 rows. If one requires only the first few rows there is no need to set a large prefetch value.
If the ora_prefetch_memory less than 1 or not present then memory size is not included in computing the number of rows to prefetch otherwise the number of rows will be limited to memory size. Likewise if the RowCacheSize is less than 1 it is not included in the computing of the prefetch rows.
ORACLE-SPECIFIC STATEMENT HANDLE METHODS 🎀
ora_stmt_type
Returns the OCI Statement Type number for the SQL of a statement handle.
ora_stmt_type_name
Returns the OCI Statement Type name for the SQL of a statement handle.
DBI STATEMENT HANDLE OBJECT METHODS 🌈
bind_param
$rv = $sth->bind_param($param_num, $bind_value);
$rv = $sth->bind_param($param_num, $bind_value, $bind_type);
$rv = $sth->bind_param($param_num, $bind_value, \%attr);Allows the user to bind a value and/or a data type to a placeholder. 🫶
The value of $param_num is a number if using the '?' or if using ":foo" style placeholders, the complete name (e.g. ":foo") must be given. The $bind_value argument is fairly self-explanatory. A value of "undef" will bind a "NULL" to the placeholder. Using "undef" is useful when you want to change just the type and will be overwriting the value later. (Any value is actually usable, but "undef" is easy and efficient).
The "\%attr" hash is used to indicate the data type of the placeholder. The default value is "varchar". If you need something else, you must use one of the values provided by DBI or by DBD::Oracle. To use a SQL value, modify your "use DBI" statement at the top of your script as follows:
use DBI qw(:sql_types);okay so this is gonna import some constants right into your script, and you can plug those directly into your bind_param call. The ones you'll bump into the most:
SQL_INTEGERWanna use actual Oracle SQL data types instead? Import the whole list of values like this:
use DBD::Oracle qw(:ora_types);and then you set your data types by setting the value of the ora_type key in the hash you hand over to bind_param. The current list of Oracle data types that get exported is:
ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW
ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_XMLTYPE ORA_CLOB ORA_BLOB
ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE SQLT_INT SQLT_FLT ORA_OCI
SQLT_CHR SQLT_BINData types are sticky, bestie 💅 like once a data type is set on a certain placeholder, it just stays on that placeholder unless you explicitly set it to something else afterwards. And if the statement's already been prepared and you switch the data type to something else, DBD::Oracle will re-prepare the statement for you before it does the next execute. It's giving considerate. 🫶
Examples:
use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_types);
$SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
$sth = $dbh->prepare($SQL);
## Both arguments below are bound to placeholders as "varchar"
$sth->execute(123, "Merk");
## Reset the datatype for the first placeholder to an integer
$sth->bind_param(1, undef, SQL_INTEGER);
## The "undef" bound above is not used, since we supply params to execute
$sth->execute(123, "Merk");
## Set the first placeholder's value and data type
$sth->bind_param(1, 234, { pg_type => ORA_NUMBER });
## Set the second placeholder's value and data type.
## We don't send a third argument, so the default "varchar" is used
$sth->bind_param('$2', "Zool");
## We realize that the wrong data type was set above, so we change it:
$sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
## We also got the wrong value, so we change that as well.
## Because the data type is sticky, we don't need to change it
$sth->bind_param(1, 567);
## This executes the statement with 567 (integer) and "Zool" (varchar)
$sth->execute();These attributes may be used in the \%attr parameter of the bind_param in DBI or bind_param_inout in DBI statement handle methods.
ora_type— set the placeholder's datatype using an Oracle datatype. A fatal error gets raised ifora_typeand the DBITYPEattribute are both used for the same placeholder. Some of these types are not supported by the current version of DBD::Oracle and will cause a fatal error if you use them. Constants for the Oracle datatypes may be imported using
use DBD::Oracle qw(:ora_types);Potentially useful values when DBD::Oracle was built using OCI 7 and later:
ORA_VARCHAR2, ORA_STRING, ORA_LONG, ORA_RAW, ORA_LONGRAW,
ORA_CHAR, ORA_MLSLABEL, ORA_RSETAdditional values when DBD::Oracle was built using OCI 8 and later:
ORA_CLOB, ORA_BLOB, ORA_XMLTYPE, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLEAdditional values when DBD::Oracle was built using OCI 9.2 and later:
SQLT_CHR, SQLT_BINSee "Binding Cursors" for the correct way to use ORA_RSET.
See "LOBS AND LONGS" for how to use ORA_CLOB and ORA_BLOB.
See "SYS.DBMS_SQL datatypes" for ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE.
See "Data Interface for Persistent LOBs" for the correct way to use SQLT_CHR and SQLT_BIN.
See "OTHER DATA TYPES" for more information.
See also "Placeholders and Bind Values" in DBI.
ora_csform— set theOCI_ATTR_CHARSET_FORMfor the bind value. Valid values areSQLCS_IMPLICIT(1) andSQLCS_NCHAR(2). Both of those constants can be imported from the DBD::Oracle module. Rarely needed, honestly. 🙈ora_csid— set the integerOCI_ATTR_CHARSET_IDfor the bind value. Character set names can't be used currently.ora_maxdata_size— set the integerOCI_ATTR_MAXDATA_SIZEfor the bind value. May be needed if a character set conversion from client to server makes the data use more space and therefore fail with a truncation error.ora_maxarray_numentries— set the maximum number of array entries to allocate. Used withORA_VARCHAR2_TABLE,ORA_NUMBER_TABLE. Define the maximum number of array entries Oracle can pass back to you in OUT variable of typeTABLE OF ... .ora_internal_type— set the internal data representation. Currently is supported only forORA_NUMBER_TABLE.
✨ Optimizing Results ✨
Prepare Postponed Till Execute
The DBD::Oracle module can avoid an explicit 'describe' operation prior to the execution of the statement unless the application requests information about the results (like $sth->{NAME}). This cuts down on communication with the server and increases performance (it reduces the number of PARSE_CALLS inside the server). Slay. 🔥
But 💀 it also means SQL errors are not detected until execute() (or $sth->{NAME} etc) is called instead of when prepare() is called. And heads up: if the describe is triggered by the use of $sth->{NAME} or a similar attribute and the describe fails, then an exception is thrown even if RaiseError is false!
Setora_check_sqlto0inprepare()to enable this behaviour. 👑
✨ bind_param_inout ✨
$rv = $sth->bind_param_inout($param_num, \$scalar, 0);DBD::Oracle fully supports bind_param_inout — below are some uses for this method.
Returning A Value from an INSERT
Oracle supports an extended SQL insert syntax which will return one or more of the values inserted. This can be particularly useful for single-pass insertion of values with re-used sequence values (avoiding a separate select seq.nextval from dual step). Chef's kiss. 😌
$sth = $dbh->prepare(qq{
INSERT INTO foo (id, bar)
VALUES (foo_id_seq.nextval, :bar)
RETURNING id INTO :id
});
$sth->bind_param(":bar", 42);
$sth->bind_param_inout(":id", \my $new_id, 99);
$sth->execute;
print "The id of the new record is $new_id\n";Got a ton of columns to bind? You can use code like this:
@params = (... column values for record to be inserted ...);
$sth->bind_param($_, $params[$_-1]) for (1..@params);
$sth->bind_param_inout(@params+1, \my $new_id, 99);
$sth->execute;And if you've got a ton of rows to insert, you can take advantage of Oracle's built in execute array feature with code like this:
my @in_values=('1',2,'3','4',5,'6',7,'8',9,'10');
my @out_values;
my @status;
my $sth = $dbh->prepare(qq{
INSERT INTO foo (id, bar)
VALUES (foo_id_seq.nextval, ?)
RETURNING id INTO ?
});
$sth->bind_param_array(1,\@in_values);
$sth->bind_param_inout_array(2,\@out_values,0,{ora_type => ORA_VARCHAR2});
$sth->execute_array({ArrayTupleStatus=>\@status}) or die "error inserting";
foreach my $id (@out_values){
print 'returned id='.$id.'\n';
}Which will return all the ids into @out_values. 🎀
Note: This will only work for numbered (?) placeholders, The third parameter ofbind_param_inout_array, (0in the example),maxlenis required by DBI but not used by DBD::Oracle Theora_typeattribute is not needed but onlyORA_VARCHAR2will work.
Returning A Recordset
DBD::Oracle does not currently support binding a PL/SQL table (aka array) as an IN OUT parameter to any Perl data structure. So you literally cannot call a PL/SQL function or procedure from DBI that uses a non-atomic datatype as either a parameter, or a return value. BUT 🌈 if you're using Oracle 9.0.1 or later, you can make use of table (or pipelined) functions.
For example, assume you have the existing PL/SQL Package:
CREATE OR REPLACE PACKAGE Array_Example AS
--
TYPE tRec IS RECORD (
Col1 NUMBER,
Col2 VARCHAR2 (10),
Col3 DATE) ;
--
TYPE taRec IS TABLE OF tRec INDEX BY BINARY_INTEGER ;
--
FUNCTION Array_Func RETURN taRec ;
--
END Array_Example ;
CREATE OR REPLACE PACKAGE BODY Array_Example AS
--
FUNCTION Array_Func RETURN taRec AS
--
l_Ret taRec ;
--
BEGIN
FOR i IN 1 .. 5 LOOP
l_Ret (i).Col1 := i ;
l_Ret (i).Col2 := 'Row : ' || i ;
l_Ret (i).Col3 := TRUNC (SYSDATE) + i ;
END LOOP ;
RETURN l_Ret ;
END ;
--
END Array_Example ;
/Currently, there is no way to directly call the function Array_Example.Array_Func from DBI. However, by making the following relatively painless additions, it's not only possible, but extremely efficient. 🔥
First, you need to create database object types that correspond to the record and table types in the package. From the above example, these would be:
CREATE OR REPLACE TYPE tArray_Example__taRec
AS OBJECT (
Col1 NUMBER,
Col2 VARCHAR2 (10),
Col3 DATE
) ;
CREATE OR REPLACE TYPE taArray_Example__taRec
AS TABLE OF tArray_Example__taRec ;Now, assuming the existing function needs to remain unchanged (it's probably being called from other PL/SQL code), we need to add a new function to the package. Here's the new package specification and body:
CREATE OR REPLACE PACKAGE Array_Example AS
--
TYPE tRec IS RECORD (
Col1 NUMBER,
Col2 VARCHAR2 (10),
Col3 DATE) ;
--
TYPE taRec IS TABLE OF tRec INDEX BY BINARY_INTEGER ;
--
FUNCTION Array_Func RETURN taRec ;
FUNCTION Array_Func_DBI RETURN taArray_Example__taRec PIPELINED ;
--
END Array_Example ;
CREATE OR REPLACE PACKAGE BODY Array_Example AS
--
FUNCTION Array_Func RETURN taRec AS
l_Ret taRec ;
BEGIN
FOR i IN 1 .. 5 LOOP
l_Ret (i).Col1 := i ;
l_Ret (i).Col2 := 'Row : ' || i ;
l_Ret (i).Col3 := TRUNC (SYSDATE) + i ;
END LOOP ;
RETURN l_Ret ;
END ;
FUNCTION Array_Func_DBI RETURN taArray_Example__taRec PIPELINED AS
l_Set taRec ;
BEGIN
l_Set := Array_Func ;
FOR i IN l_Set.FIRST .. l_Set.LAST LOOP
PIPE ROW (
tArray_Example__taRec (
l_Set (i).Col1,
l_Set (i).Col2,
l_Set (i).Col3
)
) ;
END LOOP ;
RETURN ;
END ;
--
END Array_Example ;Ok so like, the new function is literally the simplest thing ever 💅 and now all you gotta do is call it as a totally straight-forward SELECT from your DBI code. Based on the example above, your code would look something like this:
my $sth = $dbh->prepare('SELECT * FROM TABLE(Array_Example.Array_Func_DBI)');
$sth->execute;
while ( my ($col1, $col2, $col3) = $sth->fetchrow_array {
...
}✨ SYS.DBMS_SQL datatypes ✨
DBD::Oracle has built-in support for SYS.DBMS_SQL.VARCHAR2_TABLE and SYS.DBMS_SQL.NUMBER_TABLE datatypes, no cap 🫶. Here's the lil baby example:
my $statement='
DECLARE
tbl SYS.DBMS_SQL.VARCHAR2_TABLE;
BEGIN
tbl := :mytable;
:cc := tbl.count();
tbl(1) := 'def';
tbl(2) := 'ijk';
:mytable := tbl;
END;
';
my $sth=$dbh->prepare( $statement );
my @arr=( "abc","efg","hij" );
$sth->bind_param_inout(":mytable", \@arr, 10, {
ora_type => ORA_VARCHAR2_TABLE,
ora_maxarray_numentries => 100
} ) ;
$sth->bind_param_inout(":cc", \$cc, 100 );
$sth->execute();
print "Result: cc=",$cc,"\n",
"\tarr=",Data::Dumper::Dumper(\@arr),"\n";Note: take careful note bestie, we use\@arrhere becausebind_param_inoutwill only take a reference to a scalar. That's just how it is 💀
🎀 ORA_VARCHAR2_TABLE 🎀
SYS.DBMS_SQL.VARCHAR2_TABLE objects are always bound to an array reference (in bind_param() and bind_param_inout()). When you bind an array, you need to specify the full buffer size for OUT data. So there's two parameters doing the work here: max_len (specified as the 3rd argument of bind_param_inout()) and ora_maxarray_numentries. Together they define the maximum array entry length and the maximum number of rows that can be passed to Oracle and back to you 🌸.
In this example we send an array with 1 element of length=3, but we allocate space for 100 Oracle array entries with a maximum length of 10 each. So like, you can get no more than 100 array entries with length <= 10. Periodt.
- If you set
max_lento zero, the maximum array entry length is calculated as the maximum length of an entry of the bound array. - If
0 < max_len < length( $some_element ), truncation occurs. Truncation!! 😭 - If you set
ora_maxarray_numentriesto zero, the current (at bind time) bound array length is used as the maximum. - If
0 < ora_maxarray_numentries < scalar(@array), not all array entries are bound. Rude, but facts.
💜 ORA_NUMBER_TABLE 💜
SYS.DBMS_SQL.NUMBER_TABLE handling is much alike ORA_VARCHAR2_TABLE. The main difference is the internal data representation. Currently 2 types of bind are allowed: as C-integer, or as C-double type. To pick one, you may specify an additional bind parameter ora_internal_type as either SQLT_INT or SQLT_FLT for C-integer and C-double types respectively.
Integer size is architecture-specific and is usually 32 or 64 bit. Double is standard IEEE 754 type.
ora_internal_type defaults to double (SQLT_FLT).
max_len is ignored for OCI_NUMBER_TABLE.
Currently, you cannot bind a full native Oracle NUMBER(38). If you really need it, send a request to the dbi-dev list 🙈.
The usage example is here:
$statement='
DECLARE
tbl SYS.DBMS_SQL.NUMBER_TABLE;
BEGIN
tbl := :mytable;
:cc := tbl(2);
tbl(4) := -1;
tbl(5) := -2;
:mytable := tbl;
END;
';
$sth=$dbh->prepare( $statement );
if( ! defined($sth) ){
die "Prepare error: ",$dbh->errstr,"\n";
}
@arr=( 1,"2E0","3.5" );
# note, that ora_internal_type defaults to SQLT_FLT for ORA_NUMBER_TABLE .
if( not $sth->bind_param_inout(":mytable", \@arr, 10, {
ora_type => ORA_NUMBER_TABLE,
ora_maxarray_numentries => (scalar(@arr)+2),
ora_internal_type => SQLT_FLT
} ) ){
die "bind :mytable error: ",$dbh->errstr,"\n";
}
$cc=undef;
if( not $sth->bind_param_inout(":cc", \$cc, 100 ) ){
die "bind :cc error: ",$dbh->errstr,"\n";
}
if( not $sth->execute() ){
die "Execute failed: ",$dbh->errstr,"\n";
}
print "Result: cc=",$cc,"\n",
"\tarr=",Data::Dumper::Dumper(\@arr),"\n";The result is like:
Result: cc=2
arr=$VAR1 = [
'1',
'2',
'3.5',
'-1',
'-2'
];If you change the bind type to SQLT_INT, like:
ora_internal_type => SQLT_INTyou get:
Result: cc=2
arr=$VAR1 = [
1,
2,
3,
-1,
-2
];🌈 bind_param_inout_array 🌈
DBD::Oracle supports this undocumented feature of DBI. See "Returning A Value from an INSERT" for an example 👀.
🦋 bind_param_array 🦋
$rv = $sth->bind_param_array($param_num, $array_ref_or_value)
$rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
$rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)Binds an array of values to a placeholder, so that each one gets used in turn by a call to the execute_array method. Iconic behaviour honestly.
⭐ execute ⭐
$rv = $sth->execute(@bind_values);Does whatever processing is necessary to actually execute the prepared statement. Slay.
💫 execute_array 💫
$tuples = $sth->execute_array() or die $sth->errstr;
$tuples = $sth->execute_array(\%attr) or die $sth->errstr;
$tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;Executes a prepared statement once for each item in a passed-in hashref, or for items that were previously bound via the bind_param_array method. See the DBI documentation for more deets.
DBD::Oracle takes full advantage of OCI's array interface, so inserts and updates using this interface will run very quickly 🔥. Like, zoom.
🌸 execute_for_fetch 🌸
$tuples = $sth->execute_for_fetch($fetch_tuple_sub);
$tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);Used internally by the execute_array method, and rarely used directly. See the DBI documentation for more details 💅.
🎀 fetchrow_arrayref 🎀
$ary_ref = $sth->fetchrow_arrayref;Fetches the next row of data from the statement handle, and returns a reference to an array holding the column values. Any columns that are NULL come back as undef inside the array.
If there are no more rows, or if an error occurs, this method returns undef. You should check $sth->err afterwards (or use the RaiseError attribute) to figure out if that undef was actually due to an error 👀.
Note: the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch. Also, the elements of the array are reused for each row too, so take care if you want to take a reference to an element. See also bind_columns.
💖 fetchrow_array 💖
@ary = $sth->fetchrow_array;Similar to the fetchrow_arrayref method, but returns a list of column information rather than a reference to a list. Do not use this in a scalar context. Just don't. 💀
🧚 fetchrow_hashref 🧚
$hash_ref = $sth->fetchrow_hashref;
$hash_ref = $sth->fetchrow_hashref($name);Fetches the next row of data and returns a hashref containing the names of the columns as the keys and the data itself as the values. Any NULL value is returned as an undef value.
If there are no more rows, or if an error occurs, this method returns undef. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the undef returned was due to an error.
The optional $name argument should be either "NAME", "NAME_lc" or "NAME_uc", and it indicates what sort of transformation to make to the keys in the hash. By default Oracle uses upper case ✨.
🍓 fetchall_arrayref 🍓
$tbl_ary_ref = $sth->fetchall_arrayref();
$tbl_ary_ref = $sth->fetchall_arrayref( $slice );
$tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );Returns a reference to an array of arrays that contains all the remaining rows to be fetched from the statement handle. If there are no more rows, an empty arrayref will be returned. If an error occurs, the data read in so far will be returned. Because of that, you should always check $sth->err after calling this method, unless RaiseError has been enabled.
If $slice is an array reference, fetchall_arrayref uses the fetchrow_arrayref method to fetch each row as an array ref. If the $slice array is not empty then it's used as a slice to select individual columns by perl array index number (starting at 0, unlike column and parameter numbers which start at 1 👑).
With no parameters, or if $slice is undefined, fetchall_arrayref acts as if passed an empty array ref.
If $slice is a hash reference, fetchall_arrayref uses fetchrow_hashref to fetch each row as a hash reference.
See the DBI documentation for a complete discussion.
🌟 fetchall_hashref 🌟
$hash_ref = $sth->fetchall_hashref( $key_field );Returns a hashref containing all rows to be fetched from the statement handle. See the DBI documentation for a full discussion.
💅 finish 💅
$rv = $sth->finish;Tells DBI that you are finished with the statement handle and are not gonna use it again. Only needed when you haven't fetched all the possible rows.
🔥 rows 🔥
$rv = $sth->rows;Returns the number of rows affected for updates, deletes and inserts, and -1 for selects. Yep, -1. That's just the vibe.
🎀 bind_col 🎀
$rv = $sth->bind_col($column_number, \$var_to_bind);
$rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
$rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );Binds a Perl variable and/or some attributes to an output column of a SELECT statement. Column numbers count up from 1. You do not need to bind output columns in order to fetch data 💖.
NOTE: DBD::Oracle does not use the$bind_typeto determine how to bind the column; it uses what Oracle says the data type is. You can however set theStrictlyTyped/DiscardStringattributes and these will take effect, because these attributes are applied after the column is retrieved.
See the DBI documentation for a discussion of the optional parameters \%attr and $bind_type.
🫶 bind_columns 🫶
$rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);Calls the bind_col method for each column in the SELECT statement, using the supplied list. Chef's kiss, so convenient ✨.
dump_results 💅
$rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);okay so this one is a lil ✨convenience queen✨ — it fetches all the rows from the statement handle, calls DBI::neat_list on each row, and prints the results to $fh (which defaults to STDOUT). Rows get separated by $lsep (defaults to a newline), columns get separated by $fsep (defaults to a comma), and $maxlen controls how wide the output is allowed to be — default 35. 🎀
This method is literally designed as a handy little utility for prototyping and testing queries. But — and hear me on this — because it uses neat_list to format and edit the string for reading by humans, it is not recommended for data transfer applications. it's for vibes and debugging, not for shipping data, bestie. 💀
✨ STATEMENT HANDLE ATTRIBUTES ✨
NUM_OF_FIELDS (integer, read-only) 🔢
Returns the number of columns returned by the current statement. A number will only be returned for SELECT statements, for INSERT, UPDATE, and DELETE statements which contain a RETURNING clause. This method returns undef if called before execute(). no cap.
NUM_OF_PARAMS (integer, read-only) 🎯
Returns the number of placeholders in the current statement. that's it, that's the whole vibe.
NAME (arrayref, read-only) 📛
Returns an arrayref of column names for the current statement. This method will only work for SELECT statements, for SHOW statements, and for INSERT, UPDATE, and DELETE statements which contain a RETURNING clause. This method returns undef if called before execute().
NAME_lc (arrayref, read-only) 🔡
The same as the NAME attribute, except that all column names are forced to lower case.
NAME_uc (arrayref, read-only) 🔠
The same as the NAME attribute, except that all column names are forced to upper case.
NAME_hash (hashref, read-only) 🗂️
Similar to the NAME attribute, but returns a hashref of column names instead of an arrayref. The names of the columns are the keys of the hash, and the values represent the order in which the columns are returned, starting at 0. This method returns undef if called before execute().
NAME_lc_hash (hashref, read-only) 🧸
The same as the NAME_hash attribute, except that all column names are forced to lower case.
NAME_uc_hash (hashref, read-only) 🫠
The same as the NAME_hash attribute, except that all column names are forced to lower case.
not me side-eyeing this one 👀 — yeah, it literally says "lower case" right there in the NAME_uc_hash description. I'm just the messenger, bestie, I don't make the rules. 💀
TYPE (arrayref, read-only) 🏷️
Returns an arrayref indicating the data type for each column in the statement. This method returns undef if called before execute().
PRECISION (arrayref, read-only) 📏
Returns an arrayref of integer values for each column returned by the statement. The number indicates the precision for NUMERIC columns, the size in number of characters for CHAR and VARCHAR columns, and for all other types of columns it returns the number of bytes. This method returns undef if called before execute().
SCALE (arrayref, read-only) ⚖️
Returns an arrayref of integer values for each column returned by the statement. The number indicates the scale of the that column. The only type that will return a value is NUMERIC. This method returns undef if called before execute().
NULLABLE (arrayref, read-only) 🕳️
Returns an arrayref of integer values for each column returned by the statement. The number indicates if the column is nullable or not. 0 = not nullable, 1 = nullable, 2 = unknown. This method returns undef if called before execute().
Database (dbh, read-only) 🗄️
Returns the database handle this statement handle was created from. your origin story, fr fr.
ParamValues (hash ref, read-only) 📦
Returns a reference to a hash containing the values currently bound to placeholders. If the "named parameters" type of placeholders are being used (such as ":foo"), then the keys of the hash will be the names of the placeholders (without the colon). If the "dollar sign numbers" type of placeholders are being used, the keys of the hash will be the numbers, without the dollar signs. If the "question mark" type is used, integer numbers will be returned, starting at one and increasing for every placeholder.
If this method is called before execute, the literal values passed in are returned. If called after execute, then the quoted versions of the values are returned. sneaky lil glow-up there. ✨
ParamTypes (hash ref, read-only) 🧬
Returns a reference to a hash containing the type names currently bound to placeholders. The keys are the same as returned by the ParamValues method. The values are hashrefs containing a single key value pair, in which the key is either 'TYPE' if the type has a generic SQL equivalent, and 'pg_type' if the type can only be expressed by a Postgres type. The value is the internal number corresponding to the type originally passed in. (Placeholders that have not yet been bound will return undef as the value). This allows the output of ParamTypes to be passed back to the "bind_param" method. 🌀
Statement (string, read-only) 📝
Returns the statement string passed to the most recent "prepare" method called in this database handle, even if that method failed. This is especially useful where "RaiseError" is enabled and the exception handler checks $@ and sees that a "prepare" method call failed. genuinely iconic for debugging 🫶
RowsInCache 🗃️
Returns the number of un-fetched rows in the cache for selects.
🔥 SCROLLABLE CURSORS 🔥
Oracle supports the concept of a 'Scrollable Cursor' which is defined as a 'Result Set' where the rows can be fetched either sequentially or non-sequentially. One can fetch rows forward, backwards, from any given position or the n-th row from the current position in the result set. 🦋
Rows are numbered sequentially starting at one and client-side caching of the partial or entire result set can improve performance by limiting round trips to the server.
Oracle does not support DML type operations with scrollable cursors so you are limited to simple 'Select' operations only. As well you can not use this functionality with remote mapped queries or if the LONG datatype is part of the select list. 😭
However, LOBSs, CLOBSs, and BLOBs do work as do all the regular bind, and fetch methods. slay. 💚
Only use scrollable cursors if you really have a good reason to. They do use up considerable more server and client resources and have poorer response times than non-scrolling cursors. like, this is not a "just because it's cute" situation, bestie. it costs you. 💸
🌸 Enabling Scrollable Cursors 🌸
To enable this functionality you must first import the 'Fetch Orientation' and the 'Execution Mode' constants by using;
use DBD::Oracle qw(:ora_fetch_orient :ora_exe_modes);Next you will have to tell DBD::Oracle that you will be using scrolling by setting the ora_exe_mode attribute on the statement handle to 'OCI_STMT_SCROLLABLE_READONLY' with the prepare method;
$sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});When the statement is executed you will then be able to use 'ora_fetch_scroll' method to get a row or you can still use any of the other fetch methods but with a poorer response time than if you used a non-scrolling cursor. As well scrollable cursors are compatible with any applicable bind methods. 🎀
🧚 Scrollable Cursor Methods 🧚
The following driver-specific methods are used with scrollable cursors.
ora_scroll_position 📍
$position = $sth->ora_scroll_position();This method returns the current position (row number) attribute of the result set. Prior to the first fetch this value is 0. This is the only time this value will be 0 after the first fetch the value will be set, so you can use this value to test if any rows have been fetched. The minimum value will always be 1 after the first fetch. The maximum value will always be the total number of rows in the record set. ⭐
ora_fetch_scroll 🎢
$ary_ref = $sth->ora_fetch_scroll($fetch_orient,$fetch_offset);Works the same as "fetchrow_arrayref", excepts one passes in a 'Fetch Orientation' constant and a fetch_offset value which will then determine the row that will be fetched. It returns the row as a list containing the field values. Null fields are returned as undef values in the list. 🌈
The valid orientation constant and fetch offset values combination are detailed below
OCI_FETCH_CURRENT, fetches the current row, the fetch offset value is ignored.
OCI_FETCH_NEXT, fetches the next row from the current position, the fetch offset value
is ignored.
OCI_FETCH_FIRST, fetches the first row, the fetch offset value is ignored.
OCI_FETCH_LAST, fetches the last row, the fetch offset value is ignored.
OCI_FETCH_PRIOR, fetches the previous row from the current position, the fetch offset
value is ignored.
OCI_FETCH_ABSOLUTE, fetches the row that is specified by the fetch offset value.
OCI_FETCH_ABSOLUTE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_FIRST.
OCI_FETCH_ABSOLUTE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
OCI_FETCH_RELATIVE, fetches the row relative from the current position as specified by the
fetch offset value.
OCI_FETCH_RELATIVE, and a fetch offset value of 0 is equivalent to a OCI_FETCH_CURRENT.
OCI_FETCH_RELATIVE, and a fetch offset value of 1 is equivalent to a OCI_FETCH_NEXT.
OCI_FETCH_RELATIVE, and a fetch offset value of -1 is equivalent to a OCI_FETCH_PRIOR.The effect that a ora_fetch_scroll method call has on the current position attribute is detailed below.
OCI_FETCH_CURRENT, has no effect on the current position attribute.
OCI_FETCH_NEXT, increments current position attribute by 1
OCI_FETCH_NEXT, when at the last row in the record set does not change current position
attribute, it is equivalent to a OCI_FETCH_CURRENT
OCI_FETCH_FIRST, sets the current position attribute to 1.
OCI_FETCH_LAST, sets the current position attribute to the total number of rows in the
record set.
OCI_FETCH_PRIOR, decrements current position attribute by 1.
OCI_FETCH_PRIOR, when at the first row in the record set does not change current position
attribute, it is equivalent to a OCI_FETCH_CURRENT.OCI_FETCH_ABSOLUTE— sets the current position attribute to the fetch offset value.OCI_FETCH_ABSOLUTEwith a fetch offset value that's less than 1 → it does not change the current position attribute, it's literally equivalent to aOCI_FETCH_CURRENT.OCI_FETCH_ABSOLUTEwith a fetch offset value that's greater than the number of records in the record set → also does not change the current position attribute, also equivalent to aOCI_FETCH_CURRENT.
OCI_FETCH_RELATIVE— sets the current position attribute to (current position attribute + fetch offset value).OCI_FETCH_RELATIVEwith a fetch offset value that makes the current position less than 1 → does not change the fetch offset value, so it's equivalent to aOCI_FETCH_CURRENT.OCI_FETCH_RELATIVEwith a fetch offset value that makes it greater than the number of records in the record set → does not change the fetch offset value, so it's equivalent to aOCI_FETCH_CURRENT.
So what do all the different orientation constants actually do on that very first fetch, when the current position attribute is chillin' at 0? Obsessed with this list honestly:
OCI_FETCH_CURRENT— does not fetch a row or change the current position attribute.OCI_FETCH_FIRST— fetches row 1 and sets the current position attribute to 1.OCI_FETCH_LAST— fetches the last row in the record set and sets the current position attribute to the total number of rows in the record set.OCI_FETCH_NEXT— equivalent to aOCI_FETCH_FIRST.OCI_FETCH_PRIOR— equivalent to aOCI_FETCH_CURRENT.OCI_FETCH_ABSOLUTE, with a fetch offset value that's less than 1 → equivalent to aOCI_FETCH_CURRENT.OCI_FETCH_ABSOLUTE, with a fetch offset value that's greater than the number of records in the record set → equivalent to aOCI_FETCH_CURRENT.OCI_FETCH_RELATIVE, with a fetch offset value that's less than 1 → equivalent to aOCI_FETCH_CURRENT.OCI_FETCH_RELATIVE, with a fetch offset value that makes it greater than the number of records in the record set → equivalent to aOCI_FETCH_CURRENT.
Scrollable Cursor Usage 🌀
Okay so picture a cute little bit of code like this:
use DBI;
use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
my $dbh = DBI->connect($dsn, $dbuser, '');
my $SQL = "select id,
first_name,
last_name
from employee";
my $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY});
$sth->execute();
my $value;and let's just say the number of rows that come back from the query is 20. The little snippets below show off every use of the ora_fetch_scroll method, and they are so satisfying to read top to bottom, trust.
Fetching the Last Row 💫
$value = $sth->ora_fetch_scroll(OCI_FETCH_LAST,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 20 after this snippet. It's also a way to get the number of rows in the record set, but heads up — if the record set is large, this could take some time.
Fetching the Current Row 💫
$value = $sth->ora_fetch_scroll(OCI_FETCH_CURRENT,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will still be 20 after this snippet.
Fetching the First Row 💫
$value = $sth->ora_fetch_scroll(OCI_FETCH_FIRST,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 1 after this snippet.
Fetching the Next Row 💫
for(my $i=0;$i<=3;$i++){
$value = $sth->ora_fetch_scroll(OCI_FETCH_NEXT,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
}
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 5 after this snippet.
Fetching the Prior Row 💫
for(my $i=0;$i<=3;$i++){
$value = $sth->ora_fetch_scroll(OCI_FETCH_PRIOR,0);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
}
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 1 after this snippet.
Fetching the 10th Row 💫
$value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,10);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 10 after this snippet.
Fetching the 10th to 14th Row 💫
for(my $i=10;$i<15;$i++){
$value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,$i);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
}
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 14 after this snippet.
Fetching the 14th to 10th Row 💫
for(my $i=14;$i>9;$i--){
$value = $sth->ora_fetch_scroll(OCI_FETCH_ABSOLUTE,$i);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
}
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 10 after this snippet.
Fetching the 5th Row From the Present Position. 💫
$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,5);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 15 after this snippet.
Fetching the 9th Row Prior From the Present Position 💫
$value = $sth->ora_fetch_scroll(OCI_FETCH_RELATIVE,-9);
print "id=".$value->[0].", First Name=".$value->[1].", Last Name=".$value->[2]."\n";
print "current scroll position=".$sth->ora_scroll_position()."\n";The current position attribute will be 6 after this snippet.
Use Finish 💫
$sth->finish();🚨 Bestie, listen to me on this one: when you're using scrollable cursors it is required that you use the $sth->finish() method once you're done with the cursor, because this type of cursor has to be explicitly cancelled on the server. If you don't do this you may cause resource problems on your database. Like, actual problems. Not cute. 💀
LOBS AND LONGS 🫶
The key to working with LOBs (CLOB, BLOBs) is to remember the value of an Oracle LOB column is not the content of the LOB. It's a 'LOB Locator' which, after being selected or inserted, needs extra processing to read or write the content of the LOB. There are also legacy LONG types (LONG, LONG RAW) which are presently deprecated by Oracle but are still in use. These LONG types do not utilize a 'LOB Locator' and also are more limited in functionality than CLOB or BLOB fields.
DBD::Oracle now offers three interfaces to LOB and LONG data:
- "Data Interface for Persistent LOBs" — with this interface
DBD::Oraclehandles your data directly utilizing regular OCI calls. Oracle itself takes care of the LOB Locator operations in the case of BLOBs and CLOBs, treating them exactly as if they were the same as the legacy LONG or LONG RAW types. - "Data Interface for LOB Locators" — with this interface
DBD::Oraclehandles your data utilizing LOB Locator OCI calls, so it only works with CLOB and BLOB datatypes. With this interfaceDBD::Oracletakes care of the LOB Locator operations for you. - LOB Locator Method Interface — this allows the user direct access to the LOB Locator methods, so you have to take care of the LOB Locator operations yourself.
Generally speaking the interface that you will choose will be dependent on what end you are trying to achieve. All have their benefits and drawbacks.
One point to remember when working with LOBs (CLOBs, BLOBs) is if your LOB column can be in one of three states:
- NULL — the table cell is created, but the cell holds no locator or value. If your LOB field is in this state then there is no LOB Locator that
DBD::Oraclecan work with, so you'll encounter a
DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtrerror when working with a LOB.
You can correct this by using an SQL UPDATE statement to reset the LOB column to a non-NULL (or empty LOB) value with either EMPTY_BLOB or EMPTY_CLOB as in this example:
UPDATE lob_example
SET bindata=EMPTY_BLOB()
WHERE bindata IS NULL.- Empty — a LOB instance with a locator exists in the cell, but it has no value. The length of the LOB is zero. In this case
DBD::Oraclewill return 'undef' for the field. - Populated — a LOB instance with a locator and a value exists in the cell. You actually get the LOB value.
Data Interface for Persistent LOBs 🦋
This is the original interface for LONG and LONG RAW datatypes, and from Oracle 9iR1 and later the OCI API was extended to work directly with the other LOB datatypes. In other words you can treat all LOB type data (BLOB, CLOB) as if it was a LONG, LONG RAW, or VARCHAR2. So you can perform INSERT, UPDATE, fetch, bind, and define operations on LOBs using the same techniques you would use on other datatypes that store character or binary data. In some cases there are fewer round trips to the server as no 'LOB Locators' are used — normally one can get an entire LOB in a single round trip.
Simple Fetch for LONGs and LONG RAWs 🌸
As the name implies, this is the simplest way to use this interface. DBD::Oracle just attempts to get your LONG datatypes as a single large piece. There are no special settings, simply set the database handle's 'LongReadLen' attribute to a value that will be larger than the expected size of the LONG or LONG RAW. If the size of the LONG or LONG RAW exceeds 'LongReadLen' DBD::Oracle will return an 'ORA-24345: A Truncation' error. To stop this, set the database handle's 'LongTruncOk' attribute to '1'. The maximum value of 'LongReadLen' seems to be dependent on the physical memory limits of the box that Oracle is running on. You have most likely reached this limit if you run into an 'ORA-01062: unable to allocate memory for define buffer' error. One solution is to set the size of 'LongReadLen' to a lower value.
For example give this table;
...and boom, here's the table we're working with:
CREATE TABLE test_long (
id NUMBER,
long1 long)then this code:
$dbh->{LongReadLen} = 2*1024*1024; #2 meg
$SQL='select p_id,long1 from test_long';
$sth=$dbh->prepare($SQL);
$sth->execute();
while (my ( $p_id,$long )=$sth->fetchrow()){
print "p_id=".$p_id."\n";
print "long=".$long."\n";
}This will pull out every single long1 field in the table — as long as they're all under 2MB in length. If a value in long1 is longer than that? It throws an error, periodt. Add this line:
$dbh->{LongTruncOk}=1;before the execute, and you'll get all the long1 fields back, but they'll be truncated at 2MBs. 💅
🦋 Using ora_ncs_buff_mtpl 🦋
okay so when you're getting CLOBs and NCLOBs in or out of Oracle, the Server translates from the Server's NCharSet to the Client's. If those two happen to be the same, or at least compatible, then all of these actions are a 1 char to 1 char bases. So if you set your LongReadLen buffer to 10_000_000 you'll get up to 10_000_000 char. slay.
But bestie, if the Server has to translate from one NCharSet to another, it uses bytes for conversion. The buffer value gets set to 4 * LONG_READ_LEN, which was super wasteful — like, you might only be asking for 10_000_000 bytes but you were actually using 40_000_000 bytes of buffer under the hood. You'd still get 10_000_000 bytes (maybe fewer characters though, lowkey) but you're burning way more memory than you need. 😭
So now you can customize the size of that buffer by setting 'ora_ncs_buff_mtpl' either on the connection or the statement handle. You can also set it as the 'ORA_DBD_NCS_BUFFER' OS environment variable — so you will have to go back and change all your code if you're getting into trouble.
The default value is still set to 4 for backward compatibility. You can lower this value and thus increase the amount of data you can retrieve. And if ora_ncs_buff_mtpl is too small, DBD::Oracle will throw an error telling you to increase this buffer by one.
If that error is not captured, then you may get — at some random point later on, usually at a finish() or disconnect() or even a fetch() — this error:
ORA-03127: no new operations allowed until the active operation endsThis is one of the more obscure ORA errors (have some fun and report it to Meta-Link, they will scratch their heads for hours 🫠)
If you get this, simply increment the ora_ncs_buff_mtpl by one until it goes away. iconic.
This should greatly increase your ability to select very large CLOBs or NCLOBs, by freeing up a large block of memory. 💖
You can tune this value by setting ora_oci_success_warn, which will display the following:
OCILobRead field 2 of 3 SUCCESS: csform 1 (SQLCS_IMPLICIT), LOBlen 10240(characters), LongReadLen
20(characters), BufLen 80(characters), Got 28(characters)In the case above the query Got 28 characters (well, really only 20 characters of 28 bytes) so we could use ora_ncs_buff_mtpl=>2 (20*2=40) thus saving 40bytes of memory. chef's kiss. 👑
🌸 Simple Fetch for CLOBs and BLOBs 🌸
To use this interface for the CLOB and BLOB datatypes, set the 'ora_pers_lob' attribute of the statement handle to '1' with the prepare method, and set the database handle's 'LongReadLen' attribute to a value that will be larger than the expected size of the LOB. If the size of the LOB exceeds 'LongReadLen', DBD::Oracle will return an 'ORA-24345: A Truncation' error. To stop this, set the database handle's 'LongTruncOk' attribute to '1'. The maximum value of 'LongReadLen' seems to be dependent on the physical memory limits of the box that Oracle is running on, in the same way that LONGs and LONG RAWs are.
For CLOBs and NCLOBs the limit is 64k chars if there is no truncation. This is an internal OCI limit — complain to them if you want it changed. 😤 However, if your CLOB is longer than this and also larger than 'LongReadLen', then 'LongReadLen' chars are returned.
It seems with BLOBs you are not limited by the 64k.
For example, give this table:
CREATE TABLE test_lob (id NUMBER,
clob1 CLOB,
clob2 CLOB,
blob1 BLOB,
blob2 BLOB)this code:
$dbh->{LongReadLen} = 2*1024*1024; #2 meg
$SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
$sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
$sth->execute();
while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
print "p_id=".$p_id."\n";
print "clob1=".$clob1."\n";
print "clob2=".$clob2."\n";
print "blob1=".$blob2."\n";
print "blob2=".$blob2."\n";
}Will select out all of the LOBs in the table as long as they are all under 2MB in length. Longer lobs will throw an error. Adding this line:
$dbh->{LongTruncOk}=1;before the execute will return all the lobs but they will be truncated at 2MBs.
🧚 Piecewise Fetch with Callback 🧚
With a piecewise callback fetch, DBD::Oracle sets up a function that will 'callback' to the DB during the fetch and gets your LOB (LONG, LONG RAW, CLOB, BLOB) piece by piece. To use this interface, set the 'ora_clbk_lob' attribute of the statement handle to '1' with the prepare method. Next, set the 'ora_piece_size' to the size of the piece that you want to return on the callback. Finally, set the database handle's 'LongReadLen' attribute to a value that will be larger than the expected size of the LOB. Like the "Simple Fetch for LONGs and LONG RAWs" and "Simple Fetch for CLOBs and BLOBs", if the size of the LOB exceeds 'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB or set 'LongReadLen' to a higher value. With this interface the value of 'ora_piece_size' seems to be constrained by the same memory limit as found on the Simple Fetch interface. If you encounter an 'ORA-01062' error, try setting the value of 'ora_piece_size' to a smaller value. The value for 'LongReadLen' is dependent on the version and settings of the Oracle DB you are using. In theory it ranges from 8GBs in 9iR1 up to 128 terabytes with 11g but you will also be limited by the physical memory of your PERL instance. 💀
Using the table from the last example, this code:
$dbh->{LongReadLen} = 20*1024*1024; #20 meg
$SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
$sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
$sth->execute();
while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
print "p_id=".$p_id."\n";
print "clob1=".$clob1."\n";
print "clob2=".$clob2."\n";
print "blob1=".$blob2."\n";
print "blob2=".$blob2."\n";
}Will select out all of the LOBs in the table as long as they are all under 20MB in length. If the LOB is longer than 5MB (ora_piece_size), DBD::Oracle will fetch it in at least 2 pieces to a maximum of 4 pieces (4*5MB=20MB). Like the Simple Fetch examples, Lobs longer than 20MB will throw an error.
Using the table from the first example (LONG), this code:
$dbh->{LongReadLen} = 20*1024*1024; #2 meg
$SQL='select p_id,long1 from test_long';
$sth=$dbh->prepare($SQL,{ora_clbk_lob=>1,ora_piece_size=>5*1024*1024});
$sth->execute();
while (my ( $p_id,$long )=$sth->fetchrow()){
print "p_id=".$p_id."\n";
print "long=".$long."\n";
}Will select all of the long1 fields from table as long as they are is under 20MB in length. If the long1 filed is longer than 5MB (ora_piece_size), DBD::Oracle will fetch it in at least 2 pieces to a maximum of 4 pieces (4*5MB=20MB). Like the other examples, long1 fields longer than 20MB will throw an error.
🌈 Piecewise Fetch with Polling 🌈
With a polling piecewise fetch, DBD::Oracle iterates (Polls) over the LOB during the fetch, getting your LOB (LONG, LONG RAW, CLOB, BLOB) piece by piece. To use this interface, set the 'ora_piece_lob' attribute of the statement handle to '1' with the prepare method. Next, set the 'ora_piece_size' to the size of the piece that you want to return on the callback. Finally, set the database handle's 'LongReadLen' attribute to a value that will be larger than the expected size of the LOB. Like the "Piecewise Fetch with Callback" and Simple Fetches, if the size of the LOB exceeds 'LongReadLen' you can use the 'LongTruncOk' attribute to truncate the LOB or set 'LongReadLen' to a higher value. With this interface the value of 'ora_piece_size' seems to be constrained by the same memory limit as found on the "Piecewise Fetch with Callback".
Using the table from the example above, this code:
$dbh->{LongReadLen} = 20*1024*1024; #20 meg
$SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs';
$sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
$sth->execute();
while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
print "p_id=".$p_id."\n";
print "clob1=".$clob1."\n";
print "clob2=".$clob2."\n";
print "blob1=".$blob2."\n";
print "blob2=".$blob2."\n";
}Will select out all of the LOBs in the table as long as they are all under 20MB in length. If the LOB is longer than 5MB (ora_piece_size), DBD::Oracle will fetch it in at least 2 pieces to a maximum of 4 pieces (4*5MB=20MB). Like the other fetch methods, LOBs longer than 20MB will throw an error.
Finally, with this code:
$dbh->{LongReadLen} = 20*1024*1024; #2 meg
$SQL='select p_id,long1 from test_long';
$sth=$dbh->prepare($SQL,{ora_piece_lob=>1,ora_piece_size=>5*1024*1024});
$sth->execute();
while (my ( $p_id,$long )=$sth->fetchrow()){
print "p_id=".$p_id."\n";
print "long=".$long."\n";
}Will select all of the long1 fields from table as long as they are is under 20MB in length. If the long1 field is longer than 5MB (ora_piece_size), DBD::Oracle will fetch it in at least 2 pieces to a maximum of 4 pieces (4*5MB=20MB). Like the other examples, long1 fields longer than 20MB will throw an error.
💜 Binding for Updates and Inserts for CLOBs and BLOBs 💜
To bind for updates and inserts, all that is required to use this interface is to set the statement handle's prepare method 'ora_type' attribute to 'SQLT_CHR' in the case of CLOBs and NCLOBs, or 'SQLT_BIN' in the case of BLOBs — as in this example for an insert:
my $in_clob = "<document>\n";
$in_clob .= " <value>$_</value>\n" for 1 .. 10_000;
$in_clob .= "</document>\n";
my $in_blob ="0101" for 1 .. 10_000;
$SQL='insert into test_lob3@tpgtest (id,clob1,clob2, blob1,blob2) values(?,?,?,?,?)';
$sth=$dbh->prepare($SQL );
$sth->bind_param(1,3);
$sth->bind_param(2,$in_clob,{ora_type=>SQLT_CHR});
$sth->bind_param(3,$in_clob,{ora_type=>SQLT_CHR});
$sth->bind_param(4,$in_blob,{ora_type=>SQLT_BIN});
$sth->bind_param(5,$in_blob,{ora_type=>SQLT_BIN});
$sth->execute();and that's the whole vibe for LOBs and LONGs, bestie — go forth and bind responsibly 🫶
...so like, the only ceiling we've hit with this style of insert is that your LOBs have to stay under 2GB in size. That's the whole vibe check. 💀
🦋 Support for Remote LOBs 🦋
Starting with Oracle 10gR2, the interface for Persistent LOBs got expanded to support remote LOBs (i.e. access over a dblink). So say you've got a database called 'lob_test' that has a 'LINK' defined like this:
CREATE DATABASE LINK link_test CONNECT TO test_lobs IDENTIFIED BY tester USING 'lob_test';pointing at a remote database called 'test_lobs' — then bestie, this code right here just works:
$dbh = DBI->connect('dbi:Oracle:','test@lob_test','test');
$dbh->{LongReadLen} = 2*1024*1024; #2 meg
$SQL='select p_id,lob_1,lob_2,blob_2 from test_lobs@link_test';
$sth=$dbh->prepare($SQL,{ora_pers_lob=>1});
$sth->execute();
while (my ( $p_id,$log,$log2,$log3,$log4 )=$sth->fetchrow()){
print "p_id=".$p_id."\n";
print "clob1=".$clob1."\n";
print "clob2=".$clob2."\n";
print "blob1=".$blob2."\n";
print "blob2=".$blob2."\n";
}But now the limitations of Remote LOBs, because there are always limitations, aren't there 😭
- Queries involving more than one database are not supported; so this returns an error:
SELECT t1.lobcol,
a2.lobcol
FROM t1,
t2.lobcol@dbs2 a2 W
WHERE LENGTH(t1.lobcol) = LENGTH(a2.lobcol);and so does this:
SELECT t1.lobcol
FROM t1@dbs1
UNION ALL
SELECT t2.lobcol
FROM t2@dbs2;- DDL commands are not supported; so this returns an error:
CREATE VIEW v AS SELECT lob_col FROM tab@dbs;- Only binds and defines for data going into remote persistent LOBs are supported. So parameter passing in PL/SQL where CHAR data is bound or defined for remote LOBs is not allowed .
These statements all produce errors:
SELECT foo() FROM table1@dbs2;
SELECT foo()@dbs INTO char_val FROM DUAL;
SELECT XMLType().getclobval FROM table1@dbs2;- If the remote object is a view such as
CREATE VIEW v AS SELECT foo() FROM ...then the following would not work:
SELECT * FROM v@dbs2;- Limited PL/SQL parameter passing — PL/SQL parameter passing is not allowed where the actual argument is a LOB type and the remote argument is one of
VARCHAR2,NVARCHAR2,CHAR,NCHAR, orRAW. RETURNING INTOdoes not support implicit conversions between CHAR and CLOB. So this returns an error:
SELECT t1.lobcol as test, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 RETURNING test🌸 Locator Data Interface 🌸
Simple Usage
When you fetch LOBs with this interface, a 'LOB Locator' gets created and then used to grab the lob with the LongReadLen and LongTruncOk attributes. The value for 'LongReadLen' depends on the version and settings of the Oracle DB you're running. In theory it ranges from 8GBs in 9iR1 all the way up to 128 terabytes with 11g — but ngl you'll also be capped by the physical memory of your PERL instance. 🙈
When you're inserting or updating LOBs, some major magic goes down behind the scenes to keep it transparent. Basically the driver has to insert a 'LOB Locator' and then refetch that freshly-inserted LOB Locator before it can write the data into it. That said, it works great most of the time, and it's been made as fast as possible — just one extra server-round-trip per insert or update after the first one. For now though, only single-row LOB updates are supported. ✨
To insert or update a large LOB using a placeholder, DBD::Oracle has to know in advance that it's a LOB type. So you need to say:
$sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });The ORA_CLOB and ORA_BLOB constants can be imported using
use DBD::Oracle qw(:ora_types);or you can use the corresponding integer values (112 and 113).
One further wrinkle: for inserts and updates of LOBs, DBD::Oracle has to be able to tell which parameters relate to which table fields. In all cases where it can possibly work it out for itself, it does — however, if there are multiple LOB fields of the same type in the table, then you need to tell it which field each LOB param relates to:
$sth->bind_param($idx, $value, { ora_type=>ORA_CLOB, ora_field=>'foo' });There are some limitations inherent in the way DBD::Oracle makes typical LOB operations simple by hiding the LOB Locator processing:
- Can't read/write LOBs in chunks (except via
DBMS_LOB.WRITEAPPENDin PL/SQL) - To INSERT a LOB, you need UPDATE privilege.
The alternative is to disable the automatic LOB Locator processing. If "ora_auto_lob" is 0 in prepare(), you can fetch the LOB Locators and do all the work yourself using the ora_lob_*() methods. See the "Data Interface for LOB Locators" section below. 👑
LOB support in PL/SQL
LOB Locators can be passed to PL/SQL calls by binding them to placeholders with the proper "ora_type". If "ora_auto_lob" is true, output LOB parameters will automatically be returned as strings. 🫶
If the Oracle driver has support for temporary LOBs (Oracle 9i and higher), strings can be bound to input LOB placeholders and will be automatically converted to LOBs.
Example:
# Build a large XML document, bind it as a CLOB,
# extract elements through PL/SQL and return as a CLOB
# $dbh is a connected database handle
# output will be large
local $dbh->{LongReadLen} = 1_000_000;
my $in_clob = "<document>\n";
$in_clob .= " <value>$_</value>\n" for 1 .. 10_000;
$in_clob .= "</document>\n";
my $out_clob;
my $sth = $dbh->prepare(<<PLSQL_END);
-- extract 'value' nodes
DECLARE
x XMLTYPE := XMLTYPE(:in);
BEGIN
:out := x.extract('/document/value').getClobVal();
END;
PLSQL_END
# :in param will be converted to a temp lob
# :out parameter will be returned as a string.
$sth->bind_param( ':in', $in_clob, { ora_type => ORA_CLOB } );
$sth->bind_param_inout( ':out', \$out_clob, 0, { ora_type => ORA_CLOB } );
$sth->execute;If you ever get an
ORA-01691 unable to extend lob segment sss.ggg by nnn in tablespace ttterror while attempting to insert a LOB, that means the Oracle user has insufficient space for the LOB you're trying to insert. One solution is to use "alter database datafile 'sss.ggg' resize Mnnn" to increase the available memory for LOBs. 💅
💜 Persistent & Locator Interface Caveats 💜
Now that you've got the option of using the Persistent or the Locator interface for LOBs, the question arises: which one do you use?? For starters, if you want to access LOBs over a dblink you'll have to use the Persistent interface, so that choice is simple. After that it gets a little trickier. It basically boils down to a choice between LOB size and speed. 🦋
The Callback and Polling piecewise fetches are very very slow compared to the Simple and Locator fetches — but they can handle very large blocks of data. So in a situation where a large LOB is being read, the Locator fetch may time out while either of the piecewise fetches may not.
With the Simple fetch you're limited by the physical memory of your server, but it runs a little faster than the Locator since there are fewer round trips to the server. So if you have small LOBs and need to save a little bandwidth, this is the one to use. If you're going after large LOBs, then the Locator interface is the one. ⭐
If you need to update more than a single row with LOB data, then the Persistent interface can do it while the Locator can't.
If you encounter a situation where you have to access the legacy LOBs (LONG, LONG RAW) and the values are too large for your system, then you can use the Callback or Polling piecewise fetches to get all of the data.
Not all of the Persistent interface has been implemented yet — the following are not supported:
- Piecewise, polling and callback binds for INSERT and UPDATE operations.
- Piecewise array binds for SELECT, INSERT and UPDATE operations.
Most of the time you should just use the "Locator Data Interface" since that's the one with the best combination of speed and size.
All this being said, if you're doing some critical programming I would use the "Data Interface for LOB Locators" because this gives you very fine-grain control of your LOBs — of course the code for this will be somewhat more involved. 😌
🎀 Data Interface for LOB Locators 🎀
The following driver-specific methods let you manipulate "LOB Locators" directly. To select a LOB locator directly, set the "ora_auto_lob" attribute to false — or alternatively they can be returned via PL/SQL procedure calls.
(If you're using a DBI version earlier than 1.36 they must be called via thefunc()method. Heads up that methods called viafunc()don't honourRaiseErroretc, so it's super important to check$dbh->errafter each call. It's recommended that you upgrade to DBI 1.38 or later.)
Also note that LOB locators are only valid while the statement handle that created them is valid. When all references to the original statement handle are lost, the handle is destroyed and the locators are freed. 🫶
ora_lob_read
$data = $dbh->ora_lob_read($lob_locator, $offset, $length);Read a portion of the LOB. $offset starts at 1. Uses the Oracle OCILobRead function.
NOTE: DBD::Oracle post 1.46 will returnundeffor any read lob if the length specified in theora_lob_readis0. See RT 55028. This avoids the potential problem with empty lobs (created withempty_clob) which return a length of0fromora_lob_length— and prior to 1.46 a call toora_lob_readwith a0length would segfault. 💀
ora_lob_write
$rc = $dbh->ora_lob_write($lob_locator, $offset, $data);Write/overwrite a portion of the LOB. $offset starts at 1. Uses the Oracle OCILobWrite function.
ora_lob_append
$rc = $dbh->ora_lob_append($lob_locator, $data);Append $data to the LOB. Uses the Oracle OCILobWriteAppend function.
ora_lob_trim
$rc = $dbh->ora_lob_trim($lob_locator, $length);Trims the length of the LOB to $length. Uses the Oracle OCILobTrim function.
ora_lob_length
$length = $dbh->ora_lob_length($lob_locator);Returns the length of the LOB. Uses the Oracle OCILobGetLength function.
ora_lob_is_init
$is_init = $dbh->ora_lob_is_init($lob_locator);Returns true (1) if the Lob Locator is initialized, false (0) if it is not, or 'undef' if there is an error. Uses the Oracle OCILobLocatorIsInit function.
ora_lob_chunk_size
$chunk_size = $dbh->ora_lob_chunk_size($lob_locator);Returns the chunk size of the LOB. Uses the Oracle OCILobGetChunkSize function. ✨
Okay so for optimal performance, Oracle recommends reading from and writing to a LOB in batches using a multiple of the LOB chunk size. In Oracle 10g and before, when all the defaults are in place, this chunk size defaults to 8k (8192).
💖 LOB Locator Method Examples 💖
Note: bestie, please make sure you first read the note in the section above about multi-byte character set issues with these methods. It's not optional, it's giving mandatory. 💅
The examples below show you how to use LOB Locators to read, write, and append data, and to query the size of large data.
These examples assume a table with two large object columns — one binary, one character — plus a primary key column, defined like this:
CREATE TABLE lob_example (
lob_id INTEGER PRIMARY KEY,
bindata BLOB,
chardata CLOB
)They also assume a sequence for generating unique lob_id field values, defined like this:
CREATE SEQUENCE lob_example_seqExample: Inserting a new row with large data
Unless you've got enough memory to store and bind the entire LOB data for insert all at once, the LOB columns have to be written interactively, piece by piece. For a brand new row, you do it by first inserting a row with empty values in the LOB columns, then modifying that row by writing the large data interactively to the LOB columns using their LOB locators as handles.
The insert statement has to create token values in the LOB columns. Here, we use the empty string for both the binary and character large object columns 'bindata' and 'chardata'.
After the INSERT statement, a SELECT statement is used to acquire LOB locators to the 'bindata' and 'chardata' fields of the newly inserted row. Because these LOB locators get written to afterwards, they must be acquired from a select statement containing the clause 'FOR UPDATE' (LOB locators are only valid within the transaction that fetched them, so they can't be used effectively if AutoCommit is enabled).
my $lob_id = $dbh->selectrow_array( <<" SQL" );
SELECT lob_example_seq.nextval FROM DUAL
SQL
my $sth = $dbh->prepare( <<" SQL" );
INSERT INTO lob_example
( lob_id, bindata, chardata )
VALUES ( ?, EMPTY_BLOB(),EMPTY_CLOB() )
SQL
$sth->execute( $lob_id );
$sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
SELECT bindata, chardata
FROM lob_example
WHERE lob_id = ?
FOR UPDATE
SQL
$sth->execute( $lob_id );
my ( $bin_locator, $char_locator ) = $sth->fetchrow_array();
$sth->finish();
open BIN_FH, "/binary/data/source" or die;
open CHAR_FH, "/character/data/source" or die;
my $chunk_size = $dbh->ora_lob_chunk_size( $bin_locator );
# BEGIN WRITING BIN_DATA COLUMN
my $offset = 1; # Offsets start at 1, not 0
my $length = 0;
my $buffer = '';
while( $length = read( BIN_FH, $buffer, $chunk_size ) ) {
$dbh->ora_lob_write( $bin_locator, $offset, $buffer );
$offset += $length;
}
# BEGIN WRITING CHAR_DATA COLUMN
$chunk_size = $dbh->ora_lob_chunk_size( $char_locator );
$offset = 1; # Offsets start at 1, not 0
$length = 0;
$buffer = '';
while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
$dbh->ora_lob_write( $char_locator, $offset, $buffer );
$offset += $length;
}In this example we demonstrate the use of ora_lob_write() interactively to append data to the columns 'bin_data' and 'char_data'. Had we used ora_lob_append(), we could have saved ourselves the trouble of keeping track of the offset into the lobs. The snippet of code beneath the comment 'BEGIN WRITING BIN_DATA COLUMN' could look as follows:
my $buffer = '';
while ( read( BIN_FH, $buffer, $chunk_size ) ) {
$dbh->ora_lob_append( $bin_locator, $buffer );
}The scalar variables $offset and $length are no longer needed, because ora_lob_append() keeps track of the offset for us. Iconic behaviour honestly. ✨
Example: Updating an existing row with large data
Here we demonstrate a technique for overwriting a portion of a blob field with new binary data. The blob data before and after the section overwritten stays unchanged. Which means this technique could be used for updating fixed length subfields embedded in a binary field. 🎀
my $lob_id = 5; # Arbitrary row identifier, for example
$sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
SELECT bindata
FROM lob_example
WHERE lob_id = ?
FOR UPDATE
SQL
$sth->execute( $lob_id );
my ( $bin_locator ) = $sth->fetchrow_array();
my $offset = 100234;
my $data = "This string will overwrite a portion of the blob";
$dbh->ora_lob_write( $bin_locator, $offset, $data );After running this code, the row where lob_id = 5 will contain, starting at position 100234 in the bin_data column, the string "This string will overwrite a portion of the blob".
Example: Streaming character data from the database
In this example we demonstrate a technique for streaming data from the database to a file handle — in this case STDOUT. This lets you read in and write out more data than could be stored in memory at any given time. No cap, it's a lifesaver. 🦋
my $lob_id = 17; # Arbitrary row identifier, for example
$sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
SELECT chardata
FROM lob_example
WHERE lob_id = ?
SQL
$sth->execute( $lob_id );
my ( $char_locator ) = $sth->fetchrow_array();
my $chunk_size = 1034; # Arbitrary chunk size, for example
my $offset = 1; # Offsets start at 1, not 0
while(1) {
my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size );
last unless length $data;
print STDOUT $data;
$offset += $chunk_size;
}Notice that the select statement does not contain the phrase "FOR UPDATE". Because we are only reading from the LOB Locator returned, and not modifying the LOB it refers to, the select statement does not require the "FOR UPDATE" clause. ✨
A word of caution when using the data returned from an ora_lob_read in a conditional statement. For example, if the code below:
while( my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size ) ) {
print STDOUT $data;
$offset += $chunk_size;
}was used with a chunk size of4096against a blob that requires more than 1 chunk to return the data, and the last chunk is one byte long and contains a zero (ASCII 48), you will miss this last byte — because$datawill contain0, which PERL will see as false and not print it out. Lowkey the sneakiest bug in the whole page. 😭
Example: Truncating existing large data
In this example we truncate the data already present in a large object column in the database. Specifically, for every row in the table, we truncate the 'bindata' value to half its previous length.
After acquiring a LOB Locator for the column, we query its length, then we trim the length by half. Because we modify the large objects with the call to ora_lob_trim(), we must select the LOB locators 'FOR UPDATE'.
my $sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
SELECT bindata
FROM lob_example
FOR UPATE
SQL
$sth->execute();
while( my ( $bin_locator ) = $sth->fetchrow_array() ) {
my $binlength = $dbh->ora_lob_length( $bin_locator );
if( $binlength > 0 ) {
$dbh->ora_lob_trim( $bin_locator, $binlength/2 );
}
}🌸 SPACES AND PADDING 🌸
Trailing Spaces
Only the Oracle OCI 8 strips trailing spaces from VARCHAR placeholder values and uses Nonpadded Comparison Semantics with the result. Which causes trouble if the spaces are needed for comparison with a CHAR value, or to prevent the value from becoming '' — which Oracle treats as NULL. Look for Blank-padded Comparison Semantics and Nonpadded Comparison Semantics in Oracle's SQL Reference or Server SQL Reference for more details. 📚
To preserve trailing spaces in placeholder values for Oracle clients that use OCI 8, either change the default placeholder type with "ora_ph_type", or the placeholder type for a particular call to "bind" in DBI or "bind_param_inout" in DBI with "ora_type" or "TYPE". Using ORA_CHAR with ora_type or "SQL_CHAR" with "TYPE" allows the placeholder to be used with Padded Comparison Semantics if the value it is being compared to is a CHAR, NCHAR, or literal. 💅
Please remember that using spaces as a value, or at the end of a value, makes visually distinguishing values with different numbers of spaces difficult — and it should be avoided. Like, genuinely, don't do it, bestie. 🙈
Oracle Clients that use OCI 9.2 do not strip trailing spaces. 🎀
Padded Char Fields
Oracle Clients after OCI 9.2 will automatically pad CHAR placeholder values to the size of the CHAR. Since the default placeholder type value in DBD::Oracle is ORA_VARCHAR2, to access this behaviour you'll have to change the default placeholder type with "ora_ph_type", or the placeholder type for a particular call with "bind" in DBI or "bind_param_inout" in DBI with "ORA_CHAR". ✨
🦄 UNICODE 🦄
okay bestie, listen up — DBD::Oracle now supports Unicode UTF-8 💖 which is amazing, but there are a bunch of little gotchas you gotta know about, so please, for the love of everything, read this whole section carefully. fr fr.
So here's the plan: we're gonna talk about "Perl and Unicode", then "Oracle and Unicode", and then finally "DBD::Oracle and Unicode". slay.
If you want the general lore on Unicode, it lives at: <http://www.unicode.org/>. It is so worth reading, because there are like a million misconceptions floating around about Unicode and ngl you might be holding a couple of them right now. 🙈
Perl and Unicode
Perl started implementing Unicode back in version 5.6, but honestly the implementation didn't really mature until 5.8 and later. So if you're planning to go full Unicode, I am begging you — use Perl 5.8.2 or later, and actually read the Perl docs on Unicode:
perldoc perluniintro # in Perl 5.8 or later
perldoc perlunicodeAnd then read it again. no seriously. read it again. 📚
Perl's internal Unicode format is UTF-8, which corresponds to the Oracle character set called AL32UTF8.
Oracle and Unicode
Oracle supports many character sets, including several different flavors of Unicode. These include:
AL16UTF16 => valid for NCHAR columns (CSID=2000)
UTF8 => valid for NCHAR columns (CSID=871), deprecated
AL32UTF8 => valid for NCHAR and CHAR columns (CSID=873)When you create an Oracle database, you must specify the DATABASE character set (used for DDL, DML and CHAR datatypes) and the NATIONAL character set (used for NCHAR and NCLOB types). Wanna find out which character sets your database is using? easy:
$hash_ref = $dbh->ora_nls_parameters()
$database_charset = $hash_ref->{NLS_CHARACTERSET};
$national_charset = $hash_ref->{NLS_NCHAR_CHARACTERSET};The Oracle 9.2 and later default for the national character set is AL16UTF16. The default for the database character set is often US7ASCII. That said, lots of experienced DBAs will consider an 8bit character set like WE8ISO8859P1 or WE8MSWIN1252. To use any character set with Oracle other than US7ASCII, you need the NLS_LANG environment variable set. Peep the "Oracle UTF8 is not UTF-8" section below for that whole tea. 🍵
You are strongly urged to read the Oracle Internationalization documentation — specifically the bits about the choices and trade offs for creating databases for use with international character sets. it's giving homework but it's important homework, iykyk.
Oracle uses the NLS_LANG environment variable to indicate what character set is being used on the client. When fetching data, Oracle will convert from whatever the database character set is to the client character set specified by NLS_LANG. Similarly, when sending data to the database, Oracle will convert from the character set specified by NLS_LANG to the database character set. ✨
The NLS_NCHAR environment variable can be used to define a different character set for 'national' (NCHAR) character types.
Both UTF8 and AL32UTF8 can be used in NLS_LANG and NLS_NCHAR. for example:
NLS_LANG=AMERICAN_AMERICA.UTF8
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_NCHAR=UTF8
NLS_NCHAR=AL32UTF8Oracle UTF8 is not UTF-8
AL32UTF8 should be used in preference to UTF8 if it works for you — which it should, for Oracle 9.2 or later. But if you're stuck on an old version of Oracle that doesn't support AL32UTF8, then bestie you should avoid using any Unicode characters that require surrogates. In other words: characters beyond the Unicode BMP (Basic Multilingual Plane). 🚫
Why? Because the character set that Oracle calls "UTF8" does not conform to the UTF-8 standard in how it handles surrogate characters. Technically, the encoding Oracle calls "UTF8" is known as "CESU-8". Here are a couple of extracts from <http://www.unicode.org/reports/tr26/>:
CESU-8 is useful in 8-bit processing environments where binary collation with UTF-16 is required. It is designed and recommended for use only within products requiring this UTF-16 binary collation equivalence. It is not intended nor recommended for open interchange. As a very small percentage of characters in a typical data stream are expected to be supplementary characters, there is a strong possibility that CESU-8 data may be misinterpreted as UTF-8. Therefore, all use of CESU-8 outside closed implementations is strongly discouraged, such as the emittance of CESU-8 in output files, markup language or other open transmission forms.
Oracle uses this internally because it collates (sorts) in the same order as UTF16, which is the basis of Oracle's internal collation definitions. makes sense, honestly. 🤷
Rather than change UTF8 for clients, Oracle chose to define a new character set called "AL32UTF8" which does conform to the UTF-8 standard. (The AL32UTF8 character set can't be used on the server because it would break collation. 💀)
Because of that, for the rest of this document we'll use "AL32UTF8". And if you're running an Oracle version below 9.2, you'll need to use "UTF8" until you upgrade. sorry bestie. 😭
DBD::Oracle and Unicode
DBD::Oracle Unicode support has been implemented for Oracle versions 9 or greater, and Perl version 5.6 or greater (though we strongly suggest you use Perl 5.8.2 or later — like, actually).
Wanna know which Oracle version your DBD::Oracle was built with? Just import the ORA_OCI constant from DBD::Oracle. easy peasy. ✨
Fetching Data
Any data returned from Oracle to DBD::Oracle in the AL32UTF8 character set will be marked as UTF-8, so Perl handles it correctly. 💅
For Oracle to return data in the AL32UTF8 character set, the NLS_LANG or NLS_NCHAR environment variable must be set as described in the previous section.
When fetching NCHAR, NVARCHAR, or NCLOB data from Oracle, DBD::Oracle will set the Perl UTF-8 flag on the returned data if either NLS_NCHAR is AL32UTF8, or NLS_NCHAR is not set and NLS_LANG is AL32UTF8. got it? got it. 💖
When fetching other character data from Oracle, DBD::Oracle will set the Perl UTF-8 flag on the returned data if NLS_LANG is AL32UTF8.
Sending Data using Placeholders
Data bound to a placeholder is assumed to be in the default client character set (specified by NLS_LANG) — except for a few special cases. Here they are, listed with the highest precedence first:
- If the
"ora_csid"attribute is given tobind_param()then that is passed to Oracle and takes precedence. 👑 - If the value is a Perl Unicode string (UTF-8) then DBD::Oracle ensures that Oracle uses the Unicode character set, regardless of the
NLS_LANGandNLS_NCHARsettings. - If the placeholder is for inserting an NCLOB then the client
NLS_NCHARcharacter set is used. (That's useful but inconsistent with the other behaviour so it may change. Best to be explicit by using the"ora_csform"attribute. 🙈) - If the
"ora_csform"attribute is given tobind_param()then that determines if the value should be assumed to be in the default (NLS_LANG) or NCHAR (NLS_NCHAR) client character set.
use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
...
$sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR });or
$dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholdersBinding with bind_param_array and execute_array is also UTF-8 compatible in the same way. But — and listen closely bestie — if you attempt to insert UTF-8 data into a non UTF-8 Oracle instance, or with a non UTF-8 NCHAR or NVARCHAR, the insert will still happen, but an error code of 0 will be returned with the following warning:
DBD Oracle Warning: You have mixed utf8 and non-utf8 in an array bind in parameter#1. This may result in corrupt data.
The Query charset id=1, name=US7ASCIIThe warning will report the parameter number and the NCHAR setting that the query is running. 👀
Sending Data using SQL
Oracle assumes the SQL statement is in the default client character set (as specified by NLS_LANG). Soooo — Unicode strings containing non-ASCII characters should not be used unless the default client character set is AL32UTF8. ⚠️
DBD::Oracle and Other Character Sets and Encodings
The only multi-byte Oracle character set supported by DBD::Oracle is "AL32UTF8" (and "UTF8"). Single-byte character sets should work well. 🫶
🦋 OBJECT & COLLECTION DATA TYPES 🦋
okay so real talk: Oracle lets you build user-defined types that are giving very much object-oriented, and DBD::Oracle is here for it. There are two flavours, bestie:
- Embedded — an object just chillin' inside a column of a regular table
- REF — an object you get at through the REF retrieval mechanism
DBD::Oracle supports only the 'selection' of embedded objects of these types: OBJECT, VARRAY and TABLE, in any combination. And the support is seamless and recursive, meaning you only need to hand over one lil' simple SQL statement and you get all the values in an embedded object. You can either get the values as an array of scalars, or they can be returned into a DBD::Oracle::Object. no cap.
Array example — say we have this type and table:
CREATE OR REPLACE TYPE "PHONE_NUMBERS" as varray(10) of varchar(30);
CREATE TABLE "CONTACT"
( "COMPANYNAME" VARCHAR2(40),
"ADDRESS" VARCHAR2(100),
"PHONE_NUMBERS" "PHONE_NUMBERS"
)The code to access all the data in the table could be something like this:
my $sth = $dbh->prepare('SELECT * FROM CONTACT');
$sth->execute;
while ( my ($company, $address, $phone) = $sth->fetchrow()) {
print "Company: ".$company."\n";
print "Address: ".$address."\n";
print "Phone #: ";
foreach my $items (@$phone){
print $items.", ";
}
print "\n";
}✨ heads up bestie: values in PHONE_NUMBERS are returned as an array reference '@$phone'.
So like I said, DBD::Oracle will automatically drill into the embedded object and extract all of the data as reference arrays of scalars. The example below has an OBJECT type embedded in a TABLE type embedded in an SQL TABLE — yes, it's layers, it's giving parfait:
CREATE OR REPLACE TYPE GRADELIST AS TABLE OF NUMBER;
CREATE OR REPLACE TYPE STUDENT AS OBJECT(
NAME VARCHAR2(60),
SOME_GRADES GRADELIST);
CREATE OR REPLACE TYPE STUDENTS_T AS TABLE OF STUDENT;
CREATE TABLE GROUPS(
GRP_ID NUMBER(4),
GRP_NAME VARCHAR2(10),
STUDENTS STUDENTS_T)
NESTED TABLE STUDENTS STORE AS GROUP_STUDENTS_TAB
(NESTED TABLE SOME_GRADES STORE AS GROUP_STUDENT_GRADES_TAB);The following code will access all of the embedded data:
$SQL='select grp_id,grp_name,students as my_students_test from groups';
$sth=$dbh->prepare($SQL);
$sth->execute();
while (my ($grp_id,$grp_name,$students)=$sth->fetchrow()){
print "Group ID#".$grp_id." Group Name =".$grp_name."\n";
foreach my $student (@$students){
print "Name:".$student->[0]."\n";
print "Marks:";
foreach my $grades (@$student->[1]){
foreach my $marks (@$grades){
print $marks.",";
}
}
print "\n";
}
print "\n";
}Object example — given this object and table:
CREATE OR REPLACE TYPE Person AS OBJECT (
name VARCHAR2(20),
age INTEGER)
) NOT FINAL;
CREATE TYPE Employee UNDER Person (
salary NUMERIC(8,2)
);
CREATE TABLE people (id INTEGER, obj Person);
INSERT INTO people VALUES (1, Person('Black', 25));
INSERT INTO people VALUES (2, Employee('Smith', 44, 5000));The following code will access the data:
$dbh{'ora_objects'} =>1;
$sth = $dbh->prepare("select * from people order by id");
$sth->execute();
# object are fetched as instance of DBD::Oracle::Object
my ($id1, $obj1) = $sth->fetchrow();
my ($id2, $obj2) = $sth->fetchrow();
# get full type-name of object
print $obj1->type_name."44\n"; # 'TEST.PERSON' is printed
print $obj2->type_name."4\n"; # 'TEST.EMPLOYEE' is printed
# get attribute NAME from object
print $obj1->attr('NAME')."3\n"; # 'Black' is printed
print $obj2->attr('NAME')."3\n"; # 'Smith' is printed
# get all attributes as hash reference
my $h1 = $obj1->attr; # returns {'NAME' => 'Black', 'AGE' => 25}
my $h2 = $obj2->attr; # returns {'NAME' => 'Smith', 'AGE' => 44,
# 'SALARY' => 5000 }
# get all attributes (names and values) as array
my @a1 = $obj1->attributes; # returns ('NAME', 'Black', 'AGE', 25)
my @a2 = $obj2->attributes; # returns ('NAME', 'Smith', 'AGE', 44,
# 'SALARY', 5000 )So far DBD::Oracle has been tested on a table with 20 embedded Objects, Varrays and Tables nested to 10 levels. iconic behaviour tbh.
🫶 AnyNULLvalues found in the embedded object will be returned as'undef'.
🌈 OTHER DATA TYPES 🌈
ngl, DBD::Oracle does not explicitly support most Oracle datatypes. It simply asks Oracle to return them as strings and Oracle does so. Mostly. Same energy when binding placeholder values — DBD::Oracle binds them as strings and Oracle converts them to the appropriate type, such as DATE, when used.
Some of these automatic conversions to and from strings use NLS settings to control the formatting for output and the parsing for input. The most common example is the DATE type. The default NLS format for DATE might be DD-MON-YYYY and so when a DATE type is fetched that's how Oracle will format the date. NLS settings also control the default parsing of strings into DATE values. An error will be generated if the contents of the string don't match the NLS format. If you're dealing in dates which don't match the default NLS format then you can either change the default NLS format or, more commonly, use TO_CHAR(field, "format") and TO_DATE(?, "format") to explicitly specify formats for converting to and from strings.
A slightly more subtle problem can occur with NUMBER types. The default NLS settings might format numbers with a fullstop (".") to separate thousands and a comma (",") as the decimal point. Perl will generate warnings and use incorrect values when numbers, returned and formatted as strings in this way by Oracle, are used in a numeric context. You could explicitly convert each numeric value using the TO_CHAR(...) function but that gets tedious very quickly. The best fix is to change the NLS settings. That can be done for an individual connection by doing:
$dbh->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");There are some types, like BOOLEAN, that Oracle does not automatically convert to or from strings (pity 😭). These need to be converted explicitly using SQL or PL/SQL functions.
Examples:
# DATE values
my $sth0 = $dbh->prepare( <<SQL_END );
SELECT username, TO_CHAR( created, ? )
FROM all_users
WHERE created >= TO_DATE( ?, ? )
SQL_END
$sth0->execute( 'YYYY-MM-DD HH24:MI:SS', "2003", 'YYYY' );
# BOOLEAN values
my $sth2 = $dbh->prepare( <<PLSQL_END );
DECLARE
b0 BOOLEAN;
b1 BOOLEAN;
o0 VARCHAR2(32);
o1 VARCHAR2(32);
FUNCTION to_bool( i VARCHAR2 ) RETURN BOOLEAN IS
BEGIN
IF i IS NULL THEN RETURN NULL;
ELSIF i = 'F' OR i = '0' THEN RETURN FALSE;
ELSE RETURN TRUE;
END IF;
END;
FUNCTION from_bool( i BOOLEAN ) RETURN NUMBER IS
BEGIN
IF i IS NULL THEN RETURN NULL;
ELSIF i THEN RETURN 1;
ELSE RETURN 0;
END IF;
END;
BEGIN
-- Converting values to BOOLEAN
b0 := to_bool( :i0 );
b1 := to_bool( :i1 );
-- Converting values from BOOLEAN
:o0 := from_bool( b0 );
:o1 := from_bool( b1 );
END;
PLSQL_END
my ( $i0, $i1, $o0, $o1 ) = ( "", "Something else" );
$sth2->bind_param( ":i0", $i0 );
$sth2->bind_param( ":i1", $i1 );
$sth2->bind_param_inout( ":o0", \$o0, 32 );
$sth2->bind_param_inout( ":o1", \$o1, 32 );
$sth2->execute();
foreach ( $i0, $b0, $o0, $i1, $b1, $o1 ) {
$_ = "(undef)" if ! defined $_;
}
print "$i0 to $o0, $i1 to $o1\n";
# Result is : "'' to '(undef)', 'Something else' to '1'"🎀 Support for Insert of XMLType (ORA_XMLTYPE) 🎀
Inserting large XML data sets into tables with XMLType fields is now supported by DBD::Oracle. The only special requirement is the use of bind_param() with an attribute hash parameter that specifies ora_type as ORA_XMLTYPE. For example with a table like this:
create table books (book_id number, book_xml XMLType);one can insert data using this code:
$SQL='insert into books values (1,:p_xml)';
$xml= '<Books>
<Book id=1>
<Title>Programming the Perl DBI</Title>
<Subtitle>The Cheetah Book</Subtitle>
<Authors>
<Author>T. Bunce</Author>
<Author>Alligator Descartes</Author>
</Authors>
</Book>
<Book id=10000>...
</Books>';
my $sth =$dbh-> prepare($SQL);
$sth-> bind_param("p_xml", $xml, { ora_type => ORA_XMLTYPE });
$sth-> execute();In the above case we will assume that $xml has 10000 Book nodes and is over 32k in size and is well formed XML. This will also work for XML that is smaller than 32k as well. Attempting to insert malformed XML will cause an error. 💀
💅 Binding Cursors 💅
Cursors can be returned from PL/SQL blocks, either from stored functions (or procedures with OUT parameters) or from direct "OPEN" statements, as shown below:
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect(...);
my $sth1 = $dbh->prepare(q{
BEGIN OPEN :cursor FOR
SELECT table_name, tablespace_name
FROM user_tables WHERE tablespace_name = :space;
END;
});
$sth1->bind_param(":space", "USERS");
my $sth2;
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute;
# $sth2 is now a valid DBI statement handle for the cursor
while ( my @row = $sth2->fetchrow_array ) { ... }The only special requirement is the use of "bind_param_inout()" with an attribute hash parameter that specifies "ora_type" as "ORA_RSET". If you don't do that you'll get an error from the "execute()" like: "ORA-06550: line X, column Y: PLS-00306: wrong number or types of arguments in call to ...".
Here's an alternative form using a function that returns a cursor. This example uses the pre-defined weak (or generic) REF CURSOR type SYS_REFCURSOR. This is an Oracle 9 feature.
# Create the function that returns a cursor
$dbh->do(q{
CREATE OR REPLACE FUNCTION sp_ListEmp RETURN SYS_REFCURSOR
AS l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR select ename, empno from emp
ORDER BY ename;
RETURN l_cursor;
END;
});
# Use the function that returns a cursor
my $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
my $sth2;
$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute;
# $sth2 is now a valid DBI statement handle for the cursor
while ( my @row = $sth2->fetchrow_array ) { ... }okay so a cursor you got from PL/SQL like that? You can absolutely hand it right back into PL/SQL by binding it for input, like this lil example here that explicitly closes a cursor 💅
my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
$sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
$sth3->execute;ngl you don't usually need to close a cursor explicitly like that. Oracle closes it for you automatically at the very first client-server interaction after the cursor statement handle gets destroyed 🫶 An explicit close is only cute/desirable if the reference to the cursor handle from the PL/SQL statement handle is delaying the destruction of the cursor handle for too long. That reference sticks around until the PL/SQL handle is re-bound, re-executed or destroyed. So like. it lingers bestie 😭
NOTE: FromDBD::Oracle1.57 onwards, functions or procedures returningSYS_REFCURSORs which have not been opened (they're still in the initialised state) will returnundeffor the cursor statement handle — e.g. in the example above if thesp_ListEmpfunction simply returnedl_cursorinstead of opening it. Which is actually iconic bc it means you can have a function/procedure which can elect to open the cursor or not ✨ Before this change, if you called a function/procedure which returned aSYS_REFCURSORwhich was not opened,DBD::Oraclewould error in the execute for aOCIAttrGeton the uninitialised cursor 💀
See the "curref.pl" script in the Oracle.ex directory in the DBD::Oracle source distribution for a complete working example.
🌈 Fetching Nested Cursors 🌈
Oracle supports the use of select list expressions of type REF CURSOR. These may be explicit cursor expressions — "CURSOR(SELECT ...)" — or calls to PL/SQL functions which return REF CURSOR values. The values of these expressions are known as nested cursors 🦋
The value returned to a Perl program when a nested cursor is fetched is a statement handle. This statement handle is ready to be fetched from. It should not (indeed, must not) be executed. do not execute it bestie i am begging 🙈
Oracle imposes a restriction on the order of fetching when nested cursors are used. Suppose $sth1 is a handle for a select statement involving nested cursors, and $sth2 is a nested cursor handle fetched from $sth1. $sth2 can only be fetched from while $sth1 is still active, and the row containing $sth2 is still current in $sth1. Any attempt to fetch another row from $sth1 renders all nested cursor handles previously fetched from $sth1 defunct. gone. deceased. 💀
Fetching from such a defunct handle results in an error with the message:
ERROR nested cursor is defunct (parent row is no longer current)Which means the "fetchall..." or "selectall..." methods are not useful for queries returning nested cursors. By the time such a method returns, all the nested cursor handles it has fetched will be defunct. rip 🪦
It is necessary to use an explicit fetch loop, and to do all the fetching of nested cursors within the loop, as the following example shows:
use DBI;
my $dbh = DBI->connect(...);
my $sth = $dbh->prepare(q{
SELECT dname, CURSOR(
SELECT ename FROM emp
WHERE emp.deptno = dept.deptno
ORDER BY ename
) FROM dept ORDER BY dname
});
$sth->execute;
while ( my ($dname, $nested) = $sth->fetchrow_array ) {
print "$dname\n";
while ( my ($ename) = $nested->fetchrow_array ) {
print " $ename\n";
}
}The cursor returned by the function "sp_ListEmp" defined in the previous section can be fetched as a nested cursor as follows:
my $sth = $dbh->prepare(q{SELECT sp_ListEmp FROM dual});
$sth->execute;
my ($nested) = $sth->fetchrow_array;
while ( my @row = $nested->fetchrow_array ) { ... }🎀 Pre-fetching Nested Cursors 🎀
By default, DBD::Oracle pre-fetches rows in order to reduce the number of round trips to the server. For queries which do not involve nested cursors, the number of pre-fetched rows is controlled by the DBI database handle attribute "RowCacheSize" (q.v.). ✨
In Oracle, server side open cursors are a controlled resource, limited in number, on a per session basis, to the value of the initialization parameter "OPEN_CURSORS". Nested cursors count towards this limit 👑 Each nested cursor in the current row counts 1, as does each nested cursor in a pre-fetched row. Defunct nested cursors do not count. slay.
An Oracle specific database handle attribute, "ora_max_nested_cursors", further controls pre-fetching for queries involving nested cursors. For each statement handle, the total number of nested cursors in pre-fetched rows is limited to the value of this parameter. The default value is 0, which disables pre-fetching for queries involving nested cursors. so if you're wondering why ur nested cursors aren't pre-fetching... that's why bestie 💅
✨ PL/SQL Examples ✨
Okay bestie, this is the good stuff 💖 Most of these PL/SQL examples come from: Eric Bartley <bartley@cc.purdue.edu>. We love a credited icon.
First up, the package itself — run this with sqlplus:
/*
* PL/SQL to create package with stored procedures invoked by
* Perl examples. Execute using sqlplus.
*
* Use of "... OR REPLACE" prevents failure in the event that the
* package already exists.
*/
CREATE OR REPLACE PACKAGE plsql_example
IS
PROCEDURE proc_np;
PROCEDURE proc_in (
err_code IN NUMBER
);
PROCEDURE proc_in_inout (
test_num IN NUMBER,
is_odd IN OUT NUMBER
);
FUNCTION func_np
RETURN VARCHAR2;
END plsql_example;
/
CREATE OR REPLACE PACKAGE BODY plsql_example
IS
PROCEDURE proc_np
IS
whoami VARCHAR2(20) := NULL;
BEGIN
SELECT USER INTO whoami FROM DUAL;
END;
PROCEDURE proc_in (
err_code IN NUMBER
)
IS
BEGIN
RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
END;
PROCEDURE proc_in_inout (
test_num IN NUMBER,
is_odd IN OUT NUMBER
)
IS
BEGIN
is_odd := MOD(test_num, 2);
END;
FUNCTION func_np
RETURN VARCHAR2
IS
ret_val VARCHAR2(20);
BEGIN
SELECT USER INTO ret_val FROM DUAL;
RETURN ret_val;
END;
END plsql_example;
/
/* End PL/SQL for example package creation. */And now the Perl side, because this is literally what we're all here for 🫶
use DBI;
my($db, $csr, $ret_val);
$db = DBI->connect('dbi:Oracle:database','user','password')
or die "Unable to connect: $DBI::errstr";
# So we don't have to check every DBI call we set RaiseError.
# See the DBI docs now if you're not familiar with RaiseError.
$db->{RaiseError} = 1;
# Example 1 Eric Bartley <bartley@cc.purdue.edu>
#
# Calling a PLSQL procedure that takes no parameters. This shows you the
# basic's of what you need to execute a PLSQL procedure. Just wrap your
# procedure call in a BEGIN END; block just like you'd do in SQL*Plus.
#
# p.s. If you've used SQL*Plus's exec command all it does is wrap the
# command in a BEGIN END; block for you.
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_NP;
END;
});
$csr->execute;
# Example 2 Eric Bartley <bartley@cc.purdue.edu>
#
# Now we call a procedure that has 1 IN parameter. Here we use bind_param
# to bind out parameter to the prepared statement just like you might
# do for an INSERT, UPDATE, DELETE, or SELECT statement.
#
# I could have used positional placeholders (e.g. :1, :2, etc.) or
# ODBC style placeholders (e.g. ?), but I prefer Oracle's named
# placeholders (but few DBI drivers support them so they're not portable).
my $err_code = -20001;
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_IN(:err_code);
END;
});
$csr->bind_param(":err_code", $err_code);
# PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to 'fail'.
# Because we set RaiseError, the DBI will croak (die) so we catch that with eval.
eval {
$csr->execute;
};
print 'After proc_in: $@=',"'$@', errstr=$DBI::errstr, ret_val=$ret_val\n";
# Example 3 Eric Bartley <bartley@cc.purdue.edu>
#
# Building on the last example, I've added 1 IN OUT parameter. We still
# use a placeholders in the call to prepare, the difference is that
# we now call bind_param_inout to bind the value to the place holder.
#
# Note that the third parameter to bind_param_inout is the maximum size
# of the variable. You normally make this slightly larger than necessary.
# But note that the Perl variable will have that much memory assigned to
# it even if the actual value returned is shorter.
my $test_num = 5;
my $is_odd;
$csr = $db->prepare(q{
BEGIN
PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
END;
});
# The value of $test_num is _copied_ here
$csr->bind_param(":test_num", $test_num);
$csr->bind_param_inout(":is_odd", \$is_odd, 1);
# The execute will automagically update the value of $is_odd
$csr->execute;
print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n";
# Example 4 Eric Bartley <bartley@cc.purdue.edu>
#
# What about the return value of a PLSQL function? Well treat it the same
# as you would a call to a function from SQL*Plus. We add a placeholder
# for the return value and bind it with a call to bind_param_inout so
# we can access its value after execute.
my $whoami = "";
$csr = $db->prepare(q{
BEGIN
:whoami := PLSQL_EXAMPLE.FUNC_NP;
END;
});
$csr->bind_param_inout(":whoami", \$whoami, 20);
$csr->execute;
print "Your database user name is $whoami\n";
$db->disconnect;You can find more examples in the t/plsql.t file in the DBD::Oracle source directory. Go look, it's giving extra credit 💅
Heads up bestie, there's a bug situation: Oracle 9.2 appears to have a bug where a variable bound with bind_param_inout() that isn't assigned to by the executed PL/SQL block may contain garbage. See <http://www.mail-archive.com/dbi-users@perl.org/msg18835.html>
🚫 Avoid Using "SQL Call" 🚫
Avoid using the "SQL Call" statement with DBD:Oracle as you might find that DBD::Oracle will not raise an exception in some case. Specifically if you use "SQL Call" to run a procedure all "No data found" exceptions will be quietly ignored and returned as null. According to Oracle support this is part of the same mechanism where;
select (select * from dual where 0=1) from dualreturns a null value rather than an exception. Not it just silently handing you nothing like it's fine 😭
🛠️ CONTRIBUTING 🛠️
If you'd like DBD::Oracle to do something new or different the best way to make that happen is to do it yourself and email to dbi-dev@perl.org a patch of the source code (using 'diff' - see below) that shows the changes. Main character energy honestly ✨
Speak before you patch 💬
For anything non-trivial or possibly controversial it's a good idea to discuss (on dbi-dev@perl.org) the changes you propose before actually spending time working on them. Otherwise you run the risk of them being rejected because they don't fit into some larger plans you may not be aware of. Fr fr, ask first, save yourself the heartbreak 🥲
🌈 WHICH VERSION OF DBD::ORACLE IS FOR ME? 🌈
From version 1.25 onwards DBD::Oracle only support Oracle clients 9.2 or greater. Support for ProC connections was dropped in 1.29.
If you are still stuck with an older version of Oracle or its client you might want to look at the table below.
+---------------------+-----------------------------------------------------+
| | Oracle Version |
+---------------------+----+-------------+---------+------+--------+--------+
| DBD::Oracle Version | <8 | 8.0.3~8.0.6 | 8iR1~R2 | 8iR3 | 9i | 9.2~11 |
+---------------------+----+-------------+---------+------+--------+--------+
| 0.1~16 | Y | Y | Y | Y | Y | Y |
+---------------------+----+-------------+---------+------+--------+--------+
| 1.17 | Y | Y | Y | Y | Y | Y |
+---------------------+----+-------------+---------+------+--------+--------+
| 1.18 | N | N | N | Y | Y | Y |
+---------------------+----+-------------+---------+------+--------+--------+
| 1.19 | N | N | N | Y | Y | Y |
+---------------------+----+-------------+---------+------+--------+--------+
| 1.20 | N | N | N | Y | Y | Y |
+---------------------+----+-------------+---------+------+--------+--------+
| 1.21~1.24 | N | N | N | N | Y | Y |
+---------------------+----+-------------+---------+------+--------+--------+
| 1.25+ | N | N | N | N | N | Y |
+---------------------+----+-------------+---------+------+--------+--------+As there are dozens of different versions of Oracle's clients this list does not include all of them, just the major released versions of Oracle. 💀
Note that one can still connect to any Oracle version with the older DBD::Oracle versions the only problem you will have is that some of the newer OCI and Oracle features available in later DBD::Oracle releases will not be available to you. Trade-offs, bestie 💅
So to make a short story a little longer:
- If you are using Oracle 7 or early 8 DB and you can manage to get a 9 client and you can use any DBD::Oracle version.
- If you have to use an Oracle 7 client then DBD::Oracle 1.17 should work
- Same thing for 8 up to R2, use 1.17, if you are lucky and have the right patch-set you might go with 1.18.
- For 8iR3 you can use any of the DBD::Oracle versions up to 1.21. Again this depends on your patch-set, If you run into trouble go with 1.19
- After 9.2 you can use any version you want.
- It seems that the 10g client can only connect to 9 and 11 DBs while the 9 can go back to 7 and even get to 10. I am not sure what the 11g client can connect to.
👀 SEE ALSO 👀
- DBI <http://search.cpan.org/~timb/DBD-Oracle/MANIFEST> for all files in the DBD::Oracle source distribution including the examples in the
Oracle.exdirectory - DBD::Oracle Tutorial — <http://www.pythian.com/blogs/wp-content/uploads/introduction-dbd-oracle.html>
- Oracle Instant Client — <http://www.oracle.com/technology/tech/oci/instantclient/index.html>
- Oracle on Linux — <http://www.ixora.com.au/>
- Free Oracle Tools and Links
ora_explainsupplied and installed with DBD::Oracle.- <http://www.orafaq.com/>
- <http://vonnieda.org/oracletool/>
- Commercial Oracle Tools and Links
- Assorted tools and references for general information. No recommendation implied.
- <http://www.platinum.com>
- <http://www.SoftTreeTech.com>
- Also PL/Vision from RevealNet and Steven Feuerstein, and "Q" from Savant Corporation.
🎀 ACKNOWLEDGEMENTS 🎀
A great many people have helped with DBD::Oracle over the 17 years between 1994 and 2011. Far too many to name, but we thank them all. Many are named in the Changes file. Genuinely so much love to the whole squad 🫶✨
👑 AUTHORS 👑
- Tim Bunce <timb@cpan.org>
- John Scoles <byterock@cpan.org>
- Yanick Champoux <yanick@cpan.org>
- Martin J. Evans <mjevans@cpan.org>
💖 COPYRIGHT AND LICENSE 💖
Okay bestie, legal moment, but make it cute. ✨
This software is copyright (c) 2022, 2021, 2019, 2014, 2013, 2012, 2011, 2010 by Tim Bunce. 👑
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself. So yeah — free, open, shareable, the whole vibe. 🫶 No gatekeeping, no weird strings attached, just the exact same deal Perl itself comes with. Periodt. 💅
And that's a wrap on the whole page, bestie!! 🎀 You just read all of DBD::Oracle(3pm) — every flag, every attribute, every dramatic caveat — and honestly? Iconic behaviour. 🌈
Tiny lil' receipt for your records: this page ships as part of perl v5.36.0, and the date stamped on it is 2023-02-26, filed under the manpage name DBD::Oracle(3pm). Keep that in your back pocket, iykyk. 💜
Now go connect to that database, bestie — you've got this. 🦋🌸💖
📦 where this page comes from
- debian bookworm: libdbd-oracle-perl 1.83-1 · Artistic-1.0-Perl OR GPL-1.0-or-later
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms