[continued from previous message]

-stick to one style within your program.
-
-## **prepare\_cached**
-
- $sth = $dbh->prepare_cached($statement, \%attr);
-
-Implemented by DBI, no driver-specific impact. This method is most useful
-if the same query is used over and over as it will cut down round trips to the server.
-
-## **do**
-
- $rv = $dbh->do($statement);
- $rv = $dbh->do($statement, \%attr);
- $rv = $dbh->do($statement, \%attr, @bind_values);
-
-Prepare and execute a single statement. Returns the number of rows affected if the
-query was successful, returns undef if an error occurred, and returns -1 if the
-number of rows is unknown or not available. Note that this method will return **0E0** instead
-of 0 for 'no rows were affected', in order to always return a true value if no error occurred.
-
-## **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 you to use the latter 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;
-
-Issues a COMMIT to the server, indicating that the current transaction is finished and that
-all changes made will be visible to other processes. If AutoCommit is enabled, then
-a warning is given and no COMMIT is issued. Returns true on success, false on error.
-
-## **rollback**
-
- $rv = $dbh->rollback;
-
-Issues a ROLLBACK to the server, which discards any changes made in the current transaction. If AutoCommit
-is enabled, then a warning is given and no ROLLBACK is issued. Returns true on success, and
-false on error.
-
-## **begin\_work**
-
-This method turns on transactions until the next call to ["commit"](#commit) or ["rollback"](#rollback), if ["AutoCommit"](#autocommit) is
-currently enabled. If it is not enabled, calling begin\_work will issue an error. Note that the
-transaction will not actually begin until the first statement after begin\_work is called.
-
-## **disconnect**
-
- $rv = $dbh->disconnect;
-
-Disconnects from the Oracle database. Any uncommitted changes will be rolled back upon disconnection. It's
-good policy to always explicitly call commit or rollback at some point before disconnecting, rather than
-relying on 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 will call the rollback() and disconnect()
-methods automatically. It is best to explicitly disconnect rather than rely on this behavior.
-
-## **ping**
-
- $rv = $dbh->ping;
-
-This `ping` method is used to check the validity of a database handle. The value returned is
-either 0, indicating that the connection is no longer valid, or 1, indicating the connection is valid.
-This function does 1 round trip to the Oracle Server.
-
-## **get\_info()**
-
- $value = $dbh->get_info($info_type);
-
-DBD::Oracle supports `get_info()`, but (currently) only a few info types.
-
-## **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 an user, the owner names
-in the data dictionary views correspond to schema names.
-Oracle does not support catalogues so TABLE\_CAT is ignored as
-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
- SEQUENCE
-
-The 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 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 are created
-with quoted identifiers (for reserved words, mixed case, special
-characters, ...). Such an identifier is case-sensitive (if not all
-upper case). Oracle stores and returns it as given.
-`table_info()` has no special quote handling, neither adds nor
-removes quotes.
-
-## **primary\_key\_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()"](#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 or
-Oracle returns it.
-See ["table\_info()"](#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.
-
-Oracle does not support catalogues, so `$pk_catalog` and `$fk_catalog` are -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()"](#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'.
-
-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()"](#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()"](#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! The DATA\_DEFAULT (COLUMN\_DEF) column is of type LONG so you
-may have to set LongReadLen on the connection handle before calling -column\_info if you have a large default column. After DBD::Oracle 1.40 -LongReadLen is set automatically to 1Mb when calling column\_info and
-reset aftwerwards.
-
-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()"](#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.
-
-## **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()"](#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
- ... other
-
-The `$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()"](#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"](#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"](#selectrow_array), except that it returns a reference to an array, by internal use of
-the ["fetchrow\_arrayref"](#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"](#selectrow_array), except that it returns a reference to an hash, by internal use of
-the ["fetchrow\_hashref"](#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"](#connect).
-
-## **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.
-
-### **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.
-
-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);
-
-This will import some constants into your script. You can plug those
-directly into the ["bind\_param"](#bind_param) call. Some common ones that you will
-encounter are:
-
- SQL_INTEGER
-
-To use Oracle SQL data types, import the list of values like this:
-
- use DBD::Oracle qw(:ora_types);
-
-You can then set the data types by setting the value of the `ora_type`
-key in the hash passed to ["bind\_param"](#bind_param).
-The current list of Oracle data types 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_BIN
-
-Data types are "sticky," in that once a data type is set to a certain placeholder,
-it will remain for that placeholder, unless it is explicitly set to something -else afterwards. If the statement has already been prepared, and you switch the
-data type to something else, DBD::Oracle will re-prepare the statement for you before
-doing the next execute.
-
-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](https://metacpan.org/pod/DBI#bind_param) or ["bind\_param\_inout" in DBI](https://metacpan.org/pod/DBI#bind_param_inout) statement handle methods.
-
-- ora\_type
-
- Specify the placeholder's datatype using an Oracle datatype.
- A fatal error is raised if `ora_type` and the DBI `TYPE` attribute
- are 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 used.
- 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_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).
-
-- ora\_csform
-
- Specify the OCI\_ATTR\_CHARSET\_FORM for the bind value. Valid values
- are SQLCS\_IMPLICIT (1) and SQLCS\_NCHAR (2). Both those constants can
- be imported from the DBD::Oracle module. Rarely needed.
-
-- ora\_csid
-
- Specify the _integer_ OCI\_ATTR\_CHARSET\_ID for the bind value.
- Character set names can't be used currently.
-
-- ora\_maxdata\_size
-
- Specify the integer OCI\_ATTR\_MAXDATA\_SIZE for the bind value.
- May be needed if a character set conversion from client to server
- causes the data to use more space and so fail with a truncation error.
-
-- ora\_maxarray\_numentries
-
- Specify the maximum number of array entries to allocate. Used with
- ORA\_VARCHAR2\_TABLE, ORA\_NUMBER\_TABLE. Define the maximum number of
- array entries Oracle can pass back to you in OUT variable of type
- TABLE OF ... .
-
-- ora\_internal\_type
-
- Specify internal data representation. Currently is supported only for
- ORA\_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 (such as $sth->{NAME}). This reduces -communication with the server and increases performance (reducing the
-number of PARSE\_CALLS inside the server).
-
-However, it also means that SQL errors are not detected until
-`execute()` (or $sth->{NAME} etc) is called instead of when
-`prepare()` is called. Note that 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!
-
-Set ["ora\_check\_sql"](#ora_check_sql) to 0 in prepare() 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).
-
- $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";
-
-If you have many 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;
-
-If you have many 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 of bind\_param\_inout\_array, (0 in the example), "maxlen" is required by DBI but not used by DBD::Oracle
-- The "ora\_type" attribute is not needed but only ORA\_VARCHAR2 will 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. You cannot therefore call -a PL/SQL function or procedure from DBI that uses a non-atomic datatype as -either a parameter, or a return value. However, if you are 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, its 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 is 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 ;
-
-As you can see, the new function is very simple. Now, it is a simple matter -of calling the function as a straight-forward SELECT from your DBI code. From -the above example, the 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. The simple example is here:
-
- 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 that we use '\\\\@arr' here because the 'bind\_param\_inout'
- will only take a reference to a scalar.
-
-### **ORA\_VARCHAR2\_TABLE**
-
-SYS.DBMS\_SQL.VARCHAR2\_TABLE object is always bound to array reference.
-( in bind\_param() and bind\_param\_inout() ). When you bind array, you need -to specify full buffer size for OUT data. So, there are two parameters: -_max\_len_ (specified as 3rd argument of bind\_param\_inout() ),
-and _ora\_maxarray\_numentries_. They define maximum array entry length and -maximum rows, that can be passed to Oracle and back to you. In this
-example we send array with 1 element with length=3, but allocate space for 100 -Oracle array entries with maximum length 10 of each. So, you can get no more -than 100 array entries with length <= 10.
-
-If you set _max\_len_ to zero, maximum array entry length is calculated
-as maximum length of entry of array bound. If 0 < _max\_len_ < length( $some\_element ),
-truncation occur.
-
-If you set _ora\_maxarray\_numentries_ to zero, current (at bind time) bound -array length is used as maximum. If 0 < _ora\_maxarray\_numentries_ < scalar(@array),
-not all array entries are bound.
-
-### **ORA\_NUMBER\_TABLE**
-
-SYS.DBMS\_SQL.NUMBER\_TABLE object handling is much alike ORA\_VARCHAR2\_TABLE.
-The main difference is internal data representation. Currently 2 types of -bind is allowed : as C-integer, or as C-double type. To select one of them, -you may specify additional bind parameter _ora\_internal\_type_ as either -**SQLT\_INT** or **SQLT\_FLT** for C-integer and C-double types.
-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 full native Oracle NUMBER(38). If you really need, -send request to 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" );
-

[continued in next message]

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)