DBD-Oracle
view release on metacpan or search on metacpan
1.64 2013-05-22
- promote 1.63_00 to stable.
1.63_00 2013-05-03
[ENHANCEMENTS]
- DBD-Oracle: Use of uninitialized value $user_only in uc [RT#84657]
(Steffen Goeldner)
[BUG FIXES]
- Make 50cursor.t Oracle8-friendly. (RT#84660, patch by Steffen Goeldner)
- Makefile.PL's use of ACL tweaked for Suse Enterprise 11 SP2
(RT#84530, patch by Alfred Nathaniel)
[DOCUMENTATION]
- Bogus 227 directory no longer required for MacOS. (GH#1, patch
by theory)
1.62 2013-04-30
- promote 1.61_00 to official release
1.61_00 2013-04-15
[BUG FIXES]
- Adjust the privs needed for the DROP/CREATE table test. [GH#35]
(Joe Crotty)
- Fixed RT84170 - when using scrollable cursors and you've done a
positioned fetch and then keep fetching until the end of the
result-set calls to fetch never return undef and you keep getting
the last row forever. Also added test case to the 51scroll.t test
(Martin J. Evans).
1.60 2013-04-01
- Move github repository to github.com/pythian/DBD-Oracle.
1.58 2013-03-05
- promote 1.57_00 to official release
violation and RT82663 - Errors if a returned SYS_REFCURSOR is not
opened (Martin J. Evans)
- Fix RT82663. If a procedure/function returns a SYS_REFCURSOR which
was never opened DBD::Oracle magics a DBI statement handle into
existence and attempts to describe it (which fails). This change
examines a returned SYS_REFCURSOR and if it it is initialised but
not executed does not create a DBI statement handle and returns
undef instead. So now if you have a procedure/function which
returns a SYS_REFCURSOR and never open it you'll get undef back
instead of a useless statement handle. Extended 50cursor.t test
to check the above fix. (Martin J. Evans)
[DOCUMENTATION]
- Update Lion instructions for 64-bit Instant Client. (GH#37, patch by
theory)
1.56 2013-01-08
- fix t/26exe_array.t in the case of no db connection (RT82506,
reported by Peter Rabbitson)
- Fix bug in 39attr.t which could fail if using an Oracle Client >
11 but not >= 11.2 (Martin J. Evans)
- ora_server_version was not documented.
- Fix RT80566. 70meta.t test fails with Oracle 8 because
ALL_TAB_COLUMNS.CHAR_LENGTH is new in Oracle 9. Use DATA_LENGTH
instead on pre-9 versions. Thanks to Steffen Goeldner for RT and
patch.
- Fix RT80704. 51scroll.t test checks scrollable cursors but assumes
all Oracles support them (only 9 and above). Thanks to Steffen
Goeldner for RT and patch.
- Fix RT81067. 58object.t has some subtype tests and subtypes were
introduced in Oracle 9. Skip if < Oracle 9. Thanks to Steffen
Goeldner for RT and patch.
- Fix RT81317. 34pres_lobs.t uses the Data Interface for Persistent
LOBs which is new in Oracle 9. Skip if < Oracle 9. Thanks to
Steffen Goeldner for RT and patch.
Fix for odd error with Ping from Tom Payerle
Removed the NEW_OCI_INIT compile directive and the deprecated OCIInitialize calls
Fix for rt.cpan.org Ticket #=57256 : Double free problem in dbdimp.c by John Scoles
Fix for invalid format in trace of OCILobLocatorIsInit_log_stat reported by Martin Evans Fixed by John Scoles
Fix for very odd UNKNOWN OCI STATUS 1041 (OCILobFreeTemporary) on disconnect reported by John Parker and Bob Mcgowan fixed by John Scoles
Fix for rt.cpan.org Ticket #=55445: get_info(28) SQL_IDENTIFIER_CASE seems to return the wrong value from Martin J Evans and a bunch of re jigging from John Scoles
Patch for PL/SQL: numeric or value error: character string buffer too small from Scott T. Hildreth
Fix for rt.cpan.org Ticket #=51594 type_info and type_info_all miss vital information from John Scoles
Added ora_lob_is_init function by John Scoles
Fix for rt.cpan.org Ticket #=55031 Ubuntu Server Building with Oracle XE under 32-bit from Brian Candler
Fix for rt.cpan.org Ticket #=56810 bug with multiple nested cursor from John Scoles
Fix for bug found only on Big-Endian hardware reported by Timothy Everett and others from Charles Jardine
Fix for memory leak when using prepared_cached and lobs reported by Mark Bobak and Martin Evans found and fixed by John Scoles and a test from Martin Evans
Added more entries to the Readmes from John Scoles
* Changes in DBD-Oracle 1.24_01(svn rev 14060)
This release has been prepared specifically for the 'Debian' http://www.debian.org project. It contains no changes
to functionality or usage. The following has been changed
Fixed some formatting and typos in Pod from Julián Patiño
Added from rt.cpan.org Ticket #=49436 Patch to add support for a few Oracle data types to type_info_all from David Hull
Added from rt.cpan.org Ticket #=49435 Patch to add support for a few Oracle data types to dbd_describe from David Hull
Fix for rt.cpan.org Ticket #=49331 Bad code example in POD from John Scoles
Added support for looking up OCI_DTYPE_PARAM Attributes
Added support for looking up csform values
Fix for rt.cpan.org Ticket #=46763,46998 enhancement -Rowcache size is now being properly implemented with row fetch buffer from John Scoles
Fix for rt.cpan.org Ticket #=46438 enhancement -Errors returned by procedures are now unicode strings from Martin Evans, John Scoles and Tim Bunce
Fix for rt.cpan.org Ticket #=47503 bugfix - using more than 1 LOB in insert broken from APLA
Fix for rt.cpan.org Ticket #=46613 bugfix - sig-abort on nested objects with ora_objects=1 from TomasP
Fix for rt.cpan.org Ticket #=46661 DBD::Oracle hangs when insert/update with LOB and quoted table name from APLA
Fix for rt.cpan.org Ticket #=46246 fetching from nested cursor (returned from procedure) leads to application crash (abort) from John Scoles
Fix for rt.cpan.org Ticket #=46016 LOBs bound with ora_field broken from RKITOVER
Fix for bug in 58object.t when test run as externally identified user from Charles Jardine
* Changes in DBD-Oracle 1.23(svn rev 12724)
Fix from rt.cpan.org ticket #=44788 bool in_lite should be char in_literal
Fix for UTF8 and blobs by John Scoles with Milo van der Leij
Fix for some warnings and one bug in ocitrace.h from Charles Jardine
Fix in case there may be a bug in 11 where the OCI_ATTR_DATA_SIZE my return 0 which should never happen, John Scoles
Fix on the Makefile.PL for a possible sql bug in IC from Martin Evans
Added a change from a suggestion from Martin Evans for making ref cursors faster.
Added rt.cpan.org Ticket #=42328 ora_objects attribute for extended embedded objects support from Tomas Pokorny
Fix for rt.cpan.org Ticket #=42328 user defined types from different schema in describe_obj from Tomas Pokorny
Added a README for sun suggested by Jim McCullars
Clean up of white space and formatting to 4 tabs from John Scoles
Fix for GCC 4.3 warnings from Eric Simon
Standardized the dbd_verbose levels so they are all 3 and over. from John Scoles
Added private statement functions ora_stmt_type_name and ora_stmt_type from John Scoles
Update to POD from Chris Underhill
Added README.win64.txt with content from Alex Buttery
Fix for rt.cpan.org Ticket #=21920 Bug with Oracle DBD for Mac OS X Instant Client From boingolover
* Changes in DBD-Oracle 1.21(svn rev 11067) 11th April 2008
Added Notes to README.win32.txt on installing Instant Client 11.1.0.6.0 from John Scoles
Added the oci_typecode_name method to get the name rather than just the number of an OCI_TYPECODE from John Scoles
Fixed a unreported bug with Embedded Objects from John Scoles
Fixes for #34621 & 33791 from RT cpan
Added patch to allow faster fetch from REF CURSORs from Biswadeep Chowdhury
Updated the Todo file for next version from John Scoles
Added support for the 10.2 Data Interface for Persistent LOBs by John Scoles
Changed the way pre-fetching is done by John Scoles
Added support for Scrollable cursors from John Scoles
Changed the max size of cache_rows to a sb4 rather than a int and or a ub4 from John Scoles
Added support for Lobs in select of OCI Embedded Objects from John Scoles with a big thankyou to Paul Weiss
Fixed for embedded object in object from Paul Weiss
Added support for direct insert of large XML data into XMLType fields from Hendrik Fuss & John Scoles
Fixed memory leak (not releasing Temp Lob with OCILobFreeTemporary) when created for a bind from John Scoles
Added support for bind_param_inout_array for use with execute_array from John Scoles
Added enhancement for Embedded Objects handling from Paul G. Weiss
Fixed to Makefile.PL let it read makefiles from other makes from Alexander V Alekseev
Updated POD to tell users to Avoid Using "SQL Call" from Charles Jardine
Updated POD to account for rt.cpan.org #30910: "DBD-Oracle crashes when trying to read empty LOB" from John Scoles
maintenance!
Fixed automatic csform setting for some UTF8 cases and for Oracle 8.0
Fixed truncation error on fetch into UTF8 charset thanks to Honza Pazdziora.
Fixed INTERVAL DAY TO SECOND thanks to Honza Pazdziora.
Fixed unicode tests for cygwin thanks to Andy Hassall.
Fixed undef warnings when connecting with undef $user.
Fixed undef warnings from $dbh->get_info(18);
Fixed LOB streaming example thanks to Pablo Zorzoli.
Added support for nested cursors in select lists thanks to Charles Jardine.
Added "Trailing Spaces" section to docs thanks to Michael A Chase.
Added support for binary floats/doubles thanks to Dennis Box.
Added INSTANCE_NAME, SERVER and SERVICE_NAME as valid connect keywords
in the 'dbi:Oracle:x=y' short form of connecting without tnsnames.ora.
For example 'dbi:Oracle:host=localhost;service_name=xe;server=dedicated'
Added auto-detection of ORACLE_HOME in some configurations.
Changed "Binding Cursors" docs, clarifying examples thanks to Charles Jardine.
Changed tests to use ORACLE_DSN or DBI_DSN env vars if defined thanks to Jill Vogel.
Updated README.vms re logical name tables thanks to Jakob Snoer.
Added (restored) the error "possibly near <*> indicator" marker for
syntax errors thanks to Jason Hitt and Andy Hassall.
Added $dbh->{ora_parse_error_offset} attribute thanks to Andy Hassall.
Added auto setting of $dbh->{Username} if not given to connect
i.e. using Oracle OS authentication and connecting as "/"
by selecting SYS_CONTEXT('userenv','session_user') from the db
thanks to Eric Lenio and Andy Hassall.
Updated README.macosx thanks to Danial Pearce and William Goedicke.
Updated README.help for UnixWare thanks to Earle Nietzel.
Updated PL/SQL cursor examples, plus Oracle.ex/README and curref.pl
examples thanks to Michael A Chase.
Updated PL/SQL example setup thanks to Bob Kline.
* Changes in DBD-Oracle 1.14 27th March 2003
NOTE: OCI 7 and Oraperl will not be supported in future releases.
Implemented inserting NULL values into LOB fields using placeholders,
(sponsored by bayerwald-fenster.de thanks to Bernhard Donaubauer).
Fixed broken get_info() due to missing GetInfo.pm #2274.
Updated table_info to decode SYS and SYSTEM to prepend 'SYSTEM ' thanks to Olga Voronina
Updated table_info and added get_info, foreign_key_info thanks to Steffen Goeldner.
Added that ShowErrorStatement is now enabled by default.
Added ParamValues attribute so ShowErrorStatement includes bind_param values.
Added experimental utf8 support thanks to Stefan Eissing. See README.utf8
Added BFILE support thanks to David Hull.
Added :ora_session_modes export tag for ORA_SYSDBA ORA_SYSOPER constants,
added ORA_STRING and ORA_CHARZ to :ora_types export tag and added docs
for ora_ph_type attribute and other doc fixes, thanks to Michael A Chase.
Added t/cursor.t tests thanks to Jeffrey Horn.
Added ability to share connections between threads via ora_dbh_share
attribute, thanks to Gerald Richter.
Added ability to connect() via 'external process context', for embedding
Perl DBI into Oracle server, thanks to Jeff Horwitz.
Documentation changes:
Updated README.hpux thanks to Lincoln Baxter and Jay Strauss.
Documented ora_parse_lang, ora_auto_lob, and ora_check_sql prepare()
method attributes thanks to Michael A Chase.
Retested for perl 5.4.4 for possibly the last time. You have been warned.
In future I'll only be testing on perl >= 5.5.3 and >=5.6.1.
Documentation:
Documented workaround for hang in "repetitive connect" test thanks to Alexi Lookin.
Short version: add bequeath_detach=yes to sqlnet.ora file.
Documented ora_oratab_orahome connect attribute (Oracle OCI 7 only).
Documented ora_module_name connect attribute thanks to Renzo Toma.
Added IRIX linking advice into README.help thanks to Dennis Box.
Added AIX core dump on disconnect advice into README.help.
Added docs on how Oracle determines the timezone thanks to Brad Howerter.
Added 'return cursor function' example to docs thanks to Michael A. Chase
Added more notes to README.java thanks to Peter Ludemann and Dave C.
Added more notes to README.login thanks to Geoff Young.
* Changes in DBD::Oracle 1.08 7th August 2001
Fix Oracle 7 build broken in previous release.
Fix for 64bit builds thanks to Alan Burlison.
Fix for 9i Makefile.PL thanks to Dong Wang.
Added Oracle.ex/curref.pl thanks to Geoffery Young.
REF types are now returned in a form usable with Oracle::OCI module
Added $dbh->{ora_ph_type} attribute to define default bind type:
1=> VARCHAR2, does strip trailing spaces, embedded \0 bytes okay
5=> STRING, doesn't strip trailing spaces, embedded \0 ends string
96=> CHAR, doesn't strip trailing spaces, embedded \0 okay
97=> CHARZ, doesn't strip trailing spaces, embedded \0 ends string?
The two CHAR types force 'blank-padded comparison semantics'.
Needs testing and documenting, volunteers most welcome!
Added (many) typecasts to avoid compiler warnings thanks to Denis Goddard.
Added ability to pass existing DBD::Oracle select statement handle
(cursor) back _into_ Oracle as a ref cursor type thanks to Mike Moretti.
Note that this enables a workaround for closing ref cursors:
$dbh->do("BEGIN CLOSE :cursor; END;", undef, $sth_ref_csr_to_close);
Improved Win32 support in Makefile.PL thanks to Michael A. Chase.
Workaround Win32::TieRegistry FETCH error during global destruction.
Re-enable row cache by default for OCI8 (can give big speed increase).
Fixed bug in OCI8 row cache calculation thanks to Greg Stark.
* Changes in DBD::Oracle 1.04 11th July 2000
Added info on workarounds for Java thread related linker errors
on Solaris with Oracle 8.1.6. Thanks to Andi Lamprecht.
Fixed memory leak on connect. Thanks to Jeffrey W. Baker.
Fixed memory and ref cursor leaks. Thanks to Mike Moretti and John Tobey.
Fixed SEGV on insert/update or many LOBS. Thanks to Honza Pazdziora.
Fixed treating SUCCESS_WITH_INFO from finish() as an error.
Fixed passing of attribs to connect thanks to K.Takaoka.
Fixed cursor binding example in docs thanks to Michael Chase.
Build using otrace/demo/atmoci.mk as last resort thanks to Chris Sylvain.
Improved reliability of "perl Makefile.PL -b" configure option.
Improved reliability of t/plsql.t cursor tests. Hopefully.
Improved reliability of ping method.
Fixed broken SQL type warning in bind_param.
* Changes in DBD::Oracle 1.03 12th July 1999
Added "perl Makefile.PL -b" configure option. Links DBD::Oracle
using same linker args as Oracle's own OCI demo applications.
Added connect("dbi:Oracle:host=foo;sid=bar", ...) syntax.
Added OCI8 function trace at level 6, useful for Oracle support.
Added ora_session_mode attribute to connect (eg SYSDBA/SYSOPER).
Contributions from Michael Chase (plus docs from Andy Duncan):
Documented plsql_errstr & dbms_output_enable/_put/_get.
Enhanced $dbh->func('plsql_errstr') output.
Replaced old Oraperl examples with DBI/DBD::Oracle ones.
* Changes in DBD::Oracle 1.02 14th June 1999
LongReadLen no longer limited to 64KB for OCI 7 & 8!
But beware of OCI 7 bug when fetching >64KB.
See t/long.t for more details.
Fixed OCI7 ref cursor missing data row.
Fixed OCI8 LOB statement handle leak & improved trace.
* Changes in DBD::Oracle 1.01 8th June 1999
Enhanced diagnostics in t/long.t test suite.
Removed byte with high-bit set from t/long.t test data.
Disable finish if database disconnected or perl is terminating.
Made t/general.t work with other NLS settings.
Added Cygwin support thanks to Alexander Smishlajev.
Fixed 'undeclared identifier' error building with old Oracle's.
* Changes in DBD::Oracle 1.00 4th June 1999
Increased default row cache size for improved performance.
Added OCI8 binding of cursors! Sponsored by cp.net.
Added OCI7 binding of cursors! (Was easier after OCI8 work :-)
Added OCI8 blob_read (only for LOBs not LONGs) thanks to Jim Lawson.
Added OCI8 re-authentication thanks to John Tobey.
Added t/long.t test script for LONG/LONG RAW/CLOB/BLOB handling.
Length of fetched LONG RAW string now 2 * LongReadLen for OCI7 & 8.
Fixed LONG fetches being one byte longer than LongReadLen.
Fixed OCI8 non-reporting of LobWrite error.
Fixed OCI8 "LongReadLen too small and/or LongTruncOk not set" hint.
Fixed OCI7 probable cursor leak.
Fixed ping method to be more robust. This should fix
the "morning bug" problem with Apache::DBI.
Fixed t/general.t core dump thanks to Donald Buczek.
Fixed ora_check_sql prepare attribute (for selects).
Fetch errors (non-row level) now turn off the Active attribute.
* Changes in DBD::Oracle 0.61, 9th April 1999
Fixed execute() always returning 0 rows! (with OCI 7).
Fixed $sth->bind_param(..., SQL_CHAR);
field-level errors when fetching.
Compiles okay now for systems without SQLT_CUR defined.
Only sets SA_RESTART on SIGCLD if connect was successful.
* Changes in DBD::Oracle 0.51 (Oraperl 1.37), 5rd July 1998
Makefile.PL no longer tries to link with just -lclntsh directly :-(
Improvements to some HP-UX builds (hopefully).
DBI->data_sources ' dummy ' value removed (and list now sorted).
connect failure now shows actual Oracle error message again.
Initial (incomplete) support for binding cursor vars (see t/plsql.t).
* Changes in DBD::Oracle 0.50 (Oraperl 1.36), 3rd June 1998
Makefile.PL changes: fixed -c option, now searches for .h files,
tries alternate location for sysliblist, checks for executable
orainst/inspdver before using it.
Fixed cursor leak.
Added first word of tnsnames.ora name as aliases if no clash.
* Changes in DBD::Oracle 0.49 (Oraperl 1.36), 1st June 1998
Further improvements to build process over 0.48.
Fixed broken truncation error in 0.48.
Fetch ORACLE_SID from Win32 registry (thanks to Preston Bannister)
Improved automatic row cache sizing (prompted by Jon Meek).
Added $sth->{ora_cache_rows} and $sth->{ora_est_row_width}
as read-only attributes to make cache size logic easier to test.
See Changes file in that release.
Major reworking of internal data management!
Only execute and fetchrow have yet to move into dbdimp.c.
These were kept back since I plan other associated changes.
Known problems:
Warning mode is on by default so certain oraperl coding
styles cause warnings such as:
Statement handle ... destroyed without finish() at ...
disconnect(...) invalidates 1 associated cursor(s) at ...
In future oraperl mode handles will have those warnings disabled.
...other changes to numerous/minor to mention
* Changes in DBD::Oracle 0.20, 1 Aug 1995:
Fixed core dump when binding an undef (treated as a NULL)
Binding a string longer that 2000 bytes will use LONG type.
Workaround OSF makefile and oratype.h problems
PL/SQL := construct will no longer confuse dbd_preparse()
t/28array_bind.t
t/30long.t
t/31lob.t
t/31lob_extended.t
t/32xmltype.t
t/34pres_lobs.t
t/36lob_leak.t
t/38taf.t
t/39attr.t
t/40ph_type.t
t/50cursor.t
t/51scroll.t
t/55nested.t
t/56embbeded.t
t/58object.t
t/60reauth.t
t/70meta.t
t/80ora_charset.t
t/README
t/lib/DBDOracleTestLib.pm
t/lib/ExecuteArray.pm
*/
/* ====== Include Oracle Header Files ====== */
#ifndef CAN_PROTOTYPE
#define signed /* Oracle headers use signed */
#endif
/* The following define avoids a problem with Oracle >=7.3 where
* ociapr.h has the line:
* sword obindps(struct cda_def *cursor, ub1 opcode, text *sqlvar, ...
* In some compilers that clashes with perls 'opcode' enum definition.
*/
#define opcode opcode_redefined
/* Hack to fix broken Oracle oratypes.h on OSF Alpha. Sigh. */
#if defined(__osf__) && defined(__alpha)
#ifndef A_OSF
#define A_OSF
#endif
#endif
README.mkdn view on Meta::CPAN
The contents and format of ORA\_OCI are subject to change (it may,
for example, become a _version object_ in later releases).
I recommend 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_RELATIVE
These 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
README.mkdn view on Meta::CPAN
See ["Prepare Postponed Till Execute"](#prepare-postponed-till-execute) for more information.
- ora\_exe\_mode
This will set the execute mode of the current statement. Presently
only one mode is supported;
OCI_STMT_SCROLLABLE_READONLY - make result set scrollable
See ["SCROLLABLE CURSORS"](#scrollable-cursors) for more details.
- ora\_prefetch\_rows
Sets the number of rows to be prefetched. If it is not set, then the
default value is 1. See ["Row Prefetching"](#row-prefetching) for more details.
- 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
README.mkdn view on Meta::CPAN
### 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.
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.
README.mkdn view on Meta::CPAN
ORA_CHAR, ORA_MLSLABEL, ORA_RSET
Additional values when DBD::Oracle was built using OCI 8 and later:
ORA_CLOB, ORA_BLOB, ORA_XMLTYPE, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE
Additional values when DBD::Oracle was built using OCI 9.2 and later:
SQLT_CHR, SQLT_BIN
See ["Binding Cursors"](#binding-cursors) for the correct way to use ORA\_RSET.
See ["LOBS AND LONGS"](#lobs-and-longs) for how to use ORA\_CLOB and ORA\_BLOB.
See ["SYS.DBMS\_SQL datatypes"](#sys-dbms_sql-datatypes) for ORA\_VARCHAR2\_TABLE, ORA\_NUMBER\_TABLE.
See ["Data Interface for Persistent LOBs"](#data-interface-for-persistent-lobs) for the correct way to use SQLT\_CHR and SQLT\_BIN.
See ["OTHER DATA TYPES"](#other-data-types) for more information.
See also ["Placeholders and Bind Values" in DBI](https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values).
README.mkdn view on Meta::CPAN
# 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.
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.
## 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
README.mkdn view on Meta::CPAN
$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();
When using scrollable cursors it is required that you use the $sth->finish() method when you are done with the cursor as this type of
cursor has to be explicitly cancelled on the server. If you do not do this you may cause resource problems on your database.
# 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,
README.mkdn view on Meta::CPAN
## 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 ) { ... }
A cursor obtained from PL/SQL as above may be passed back to PL/SQL
by binding for input, as shown in this example, which explicitly
closes a cursor:
my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
$sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
$sth3->execute;
It is not normally necessary to close a cursor explicitly in this
way. Oracle will close the cursor automatically at the first
client-server interaction after the cursor statement handle is
destroyed. An explicit close may be desirable if the reference to the
cursor handle from the PL/SQL statement handle delays the destruction
of the cursor handle for too long. This reference remains until the
PL/SQL handle is re-bound, re-executed or destroyed.
NOTE: From DBD::Oracle 1.57 functions or procedures returning
SYS\_REFCURSORs which have not been opened (are still in the
initialised state) will return undef for the cursor statement handle
e.g., in the example above if the sp\_ListEmp function simply returned l\_cursor
instead of opening it. This 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 a SYS\_REFCURSOR which was not opened
DBD::Oracle would error in the execute for a OCIAttrGet on 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.
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.
Fetching from such a defunct handle results in an error with the message
`ERROR nested cursor is defunct (parent row is no longer current)`.
This means that 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.
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.
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.
# PL/SQL Examples
Most of these PL/SQL examples come from: Eric Bartley <bartley@cc.purdue.edu>.
/*
* 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
case 8: /* LONG */
case 21: /* BINARY FLOAT os-endian */
case 22: /* BINARY DOUBLE os-endian */
case 23: /* RAW */
case 24: /* LONG RAW */
case 96: /* CHAR */
case 97: /* CHARZ */
case 100: /* BINARY FLOAT oracle-endian */
case 101: /* BINARY DOUBLE oracle-endian */
case 106: /* MLSLABEL */
case 102: /* SQLT_CUR OCI 7 cursor variable */
case 112: /* SQLT_CLOB / long */
case 113: /* SQLT_BLOB / long */
case 116: /* SQLT_RSET OCI 8 cursor variable */
case ORA_VARCHAR2_TABLE: /* 201 */
case ORA_NUMBER_TABLE: /* 202 */
case ORA_XMLTYPE: /* SQLT_NTY must be careful here as its value (108) is the same for an embedded object Well really only XML clobs not embedded objects */
return 1;
}
return 0;
}
#ifdef THIS_IS_NOT_CURRENTLY_USED
static int
case 8: /* LONG */
case 21: /* BINARY FLOAT os-endian */
case 22: /* BINARY DOUBLE os-endian */
case 23: /* RAW */
case 24: /* LONG RAW */
case 96: /* CHAR */
case 97: /* CHARZ */
case 100: /* BINARY FLOAT oracle-endian */
case 101: /* BINARY DOUBLE oracle-endian */
case 106: /* MLSLABEL */
case 102: /* SQLT_CUR OCI 7 cursor variable */
case 116: /* SQLT_RSET OCI 8 cursor variable */
case ORA_VARCHAR2_TABLE: /* 201 */
case ORA_NUMBER_TABLE: /* 202 */
case ORA_XMLTYPE: /* SQLT_NTY must be carefull here as its value (108) is the same for an embedded object Well realy only XML clobs not embedded objects */
case 113: /* SQLT_BLOB / long */
return SQLT_BIN;
case 112: /* SQLT_CLOB / long */
return SQLT_CHR;
}
return dbtype;
dbd_st_cancel(SV *sth, imp_sth_t *imp_sth)
{
dTHX;
sword status;
status = OCIBreak(imp_sth->svchp, imp_sth->errhp);
if (status != OCI_SUCCESS) {
oci_error(sth, imp_sth->errhp, status, "OCIBreak");
return 0;
}
/* if we are using a scrolling cursor we should get rid of the
cursor by fetching row 0 */
if (imp_sth->exe_mode==OCI_STMT_SCROLLABLE_READONLY){
OCIStmtFetch_log_stat(imp_sth, imp_sth->stmhp, imp_sth->errhp, 0,OCI_FETCH_NEXT,0, status);
}
return 1;
}
int
dbd_db_rollback(SV *dbh, imp_dbh_t *imp_dbh)
}
else if (kl==11 && (strEQ(key, "ora_verbose") || strEQ(key, "dbd_verbose"))) {
dbd_verbose = SvIV (valuesv);
}
else if (kl==10 && strEQ(key, "AutoCommit")) {
DBIc_set(imp_dbh,DBIcf_AutoCommit, on);
}
else if (kl==12 && strEQ(key, "RowCacheSize")) {
imp_dbh->RowCacheSize = SvIV(valuesv);
}
else if (kl==22 && strEQ(key, "ora_max_nested_cursors")) {
imp_dbh->max_nested_cursors = SvIV(valuesv);
}
else if (kl==20 && strEQ(key, "ora_array_chunk_size")) {
imp_dbh->array_chunk_size = SvIV(valuesv);
}
else if (kl==11 && strEQ(key, "ora_ph_type")) {
if (SvIV(valuesv)!=1 && SvIV(valuesv)!=5 && SvIV(valuesv)!=96 && SvIV(valuesv)!=97)
warn("ora_ph_type must be 1 (VARCHAR2), 5 (STRING), 96 (CHAR), or 97 (CHARZ)");
else
imp_dbh->ph_type = SvIV(valuesv);
}
}
else if (kl==10 && strEQ(key, "AutoCommit")) {
retsv = boolSV(DBIc_has(imp_dbh,DBIcf_AutoCommit));
}
else if (kl==12 && strEQ(key, "RowCacheSize")) {
retsv = newSViv(imp_dbh->RowCacheSize);
}
else if (kl==11 && strEQ(key, "RowsInCache")) {
retsv = newSViv(imp_dbh->RowsInCache);
}
else if (kl==22 && strEQ(key, "ora_max_nested_cursors")) {
retsv = newSViv(imp_dbh->max_nested_cursors);
}
else if (kl==11 && strEQ(key, "ora_ph_type")) {
retsv = newSViv(imp_dbh->ph_type);
}
else if (kl==13 && strEQ(key, "ora_ph_csform")) {
retsv = newSViv(imp_dbh->ph_csform);
}
else if (kl==22 && strEQ(key, "ora_parse_error_offset")) {
retsv = newSViv(imp_dbh->parse_error_offset);
}
(phs->progv) ? val: "",
(long)phs->alen, (long)phs->maxlen,
phs->ftype,sql_typecode_name(phs->ftype), phs->indp, at_exec);
}
return 1;
}
/*
* Rebind an "in" cursor ref to its real statement handle
* This allows passing cursor refs as "in" to pl/sql (but only if you got the
* cursor from pl/sql to begin with)
*/
int
pp_rebind_ph_rset_in(SV *sth, imp_sth_t *imp_sth, phs_t *phs)
{
dTHX;
dTHR;
SV * sth_csr = phs->sv;
D_impdata(imp_sth_csr, imp_sth_t, sth_csr);
sword status;
phs->progv = (char*)&phs->desc_h;
phs->maxlen = 0;
OCIBindByName_log_stat(imp_sth, imp_sth->stmhp, &phs->bndhp, imp_sth->errhp,
(text*)phs->name,
(sb4)strlen(phs->name),
phs->progv,
0,
(ub2)phs->ftype,
/* I, MJE have no evidence that passing an indicator to this func
causes ORA-01001 (invalid cursor) errors. Also, without it
you cannot test the indicator to check we have a valid output
parameter. However, it would seem when you do specify an
indicator it always comes back as 0 so it is useless. */
NULL, /* using &phs->indp triggers ORA-01001 errors! */
NULL,
0,
0,
NULL,
OCI_DEFAULT,
status);
if (status != OCI_SUCCESS) {
oci_error(sth, imp_sth->errhp, status, "OCIBindByName SQLT_RSET");
return 0;
}
/*
NOTE: The code used to magic a DBI stmt handle into existence
here before even knowing if the output parameter was going to
be a valid open cursor. The code to do this moved to post execute
below. See RT 82663 - Errors if a returned SYS_REFCURSOR is not opened
*/
}
else { /* post-execute - setup the statement handle */
dTHR;
dSP;
D_imp_dbh_from_sth;
HV *init_attr = newHV();
int count;
ub4 stmt_state = 99;
sword status;
SV * sth_csr;
/* Before we go to the bother of attempting to allocate a new sth
for this cursor make sure the Oracle sth is executed i.e.,
the returned cursor may never have been opened */
OCIAttrGet_stmhp_stat2(imp_sth, (OCIStmt*)phs->desc_h, &stmt_state, 0,
OCI_ATTR_STMT_STATE, status);
if (status != OCI_SUCCESS) {
oci_error(sth, imp_sth->errhp, status, "OCIAttrGet OCI_ATTR_STMT_STATE");
return 0;
}
if (DBIc_DBISTATE(imp_sth)->debug >= 3 || dbd_verbose >= 3 ) {
/* initialized=1, executed=2, end of fetch=3 */
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" returned cursor/statement state: %u\n", stmt_state);
}
/* We seem to get an indp of 0 even for a cursor which was never
opened and set to NULL. If this is the case we check the stmt state
and find the cursor is initialized but not executed - there is no
point in going any further if it is not executed - just return undef.
See RT 82663 */
if (stmt_state == OCI_STMT_STATE_INITIALIZED) {
OCIHandleFree_log_stat(imp_sth, (OCIStmt *)phs->desc_h,
OCI_HTYPE_STMT, status);
if (status != OCI_SUCCESS) {
oci_error(sth, imp_sth->errhp, status, "OCIHandleFree");
return 0;
}
phs->desc_h = NULL;
phs->sv = newSV(0); /* undef */
return 1;
}
/* Now we know we have an executed cursor create a new sth */
ENTER;
SAVETMPS;
PUSHMARK(SP);
XPUSHs(sv_2mortal(newRV((SV*)DBIc_MY_H(imp_dbh))));
XPUSHs(sv_2mortal(newRV((SV*)init_attr)));
PUTBACK;
count = perl_call_pv("DBI::_new_sth", G_ARRAY);
SPAGAIN;
if (count != 2)
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" pp_exec_rset bind %s - allocated %s...\n",
phs->name, neatsvpv(phs->sv, 0));
sth_csr = phs->sv;
if (DBIc_DBISTATE(imp_sth)->debug >= 3 || dbd_verbose >= 3 )
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" bind %s - initialising new %s for cursor 0x%lx...\n",
phs->name, neatsvpv(sth_csr,0), (unsigned long)phs->progv);
{
D_impdata(imp_sth_csr, imp_sth_t, sth_csr); /* TO_DO */
/* copy appropriate handles and attributes from parent statement */
imp_sth_csr->envhp = imp_sth->envhp;
imp_sth_csr->errhp = imp_sth->errhp;
imp_sth_csr->srvhp = imp_sth->srvhp;
imp_sth_csr->svchp = imp_sth->svchp;
imp_sth_csr->auto_lob = imp_sth->auto_lob;
imp_sth_csr->pers_lob = imp_sth->pers_lob;
imp_sth_csr->clbk_lob = imp_sth->clbk_lob;
imp_sth_csr->piece_size = imp_sth->piece_size;
imp_sth_csr->piece_lob = imp_sth->piece_lob;
imp_sth_csr->is_child = 1; /*no prefetching on a cursor or sp*/
/* assign statement handle from placeholder descriptor */
imp_sth_csr->stmhp = (OCIStmt*)phs->desc_h;
phs->desc_h = NULL; /* tell phs that we own it now */
/* force stmt_type since OCIAttrGet(OCI_ATTR_STMT_TYPE) doesn't work! */
imp_sth_csr->stmt_type = OCI_STMT_SELECT;
DBIc_IMPSET_on(imp_sth_csr);
if (SvNIOKp(ph_namesv) || (name && isDIGIT(name[0]))) {
sprintf(namebuf, ":p%d", (int)SvIV(ph_namesv));
name = namebuf;
name_len = strlen(name);
}
assert(name != Nullch);
if (SvROK(newvalue)
&& !IS_DBI_HANDLE(newvalue) /* dbi handle allowed for cursor variables */
&& !SvAMAGIC(newvalue) /* overload magic allowed (untested) */
&& !sv_derived_from(newvalue, "OCILobLocatorPtr" ) /* input LOB locator*/
&& !(SvTYPE(SvRV(newvalue))==SVt_PVAV) /* Allow array binds */
)
croak("Can't bind a reference (%s)", neatsvpv(newvalue,0));
if (SvTYPE(newvalue) > SVt_PVAV) /* Array binding supported */
croak("Can't bind a non-scalar, non-array value (%s)", neatsvpv(newvalue,0));
if (SvTYPE(newvalue) == SVt_PVLV && is_inout) /* may allow later */
croak("Can't bind ``lvalue'' mode scalar as inout parameter (currently)");
sword status;
int is_select = (imp_sth->stmt_type == OCI_STMT_SELECT);
if (debug >= 2 || dbd_verbose >= 3 )
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" dbd_st_execute %s (out%d, lob%d)...\n",
oci_stmt_type_name(imp_sth->stmt_type), outparams, imp_sth->has_lobs);
/* Don't attempt execute for nested cursor. It would be meaningless,
and Oracle code has been seen to core dump */
if (imp_sth->nested_cursor) {
oci_error(sth, NULL, OCI_ERROR,
"explicit execute forbidden for nested cursor");
return -2;
}
if (outparams) { /* check validity of bind_param_inout SV's */
int i = outparams;
while(--i >= 0) {
phs_t *phs = (phs_t*)(void*)SvPVX(AvARRAY(imp_sth->out_params_av)[i]);
SV *sv = phs->sv;
/* Make sure we have the value in string format. Typically a number */
" with %s = [] (len %ld/%ld, indp %d, otype %d, ptype %d)\n",
phs->name,
(long)phs->alen, (long)phs->maxlen, phs->indp,
phs->ftype, (int)SvTYPE(sv));
av_clear((AV*)SvRV(sv));
}
else
/* Some checks for mutated storage since we pointed oracle at it. */
if (SvTYPE(sv) != phs->sv_type
|| (SvOK(sv) && !SvPOK(sv))
/* SvROK==!SvPOK so cursor (SQLT_CUR) handle will call dbd_rebind_ph */
/* that suits us for now */
|| SvPVX(sv) != phs->progv
|| (SvPOK(sv) && SvCUR(sv) > UB2MAXVAL)
) {
if (!dbd_rebind_ph(sth, imp_sth, phs))
croak("Can't rebind placeholder %s", phs->name);
}
else {
/* String may have grown or shrunk since it was bound */
/* so tell Oracle about it's current length */
int num_fields = DBIc_NUM_FIELDS(imp_sth);
int i;
if (DBIc_DBISTATE(imp_sth)->debug >= 6 || dbd_verbose >= 6 )
PerlIO_printf(DBIc_LOGPIO(imp_sth), " dbd_st_finish\n");
if (!DBIc_ACTIVE(imp_sth))
return 1;
/* Cancel further fetches from this cursor. */
/* We don't close the cursor till DESTROY (dbd_st_destroy). */
/* The application may re execute(...) it. */
/* Turn off ACTIVE here regardless of errors below. */
DBIc_ACTIVE_off(imp_sth);
for(i=0; i < num_fields; ++i) {
imp_fbh_t *fbh = &imp_sth->fbh[i];
if (fbh->fetch_cleanup) fbh->fetch_cleanup(sth, fbh);
}
if (PL_dirty) /* don't walk on the wild side */
return 1;
if (!DBIc_ACTIVE(imp_dbh)) /* no longer connected */
return 1;
/*fetching on a cursor with row =0 will explicitly free any
server side resources this is what the next statment does,
not sure if we need this for non scrolling cursors they should die on
a OER(1403) no records)*/
OCIStmtFetch_log_stat(imp_sth, imp_sth->stmhp, imp_sth->errhp, 0,
OCI_FETCH_NEXT,0, status);
if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO) {
oci_error(sth, imp_sth->errhp, status, "Finish OCIStmtFetch");
return 0;
}
return 1;
void
dbd_st_destroy(SV *sth, imp_sth_t *imp_sth)
{
int fields;
int i;
sword status;
dTHX ;
D_imp_dbh_from_sth;
/* Don't free the OCI statement handle for a nested cursor. It will
be reused by Oracle on the next fetch. Indeed, we never
free these handles. Experiment shows that Oracle frees them
when they are no longer needed.
*/
/* get rid of describe handle if used*/
/* if we are using a scrolling cursor we should get rid of the
cursor by fetching row 0 */
if (imp_sth->exe_mode==OCI_STMT_SCROLLABLE_READONLY && DBIc_ACTIVE(imp_dbh)) {
OCIStmtFetch_log_stat(imp_sth, imp_sth->stmhp, imp_sth->errhp, 0,OCI_FETCH_NEXT,0, status);
}
if (imp_sth->dschp){
OCIHandleFree_log_stat(imp_sth, imp_sth->dschp, OCI_HTYPE_DESCRIBE, status);
}
if (DBIc_DBISTATE(imp_sth)->debug >= 6 || dbd_verbose >= 6 )
PerlIO_printf(DBIc_LOGPIO(imp_sth), " dbd_st_destroy %s\n",
(PL_dirty) ? "(OCIHandleFree skipped during global destruction)" :
(imp_sth->nested_cursor) ?"(OCIHandleFree skipped for nested cursor)" : "");
if (!PL_dirty) { /* XXX not ideal, leak may be a problem in some cases */
if (!imp_sth->nested_cursor) {
OCIHandleFree_log_stat(imp_sth, imp_sth->stmhp, OCI_HTYPE_STMT, status);
if (status != OCI_SUCCESS)
oci_error(sth, imp_sth->errhp, status, "OCIHandleFree");
}
}
/* Free off contents of imp_sth */
if (imp_sth->lob_refetch)
ora_free_lob_refetch(sth, imp_sth);
ub4 module_namel;
char *client_identifier; /*user defined*/
ub4 client_identifierl;
char *action; /*user defined*/
ub4 actionl;
int RowCacheSize; /* both of these are defined by DBI spec*/
int RowsInCache; /* this vaue is RO and cannot be set*/
int ph_type; /* default oratype for placeholders */
ub1 ph_csform; /* default charset for placeholders */
int parse_error_offset; /* position in statement of last error */
int max_nested_cursors; /* limit on cached nested cursors per stmt */
int array_chunk_size; /* the max size for an array bind */
ub4 server_version; /* version of Oracle server */
};
#define DBH_DUP_OFF sizeof(dbih_dbc_t)
#define DBH_DUP_LEN (sizeof(struct imp_dbh_st) - sizeof(dbih_dbc_t))
typedef struct lob_refetch_st lob_refetch_t; /* Define sth implementor data structure */
dbih_stc_t com; /* MUST be first element in structure */
void *(*get_oci_handle) _((imp_sth_t *imp_sth, int handle_type, int flags));
OCIEnv *envhp; /* copy of dbh pointer */
OCIError *errhp; /* copy of dbh pointer */
OCIServer *srvhp; /* copy of dbh pointer */
OCISvcCtx *svchp; /* copy of dbh pointer */
OCIStmt *stmhp; /* oci statement handle */
OCIDescribe *dschp; /* oci describe handle */
int is_child; /* if this is child from a ref cursor or SP*/
ub2 stmt_type; /* OCIAttrGet OCI_ATTR_STMT_TYPE */
U16 auto_lob; /* use auto lobs*/
int pers_lob; /*use dblink for lobs only for 10g Release 2. or later*/
int clbk_lob; /*use dblink for lobs only for 10g Release 2. or later*/
int piece_lob; /*use piece fetch for lobs*/
ub4 piece_size; /*used in callback to set the size of the piece to get*/
int has_lobs; /*Statement has bound LOBS */
int ret_lobs; /*Statement returns LOBS */
lob_refetch_t *lob_refetch;
int nested_cursor; /* cursors fetched from SELECTs */
AV *bind_tuples; /* Bind tuples in array execute, or NULL */
int rowwise; /* If true, bind_tuples is list of */
/* tuples, otherwise list of columns. */
/* Input Details */
char *statement; /* sql (see sth_scan) */
HV *all_params_hv; /* all params, keyed by name */
AV *out_params_av; /* quick access to inout params */
int ora_pad_empty; /* convert ""->" " when binding */
/* Select Column Output Details */
};
struct imp_fbh_st { /* field buffer EXPERIMENTAL */
imp_sth_t *imp_sth; /* 'parent' statement */
int field_num; /* 0..n-1 */
/* Oracle's description of the field */
OCIParam *parmdp;
OCIDefine *defnp;
void *desc_h; /* descriptor if needed (LOBs, cursors etc) */
ub4 desc_t; /* OCI type of descriptor */
ub4 define_mode; /*the normal case for a define*/
int (*fetch_func) _((SV *sth, imp_fbh_t *fbh, SV *dest_sv));
void (*fetch_cleanup) _((SV *sth, imp_fbh_t *fbh));
ub2 dbtype; /* actual type of field (see ftype) */
ub2 dbsize;
ub2 prec; /* XXX docs say ub1 but ub2 is needed */
sb1 scale;
ub1 nullok;
char *name;
examples/README view on Meta::CPAN
They are adapted from the Oraperl example scripts in ../Oraperl.ex/ to
show how to do the same things in Perl 5 and DBI.
$dbh->{RaiseError} is set to 1 in all scripts for automatic error checking.
bind.pl Demonstrates how execute() and fetchrow_array() may be
combined to make a simple table lookup program with placeholders.
commit.pl Demonstrates the use of commit() and rollback().
curref.pl Demonstrates how to use a cursor bind variable.
ex.pl Reads data from a table and prints it using a format.
Also illustrates how to recognise NULL fields and bind_columns
with known column names.
japh Just another Perl hacker, written for DBI.
This is no one-liner, but it demonstrates a few things.
mktable.pl Creates a table, puts some data into it, drops it.
Demonstrates do(), placeholders, inserting and reading NULL values,
examples/curref.pl view on Meta::CPAN
#!/usr/bin/env perl
#
# curref.pl - by Geoffrey Young
#
# for this example, we create a package that contains
# two procedures:
# emp_cursor - returns a specific cursor reference
# ref_cursor_close - closes any cursor reference
#
# to actually run this example as is, you will need the
# oracle demo tables. otherwise, it's just sample code...
use DBI;
use DBD::Oracle qw(:ora_types);
use strict;
# Set trace level if '-# trace_level' option is given
examples/curref.pl view on Meta::CPAN
my ( $inst, $user, $pass ) = @ARGV;
# Connect to database
my $dbh = DBI->connect( "dbi:Oracle:$inst", $user, $pass,
{ AutoCommit => 0, RaiseError => 1, PrintError => 0 } )
or die $DBI::errstr;
my $sql = qq(
CREATE OR REPLACE PACKAGE curref_test
IS
TYPE cursor_ref IS REF CURSOR;
PROCEDURE emp_cursor (job_in IN VARCHAR2, curref IN OUT cursor_ref);
PROCEDURE ref_cursor_close (curref IN cursor_ref);
END;
);
my $rv = $dbh->do($sql);
print "The package has been created...\n";
$sql = qq(
CREATE OR REPLACE PACKAGE BODY curref_test
IS
PROCEDURE emp_cursor (job_in IN VARCHAR2, curref IN OUT cursor_ref)
IS
BEGIN
OPEN curref FOR select ename, job from emp where job = job_in;
END;
PROCEDURE ref_cursor_close (curref IN cursor_ref)
IS
BEGIN
close curref;
END;
END;
);
$rv = $dbh->do($sql);
print "The package body has been created...\n";
print "These are the results from the ref cursor:\n";
$sql = qq(
BEGIN
curref_test.emp_cursor(:job_in, :curref);
END;
);
my $curref;
my $sth = $dbh->prepare($sql);
$sth->bind_param(":job_in", "CLERK");
$sth->bind_param_inout(":curref", \$curref, 0, {ora_type => ORA_RSET});
$sth->execute;
$curref->dump_results;
open_cursors();
$sql = qq(
BEGIN
curref_test.ref_cursor_close(:curref);
END;
);
$sth = $dbh->prepare($sql);
$sth->bind_param(":curref", $curref, {ora_type => ORA_RSET});
$sth->execute;
print "The cursor is now closed\n";
print "just to prove it...\n";
open_cursors();
$sql = "DROP PACKAGE curref_test"; # Also drops PACKAGE BODY
$rv = $dbh->do($sql);
print "The package has been dropped...\n";
$dbh->disconnect;
sub open_cursors {
eval {
$sth = $dbh->prepare(
'SELECT user, sql_text FROM sys.v_$open_cursor ORDER BY user, sql_text');
$sth->execute;
print "Here are the open cursors:\n";
$sth->dump_results;
};
if ( $@ ) {
print "Unable to SELECT from SYS.V_\$OPEN_CURSOR:\n";
if ( 942 == $DBI::err ) {
print " User $user needs SELECT permission.\n";
}
else { print "$@\n"; }
}
}
examples/ora_explain.pl view on Meta::CPAN
$menu->separator();
my $count = 0;
while ((my ($index_owner, $index_name) = $qry->fetchrow_array()))
{
$menu->command(-label => "$index_owner.$index_name",
-command => [ \&disp_index, $index_owner, $index_name ]);
$count++;
}
$qry->finish();
busy(0);
$menu->Popup(-popover => "cursor", -popanchor => "nw") if ($count);
return(1);
}
################################################################################
# Produce the query plan for the SQL in $PlanSql and store it in $Plan
sub _explain()
{
# Check there is some SQL
my $stmt = $PlanSql->get("1.0", "end");
examples/ora_explain.pl view on Meta::CPAN
as well as a button with the same function. Selecting this will popup a new
top-level window containing a menu bar and three frames, labelled "SQL Cache",
"SQL Statement Statistics" and "SQL Selection Criteria". At the bottom of the
window are three buttons labelled "Capture SQL", "Explain" and "Close".
The menu bar has two pulldown menus "File" and "Help". "File" allows you to
Save the contents of the SQL Cache pane to a file, copy the selected SQL
statement to the Explain window and Close the Grab window.
The "SQL Cache" frame shows the statements currently in the Oracle SQL cache.
As you move the cursor over this window, each SQL statement will be highlighted
with an outline box. Single-clicking on a statement in the SQL Cache pane will
highlight the statement in green and display more detailed information on that
statement in the SQL Statement Statistics frame.
If you want to save the entire contents of the SQL Cache pane, you can do this
from the "File" menu.
The "SQL Selection Criteria" frame allows you to specify which SQL statements
you are interested in, and how you want them sorted. The pattern used to select
statements is a normal perl regexp. Once you have defined the selection
lib/DBD/Oracle.pm view on Meta::CPAN
sub get_info {
my($dbh, $info_type) = @_;
require DBD::Oracle::GetInfo;
my $v = $DBD::Oracle::GetInfo::info{int($info_type)};
$v = $v->($dbh) if ref $v eq 'CODE';
return $v;
}
sub private_attribute_info { #this should only be for ones that have setters and getters
return { ora_max_nested_cursors => undef,
ora_array_chunk_size => undef,
ora_ph_type => undef,
ora_ph_csform => undef,
ora_parse_error_offset => undef,
ora_dbh_share => undef,
ora_envhp => undef,
ora_svchp => undef,
ora_errhp => undef,
ora_init_mode => undef,
ora_events => undef,
lib/DBD/Oracle.pm view on Meta::CPAN
The contents and format of ORA_OCI are subject to change (it may,
for example, become a I<version object> in later releases).
I recommend that you avoid checking for exact values.
=item :ora_fetch_orient
OCI_FETCH_CURRENT OCI_FETCH_NEXT OCI_FETCH_FIRST OCI_FETCH_LAST
OCI_FETCH_PRIOR OCI_FETCH_ABSOLUTE OCI_FETCH_RELATIVE
These constants are used to set the orientation of a fetch on a scrollable cursor.
=item :ora_exe_modes
OCI_STMT_SCROLLABLE_READONLY
=item :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
lib/DBD/Oracle.pm view on Meta::CPAN
=head3 Row Prefetching
Row prefetching occurs on the server side and uses the DBI database handle attribute C<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.
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.
lib/DBD/Oracle.pm view on Meta::CPAN
=head1 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.
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.
=head2 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.
=head2 Scrollable Cursor Methods
The following driver-specific methods are used with scrollable cursors.
=over
=item 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.
lib/DBD/Oracle.pm view on Meta::CPAN
$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.
=item Use Finish
$sth->finish();
When using scrollable cursors it is required that you use the $sth->finish() method when you are done with the cursor as this type of
cursor has to be explicitly cancelled on the server. If you do not do this you may cause resource problems on your database.
=back
=head1 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.
lib/DBD/Oracle.pm view on Meta::CPAN
=head2 Binding Cursors
Cursors can be returned from PL/SQL blocks, either from stored
functions (or procedures with OUT parameters) or
from direct C<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 C<bind_param_inout()> with an
attribute hash parameter that specifies C<ora_type> as C<ORA_RSET>.
If you don't do that you'll get an error from the C<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 ) { ... }
A cursor obtained from PL/SQL as above may be passed back to PL/SQL
by binding for input, as shown in this example, which explicitly
closes a cursor:
my $sth3 = $dbh->prepare("BEGIN CLOSE :cursor; END;");
$sth3->bind_param(":cursor", $sth2, { ora_type => ORA_RSET } );
$sth3->execute;
It is not normally necessary to close a cursor explicitly in this
way. Oracle will close the cursor automatically at the first
client-server interaction after the cursor statement handle is
destroyed. An explicit close may be desirable if the reference to the
cursor handle from the PL/SQL statement handle delays the destruction
of the cursor handle for too long. This reference remains until the
PL/SQL handle is re-bound, re-executed or destroyed.
NOTE: From DBD::Oracle 1.57 functions or procedures returning
SYS_REFCURSORs which have not been opened (are still in the
initialised state) will return undef for the cursor statement handle
e.g., in the example above if the sp_ListEmp function simply returned l_cursor
instead of opening it. This 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 a SYS_REFCURSOR which was not opened
DBD::Oracle would error in the execute for a OCIAttrGet on the uninitialised
cursor.
See the C<curref.pl> script in the Oracle.ex directory in the DBD::Oracle
source distribution for a complete working example.
=head2 Fetching Nested Cursors
Oracle supports the use of select list expressions of type REF CURSOR.
These may be explicit cursor expressions - C<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.
Oracle imposes a restriction on the order of fetching when nested
cursors are used. Suppose C<$sth1> is a handle for a select statement
involving nested cursors, and C<$sth2> is a nested cursor handle fetched
from C<$sth1>. C<$sth2> can only be fetched from while C<$sth1> is
still active, and the row containing C<$sth2> is still current in C<$sth1>.
Any attempt to fetch another row from C<$sth1> renders all nested cursor
handles previously fetched from C<$sth1> defunct.
Fetching from such a defunct handle results in an error with the message
C<ERROR nested cursor is defunct (parent row is no longer current)>.
This means that the C<fetchall...> or C<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.
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 C<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 ) { ... }
=head2 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 C<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 C<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.
An Oracle specific database handle attribute, C<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.
=head1 PL/SQL Examples
Most of these PL/SQL examples come from: Eric Bartley <bartley@cc.purdue.edu>.
/*
* 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
case 21: return "BINARY FLOAT os-endian";
case 22: return "BINARY DOUBLE os-endian";
case 23: return "RAW";
case 24: return "LONG RAW";
case 96: return "CHAR";
case 97: return "CHARZ";
case 100: return "BINARY FLOAT oracle-endian";
case 101: return "BINARY DOUBLE oracle-endian";
case 104: return "ROWID";
case 106: return "MLSLABEL";
case 102: return "SQLT_CUR OCI 7 cursor variable";
case 112: return "SQLT_CLOB / long";
case 113: return "SQLT_BLOB / long";
case 116: return "SQLT_RSET OCI 8 cursor variable";
case ORA_VARCHAR2_TABLE:return "ORA_VARCHAR2_TABLE";
case ORA_NUMBER_TABLE: return "ORA_NUMBER_TABLE";
case ORA_XMLTYPE: return "ORA_XMLTYPE or SQLT_NTY";/* SQLT_NTY must be careful here as its value (108) is the same for an embedded object Well really only XML clobs not embedded objects */
}
sv = sv_2mortal(newSVpv("",0));
sv_grow(sv, 50);
sprintf(SvPVX(sv),"(UNKNOWN SQL TYPECODE %d)", dbtype);
return SvPVX(sv);
}
case OCI_ATTR_LDAP_CTX: return "OCI_ATTR_LDAP_CTX"; /* LDAP administration context DN */
case OCI_ATTR_SERVER_DNS: return "OCI_ATTR_SERVER_DNS"; /* list of registration server DNs */
case OCI_ATTR_DN_COUNT: return "OCI_ATTR_DN_COUNT"; /* the number of server DNs */
case OCI_ATTR_SERVER_DN: return "OCI_ATTR_SERVER_DN"; /* server DN attribute */
case OCI_ATTR_MAXCHAR_SIZE: return "OCI_ATTR_MAXCHAR_SIZE"; /* max char size of data */
case OCI_ATTR_CURRENT_POSITION: return "OCI_ATTR_CURRENT_POSITION"; /* for scrollable result sets*/
/* Added to get attributes for ref cursor to statement handle */
case OCI_ATTR_RESERVED_3: return "OCI_ATTR_RESERVED_3"; /* reserved */
case OCI_ATTR_RESERVED_4: return "OCI_ATTR_RESERVED_4"; /* reserved */
case OCI_ATTR_DIRPATH_FN_CTX: return ""; /* fn ctx ADT attrs or args */
case OCI_ATTR_DIGEST_ALGO: return "OCI_ATTR_DIRPATH_FN_CTX"; /* digest algorithm */
case OCI_ATTR_CERTIFICATE: return "OCI_ATTR_CERTIFICATE"; /* certificate */
case OCI_ATTR_SIGNATURE_ALGO: return "OCI_ATTR_SIGNATURE_ALGO"; /* signature algorithm */
case OCI_ATTR_CANONICAL_ALGO: return "OCI_ATTR_CANONICAL_ALGO"; /* canonicalization algo. */
case OCI_ATTR_PRIVATE_KEY: return "OCI_ATTR_PRIVATE_KEY"; /* private key */
case OCI_ATTR_DIGEST_VALUE: return "OCI_ATTR_DIGEST_VALUE"; /* digest value */
case OCI_ATTR_SIGNATURE_VAL: return "OCI_ATTR_SIGNATURE_VAL"; /* signature value */
D_impdata(imp_sth_nested, imp_sth_t, sth_nested);
int fields = DBIc_NUM_FIELDS(imp_sth_nested);
int i;
for(i=0; i < fields; ++i) {
imp_fbh_t *fbh_nested = &imp_sth_nested->fbh[i];
if (fbh_nested->fetch_cleanup)
fbh_nested->fetch_cleanup(sth_nested, fbh_nested);
}
if (DBIc_DBISTATE(imp_sth)->debug >= 3 || dbd_verbose >= 3 )
PerlIO_printf(DBIc_LOGPIO(imp_sth),
" fetch_cleanup_rset - deactivating handle %s (defunct nested cursor)\n",
neatsvpv(sth_nested, 0));
DBIc_ACTIVE_off(imp_sth_nested);
SvREFCNT_dec(sth_nested);
}
}
static int
fetch_func_rset(SV *sth, imp_fbh_t *fbh, SV *dest_sv)
{
dTHR;
D_imp_sth(sth);
D_imp_dbh_from_sth;
dSP;
HV *init_attr = newHV();
int count;
if (DBIc_DBISTATE(imp_sth)->debug >= 3 || dbd_verbose >= 3 )
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" fetch_func_rset - allocating handle for cursor nested within %s ...\n",
neatsvpv(sth, 0));
ENTER; SAVETMPS; PUSHMARK(SP);
XPUSHs(sv_2mortal(newRV((SV*)DBIc_MY_H(imp_dbh))));
XPUSHs(sv_2mortal(newRV((SV*)init_attr)));
PUTBACK;
count = perl_call_pv("DBI::_new_sth", G_ARRAY);
SPAGAIN;
if (count != 2)
croak("panic: DBI::_new_sth returned %d values instead of 2", count);
if(POPs){} /* For GCC not to warn on unused result */
sv_setsv(dest_sv, POPs);
SvREFCNT_dec(init_attr);
PUTBACK; FREETMPS; LEAVE;
if (DBIc_DBISTATE(imp_sth)->debug >= 3 || dbd_verbose >= 3 )
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" fetch_func_rset - ... allocated %s for nested cursor\n",
neatsvpv(dest_sv, 0));
fbh->special = (void *)newSVsv(dest_sv);
{
D_impdata(imp_sth_nested, imp_sth_t, dest_sv);
imp_sth_nested->envhp = imp_sth->envhp;
imp_sth_nested->errhp = imp_sth->errhp;
imp_sth_nested->srvhp = imp_sth->srvhp;
imp_sth_nested->svchp = imp_sth->svchp;
imp_sth_nested->stmhp = stmhp_nested;
imp_sth_nested->nested_cursor = 1;
imp_sth_nested->stmt_type = OCI_STMT_SELECT;
DBIc_IMPSET_on(imp_sth_nested);
DBIc_ACTIVE_on(imp_sth_nested); /* So describe won't do an execute */
if (!dbd_describe(dest_sv, imp_sth_nested))
return 0;
}
return 1;
dbd_rebind_ph_rset(SV *sth, imp_sth_t *imp_sth, phs_t *phs)
{
dTHX;
if (DBIc_DBISTATE(imp_sth)->debug >= 6 || dbd_verbose >= 6 )
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" dbd_rebind_ph_rset phs->is_inout=%d\n",
phs->is_inout);
/* Only do this part for inout cursor refs because pp_exec_rset only gets called for all the output params */
if (phs->is_inout) {
phs->out_prepost_exec = pp_exec_rset;
return 2; /* OCI bind done */
}
else {
/* Call a special rebinder for cursor ref "in" params */
return(pp_rebind_ph_rset_in(sth, imp_sth, phs));
}
}
/* ------ */
static int
fetch_lob(SV *sth, imp_sth_t *imp_sth, OCILobLocator* lobloc, int ftype, SV *dest_sv, char *name);
static int
imp_sth->errhp, status);
if (status != OCI_SUCCESS) {
oci_error(h, imp_sth->errhp, status, "OCIAttrSet OCI_ATTR_PREFETCH_ROWS");
++num_errors;
}
imp_sth->rs_array_size=cache_rows;
if (max_cache_rows){/* limited to 1 by a cursor or something else*/
imp_sth->rs_array_size=1;
}
if (imp_sth->row_cache_off){/*set the size of the Rows in Cache value*/
imp_dbh->RowsInCache =1;
imp_sth->RowsInCache =1;
}
else {
imp_dbh->RowsInCache=imp_sth->rs_array_size;
dbd_describe(SV *h, imp_sth_t *imp_sth)
{
dTHX;
D_imp_dbh_from_sth;
D_imp_drh_from_dbh;
UV long_readlen;
ub4 num_fields;
int num_errors = 0;
int has_longs = 0;
int est_width = 0; /* estimated avg row width (for cache) */
int nested_cursors = 0;
ub4 i = 0;
sword status;
if (imp_sth->done_desc)
return 1; /* success, already done it */
imp_sth->done_desc = 1;
/* ora_trunc is checked at fetch time */
case 111: /* REF */
fbh_setup_getrefpv(imp_sth, fbh, OCI_DTYPE_REF, "OCIRefPtr");
break;
#endif
case ORA_RSET: /* RSET */
fbh->ftype = fbh->dbtype;
fbh->disize = sizeof(OCIStmt *);
fbh->fetch_func = fetch_func_rset;
fbh->fetch_cleanup = fetch_cleanup_rset;
nested_cursors++;
break;
case 182: /* INTERVAL YEAR TO MONTH */
case 183: /* INTERVAL DAY TO SECOND */
case 185: /* TIME (ocidfn.h) */
case 186: /* TIME WITH TIME ZONE (ocidfn.h) */
case 187: /* TIMESTAMP */
case 188: /* TIMESTAMP WITH TIME ZONE */
case 189: /* INTERVAL YEAR TO MONTH (ocidfn.h) */
case 190: /* INTERVAL DAY TO SECOND */
est_width += avg_width;
if (DBIc_DBISTATE(imp_sth)->debug >= 2 || dbd_verbose >= 3 )
dbd_fbh_dump(imp_sth, fbh, (int)i, 0);
}/* end define of filed struct[i] fbh*/
imp_sth->est_width = est_width;
sth_set_row_cache(h, imp_sth,
(imp_dbh->max_nested_cursors) ? 0 :nested_cursors ,
(int)num_fields, has_longs );
/* Initialise cache counters */
imp_sth->in_cache = 0;
imp_sth->eod_errno = 0;
/*rs_array_init(imp_sth);*/
/* now set up the oci call with define by pos*/
for(i=1; i <= num_fields; ++i) {
int ChopBlanks;
int err;
int i;
AV *av;
/* Check that execute() was executed sucessfully. This also implies */
/* that dbd_describe() executed sucessfuly so the memory buffers */
/* are allocated and bound. */
if ( !DBIc_ACTIVE(imp_sth) ) {
oci_error(sth, NULL, OCI_ERROR, imp_sth->nested_cursor ?
"nested cursor is defunct (parent row is no longer current)" :
"no statement executing (perhaps you need to call execute first)");
return Nullav;
}
for(i=0; i < num_fields; ++i) {
imp_fbh_t *fbh = &imp_sth->fbh[i];
if (fbh->fetch_cleanup)
fbh->fetch_cleanup(sth, fbh);
}
}
else {
if (DBIc_DBISTATE(imp_sth)->debug >= 3 || dbd_verbose >= 3 ){
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" dbd_st_fetch %d fields...\n", DBIc_NUM_FIELDS(imp_sth));
}
if (imp_sth->fetch_orient != OCI_DEFAULT) {
if (imp_sth->exe_mode!=OCI_STMT_SCROLLABLE_READONLY)
croak ("attempt to use a scrollable cursor without first setting ora_exe_mode to OCI_STMT_SCROLLABLE_READONLY\n") ;
if (DBIc_DBISTATE(imp_sth)->debug >= 4 || dbd_verbose >= 4 )
PerlIO_printf(
DBIc_LOGPIO(imp_sth),
" Scrolling Fetch, position before fetch=%d, "
"Orientation = %s , Fetchoffset =%d\n",
imp_sth->fetch_position, oci_fetch_options(imp_sth->fetch_orient),
imp_sth->fetch_offset);
OCIStmtFetch_log_stat(imp_sth, imp_sth->stmhp, imp_sth->errhp,1, imp_sth->fetch_orient,imp_sth->fetch_offset, status);
t/25plsql.t view on Meta::CPAN
@ary = scalar $dbh->func('dbms_output_get'); # scalar context
ok( @ary == 1 && $ary[0] && $ary[0] eq 'bar' x 15, 'dbms_output_get bar' );
@ary = $dbh->func('dbms_output_get'); # list context
is( join( ':', @ary ), 'baz:boo', 'dbms_output_get baz:boo' );
$dbh->{PrintError} = 0;
#$dbh->trace(0);
# --- test cursor variables
if (1) {
my $cur_query = q{
SELECT object_name, owner
FROM all_objects
WHERE object_name LIKE :p1
ORDER BY object_name
};
my $cur1 = 42;
#$dbh->trace(4);
my $parent = $dbh->prepare(
qq{
BEGIN OPEN :cur1 FOR $cur_query; END;
}
);
ok( $parent, 'prepare cursor' );
ok( $parent->bind_param( ':p1', 'V%' ), 'bind p1' );
ok(
$parent->bind_param_inout(
':cur1', \$cur1, 0, { ora_type => ORA_RSET }
),
'bind cursor'
);
ok( $parent->execute(), 'execute for cursor' );
my @r;
push @r, @tmp while @tmp = $cur1->fetchrow_array;
ok( @r > 0, 'rows: ' . @r );
#$dbh->trace(0); $parent->trace(0);
# compare results with normal execution of query
my $s1 = $dbh->selectall_arrayref( $cur_query, undef, 'V%' );
my @s1 = map { @$_ } @$s1;
is( join( ' ', sort @r ), join( ' ', sort @s1 ), 'ref = sql' );
# --- test re-bind and re-execute of same 'parent' statement
my $cur1_str = "$cur1";
#$dbh->trace(4); $parent->trace(4);
ok( $parent->bind_param( ':p1', 'U%' ), 'bind p1' );
ok( $parent->execute(), 'execute for cursor' );
# must be ref to new handle object
isnt( "$cur1", $cur1_str, 'expected ref to new handle' );
@r = ();
push @r, @tmp while @tmp = $cur1->fetchrow_array;
#$dbh->trace(0); $parent->trace(0); $cur1->trace(0);
my $s2 = $dbh->selectall_arrayref( $cur_query, undef, 'U%' );
my @s2 = map { @$_ } @$s2;
is( "@r", "@s2", 'ref = sql' );
t/31lob_extended.t view on Meta::CPAN
#
{
my $testname = 'ora_auto_lobs prefetch';
my ( $sth1, $ev );
eval { $sth1 = $dbh->prepare( $PLSQL, { ora_auto_lob => 0 } ); };
ok( !$@, "$testname - prepare call proc" );
my $sth2;
ok( $sth1->bind_param_inout( 1, \$sth2, 500, { ora_type => ORA_RSET } ),
"$testname - bind out cursor" );
ok( $sth1->execute, "$testname - execute to get out cursor" );
my ($lobl);
($lobl) = $sth2->fetchrow;
test_lob( $dbh, $lobl, $testname, 6000, $data0 );
($lobl) = $sth2->fetchrow;
test_lob( $dbh, $lobl, $testname, 6000, $data1 );
ok( $sth2->finish, "$testname - finished returned sth" );
ok( $sth1->finish, "$testname - finished sth" );
}
#
# prior to DBD::Oracle 1.22 if ora_auto_lob was set on a statement which
# was used to return a cursor on a result-set containing lobs, the lobs
# were not automatically fetched.
#
{
my $testname = 'ora_auto_lobs not fetching';
my ( $sth1, $ev, $lob );
# ora_auto_lobs is supposed to default to set
eval { $sth1 = $dbh->prepare($PLSQL); };
ok( !$@, "$testname prepare call proc" );
my $sth2;
ok( $sth1->bind_param_inout( 1, \$sth2, 500, { ora_type => ORA_RSET } ),
"$testname - bind out cursor" );
ok( $sth1->execute, "$testname - execute to get out cursor" );
($lob) = $sth2->fetchrow;
ok( $lob, "$testname - fetch returns something" );
isnt( ref $lob, 'OCILobLocatorPtr', "$testname - not a lob locator" );
is( $lob, $data0, "$testname, first lob matches" );
($lob) = $sth2->fetchrow;
ok( $lob, "$testname - fetch returns something" );
isnt( ref $lob, 'OCILobLocatorPtr', "$testname - not a lob locator" );
is( $lob, $data1, "$testname, second lob matches" );
t/50cursor.t view on Meta::CPAN
if ( $dbh->err
&& ( $dbh->err == 900 || $dbh->err == 6553 || $dbh->err == 600 ) )
{
warn 'Your Oracle server doesn\'t support PL/SQL' if $dbh->err == 900;
warn 'Your Oracle PL/SQL is not properly installed'
if $dbh->err == 6553 || $dbh->err == 600;
plan skip_all => 'Server does not support pl/sql or not installed';
}
$limit = $dbh->selectrow_array(
q{SELECT value-2 FROM v$parameter WHERE name = 'open_cursors'});
# allow for our open and close cursor 'cursors'
$limit -= 2 if $limit && $limit >= 2;
unless ( defined $limit ) { # v$parameter open_cursors could be 0 :)
warn(
"Can't determine open_cursors from v\$parameter, so using default\n"
);
$limit = 1;
}
$limit = 100 if $limit > 100; # lets not be greedy or upset DBA's
$tests = 2 + 10 * $limit + 6;
plan tests => $tests;
note "Max cursors: $limit";
}
else {
plan skip_all => 'Unable to connect to Oracle';
}
my @cursors;
my @row;
note("opening cursors\n");
my $open_cursor = $dbh->prepare(qq{
BEGIN OPEN :kursor FOR
SELECT * FROM all_objects WHERE rownum < 5;
END;
}
);
ok( $open_cursor, 'open cursor' );
foreach ( 1 .. $limit ) {
note("opening cursor $_\n");
ok(
$open_cursor->bind_param_inout(
':kursor', \my $cursor, 0, { ora_type => ORA_RSET }
),
'open cursor bind param inout'
);
ok( $open_cursor->execute, 'open cursor execute' );
ok( !$open_cursor->{Active}, 'open cursor Active' );
ok( $cursor->{Active}, 'cursor Active' );
ok( $cursor->fetchrow_arrayref, 'cursor fetcharray' );
ok( $cursor->fetchrow_arrayref, 'cursor fetcharray' );
ok( $cursor->finish, 'cursor finish' ); # finish early
ok( !$cursor->{Active}, 'cursor not Active' );
push @cursors, $cursor;
}
note("closing cursors\n");
my $close_cursor = $dbh->prepare(qq{ BEGIN CLOSE :kursor; END; });
ok( $close_cursor, 'close cursor' );
foreach ( 1 .. @cursors ) {
print "closing cursor $_\n";
my $cursor = $cursors[ $_ - 1 ];
ok(
$close_cursor->bind_param(
':kursor', $cursor, { ora_type => ORA_RSET }
),
'close cursor bind param'
);
ok( $close_cursor->execute, 'close cursor execute' );
}
my $PLSQL = <<'PLSQL';
DECLARE
TYPE t IS REF CURSOR;
c t;
BEGIN
? := c;
END;
PLSQL
ok( my $sth1 = $dbh->prepare($PLSQL), 'prepare exec of proc for null cursor' );
ok( $sth1->bind_param_inout( 1, \my $cursor, 100, { ora_type => ORA_RSET } ),
'binding cursor for null cursor' );
ok( $sth1->execute, 'execute for null cursor' );
is( $cursor, undef, 'undef returned for null cursor' );
ok( $sth1->execute, 'execute 2 for null cursor' );
is( $cursor, undef, 'undef 2 returned for null cursor' );
t/51scroll.t view on Meta::CPAN
use DBDOracleTestLib qw/ oracle_test_dsn table drop_table db_handle force_drop_table /;
use Test::More;
use DBD::Oracle qw(:ora_types :ora_fetch_orient :ora_exe_modes);
use DBI;
## ----------------------------------------------------------------------------
## 51scroll.t
## By John Scoles, The Pythian Group
## ----------------------------------------------------------------------------
## Just a few checks to see if one can use a scrolling cursor
## Nothing fancy.
## ----------------------------------------------------------------------------
# create a database handle
my $dbh = eval { db_handle( {
RaiseError => 1,
AutoCommit => 1,
PrintError => 0
})};
if ($dbh) {
plan skip_all => 'Scrollable cursors new in Oracle 9'
if $dbh->func('ora_server_version')->[0] < 9;
plan tests => 37;
}
else {
plan skip_all => 'Unable to connect to Oracle';
}
ok( $dbh->{RowCacheSize} = 10 );
# check that our db handle is good
isa_ok( $dbh, 'DBI::db' );
t/55nested.t view on Meta::CPAN
my $dbh = db_handle( { PrintError => 0 } );
if ($dbh) {
plan tests => 29;
}
else {
plan skip_all => 'Unable to connect to Oracle';
}
# ref cursors may be slow due to oracle bug 3735785
# believed fixed in
# 9.2.0.6 (Server Patch Set)
# 10.1.0.4 (Server Patch Set)
# 10.2.0.1 (Base Release)
{
my $outer = $dbh->prepare(
q{
SELECT object_name, CURSOR(SELECT object_name FROM dual)
t/55nested.t view on Meta::CPAN
ok( $inner1->{Active}, 'inner Active' );
ok( my @row2 = $outer->fetchrow_array, 'outer fetchrow_array' );
ok( !$inner1->{Active}, 'inner not Active' );
ok( !$inner1->fetch, 'inner fetch finished' );
is( $dbh->err, -1, 'err = -1' );
like( $dbh->errstr, qr/ defunct /, 'defunct' );
ok( $outer->finish, 'outer finish' );
is( $dbh->{ActiveKids}, 0, 'ActiveKids' );
#########################################################################
# Same test again but this time with 2 cursors
#########################################################################
$outer = $dbh->prepare(
q{
SELECT object_name,
CURSOR(SELECT object_name FROM dual),
CURSOR(SELECT object_name FROM dual)
FROM all_objects WHERE rownum <= 5}
);
ok( $outer, 'prepare select' );
t/55nested.t view on Meta::CPAN
my $sql1 = q{
SELECT object_name
FROM (SELECT object_name FROM all_objects WHERE ROWNUM<=70),
(SELECT 1 FROM all_objects WHERE ROWNUM<=70)
};
$outer = $dbh->prepare($sql1);
$outer->execute();
my $dur_std = timed_fetch( $outer, 'select' );
##################################################
# nested cursor
##################################################
$outer = $dbh->prepare("SELECT CURSOR($sql1) FROM DUAL");
$outer->execute();
my $ref_csr = $outer->fetchrow_arrayref->[0];
my $dur_ref = timed_fetch( $ref_csr, 'nested cursor' );
#########################################################################
# Fetch speed test: END
#########################################################################
}
( run in 0.593 second using v1.01-cache-2.11-cpan-4d50c553e7e )