[continued from previous message]

-Both UTF8 and AL32UTF8 can be used in NLS\_LANG and NLS\_NCHAR.
-For example:
-
- NLS_LANG=AMERICAN_AMERICA.UTF8
- NLS_LANG=AMERICAN_AMERICA.AL32UTF8
- NLS_NCHAR=UTF8
- NLS_NCHAR=AL32UTF8
-
-## Oracle UTF8 is not UTF-8
-
-AL32UTF8 should be used in preference to UTF8 if it works for you,
-which it should for Oracle 9.2 or later. If you're using an old
-version of Oracle that doesn't support AL32UTF8 then you should
-avoid using any Unicode characters that require surrogates, in other
-words characters beyond the Unicode BMP (Basic Multilingual Plane).
-
-That's because the character set that Oracle calls "UTF8" doesn't
-conform to the UTF-8 standard in its handling of surrogate characters. -Technically the encoding that Oracle calls "UTF8" is known as "CESU-8".
-Here are a couple of extracts from [http://www.unicode.org/reports/tr26/](http://www.unicode.org/reports/tr26/):
-
- CESU-8 is useful in 8-bit processing environments where binary
- collation with UTF-16 is required. It is designed and recommended
- for use only within products requiring this UTF-16 binary collation
- equivalence. It is not intended nor recommended for open interchange.
-
- As a very small percentage of characters in a typical data stream
- are expected to be supplementary characters, there is a strong
- possibility that CESU-8 data may be misinterpreted as UTF-8.
- Therefore, all use of CESU-8 outside closed implementations is
- strongly discouraged, such as the emittance of CESU-8 in output
- files, markup language or other open transmission forms.
-
-Oracle uses this internally because it collates (sorts) in the same order
-as UTF16, which is the basis of Oracle's internal collation definitions.
-
-Rather than change UTF8 for clients Oracle chose to define a new character -set called "AL32UTF8" which does conform to the UTF-8 standard.
-(The AL32UTF8 character set can't be used on the server because it
-would break collation.)
-
-Because of that, for the rest of this document we'll use "AL32UTF8".
-If you're using an Oracle version below 9.2 you'll need to use "UTF8"
-until you upgrade.
-
-## DBD::Oracle and Unicode
-
-DBD::Oracle Unicode support has been implemented for Oracle versions 9
-or greater, and Perl version 5.6 or greater (though we _strongly_
-suggest that you use Perl 5.8.2 or later).
-
-You can check which Oracle version your DBD::Oracle was built with by -importing the `ORA_OCI` constant from DBD::Oracle.
-
-**Fetching Data**
-
-Any data returned from Oracle to DBD::Oracle in the AL32UTF8
-character set will be marked as UTF-8 to ensure correct handling by Perl.
-
-For Oracle to return data in the AL32UTF8 character set the
-NLS\_LANG or NLS\_NCHAR environment variable _must_ be set as described
-in the previous section.
-
-When fetching NCHAR, NVARCHAR, or NCLOB data from Oracle, DBD::Oracle
-will set the Perl UTF-8 flag on the returned data if either NLS\_NCHAR
-is AL32UTF8, or NLS\_NCHAR is not set and NLS\_LANG is AL32UTF8.
-
-When fetching other character data from Oracle, DBD::Oracle
-will set the Perl UTF-8 flag on the returned data if NLS\_LANG is AL32UTF8.
-
-**Sending Data using Placeholders**
-
-Data bound to a placeholder is assumed to be in the default client
-character set (specified by NLS\_LANG) except for a few special
-cases. These are listed here with the highest precedence first:
-
-If the `ora_csid` attribute is given to bind\_param() then that
-is passed to Oracle and takes precedence.
-
-If the value is a Perl Unicode string (UTF-8) then DBD::Oracle
-ensures that Oracle uses the Unicode character set, regardless of
-the NLS\_LANG and NLS\_NCHAR settings.
-
-If the placeholder is for inserting an NCLOB then the client NLS\_NCHAR -character set is used. (That's useful but inconsistent with the other behaviour
-so may change. Best to be explicit by using the `ora_csform`
-attribute.)
-
-If the `ora_csform` attribute is given to bind\_param() then that
-determines if the value should be assumed to be in the default
-(NLS\_LANG) or NCHAR (NLS\_NCHAR) client character set.
-
- use DBD::Oracle qw( SQLCS_IMPLICIT SQLCS_NCHAR );
- ...
- $sth->bind_param(1, $value, { ora_csform => SQLCS_NCHAR });
-
-or
-
- $dbh->{ora_ph_csform} = SQLCS_NCHAR; # default for all future placeholders -
-Binding with bind\_param\_array and execute\_array is also UTF-8 compatible in the same way. If you attempt to
-insert UTF-8 data into a non UTF-8 Oracle instance or with an non UTF-8 NCHAR or NVARCHAR the insert
-will still happen but a error code of 0 will be returned with the following warning;
-
- DBD Oracle Warning: You have mixed utf8 and non-utf8 in an array bind in parameter#1. This may result in corrupt data.
- The Query charset id=1, name=US7ASCII
-
-The warning will report the parameter number and the NCHAR setting that the query is running.
-
-**Sending Data using SQL**
-
-Oracle assumes the SQL statement is in the default client character
-set (as specified by NLS\_LANG). So Unicode strings containing
-non-ASCII characters should not be used unless the default client
-character set is AL32UTF8.
-
-## DBD::Oracle and Other Character Sets and Encodings
-
-The only multi-byte Oracle character set supported by DBD::Oracle is -"AL32UTF8" (and "UTF8"). Single-byte character sets should work well.
-
-# OBJECT & COLLECTION DATA TYPES
-
-Oracle databases allow for the creation of object oriented like user-defined types.
-There are two types of objects, Embedded--an object stored in a column of a regular table
-and REF--an object that uses the REF retrieval mechanism.
-
-DBD::Oracle supports only the 'selection' of embedded objects of the following types OBJECT, VARRAY
-and TABLE in any combination. Support is seamless and recursive, meaning you -need only supply a simple SQL statement to get all the values in an embedded object.
-You can either get the values as an array of scalars or they can be returned into a DBD::Oracle::Object.
-
-Array example, given this type and table;
-
- CREATE OR REPLACE TYPE "PHONE_NUMBERS" as varray(10) of varchar(30);
-
- CREATE TABLE "CONTACT"
- ( "COMPANYNAME" VARCHAR2(40),
- "ADDRESS" VARCHAR2(100),
- "PHONE_NUMBERS" "PHONE_NUMBERS"
- )
-
-The code to access all the data in the table could be something like this;
-
- my $sth = $dbh->prepare('SELECT * FROM CONTACT');
- $sth->execute;
- while ( my ($company, $address, $phone) = $sth->fetchrow()) {
- print "Company: ".$company."\n";
- print "Address: ".$address."\n";
- print "Phone #: ";
-
- foreach my $items (@$phone){
- print $items.", ";
- }
- print "\n";
- }
-
-Note that values in PHONE\_NUMBERS are returned as an array reference '@$phone'.
-
-As stated before DBD::Oracle will automatically drill into the embedded object and extract
-all of the data as reference arrays of scalars. The example below has OBJECT type embedded in a TABLE type embedded in an
-SQL TABLE;
-
- CREATE OR REPLACE TYPE GRADELIST AS TABLE OF NUMBER;
-
- CREATE OR REPLACE TYPE STUDENT AS OBJECT(
- NAME VARCHAR2(60),
- SOME_GRADES GRADELIST);
-
- CREATE OR REPLACE TYPE STUDENTS_T AS TABLE OF STUDENT;
-
- CREATE TABLE GROUPS(
- GRP_ID NUMBER(4),
- GRP_NAME VARCHAR2(10),
- STUDENTS STUDENTS_T)
- NESTED TABLE STUDENTS STORE AS GROUP_STUDENTS_TAB
- (NESTED TABLE SOME_GRADES STORE AS GROUP_STUDENT_GRADES_TAB);
-
-The following code will access all of the embedded data;
-
- $SQL='select grp_id,grp_name,students as my_students_test from groups';
- $sth=$dbh->prepare($SQL);
- $sth->execute();
- while (my ($grp_id,$grp_name,$students)=$sth->fetchrow()){
- print "Group ID#".$grp_id." Group Name =".$grp_name."\n";
- foreach my $student (@$students){
- print "Name:".$student->[0]."\n";
- print "Marks:";
- foreach my $grades (@$student->[1]){
- foreach my $marks (@$grades){
- print $marks.",";
- }
- }
- print "\n";
- }
- print "\n";
- }
-
-Object example, given this object and table;
-
- CREATE OR REPLACE TYPE Person AS OBJECT (
- name VARCHAR2(20),
- age INTEGER)
- ) NOT FINAL;
-
- CREATE TYPE Employee UNDER Person (
- salary NUMERIC(8,2)
- );
-
- CREATE TABLE people (id INTEGER, obj Person);
-
- INSERT INTO people VALUES (1, Person('Black', 25));
- INSERT INTO people VALUES (2, Employee('Smith', 44, 5000));
-
-The following code will access the data;
-
- $dbh{'ora_objects'} =>1;
-
- $sth = $dbh->prepare("select * from people order by id");
- $sth->execute();
-
- # object are fetched as instance of DBD::Oracle::Object
- my ($id1, $obj1) = $sth->fetchrow();
- my ($id2, $obj2) = $sth->fetchrow();
-
- # get full type-name of object
- print $obj1->type_name."44\n"; # 'TEST.PERSON' is printed
- print $obj2->type_name."4\n"; # 'TEST.EMPLOYEE' is printed
-
- # get attribute NAME from object
- print $obj1->attr('NAME')."3\n"; # 'Black' is printed
- print $obj2->attr('NAME')."3\n"; # 'Smith' is printed
-
- # get all attributes as hash reference
- my $h1 = $obj1->attr; # returns {'NAME' => 'Black', 'AGE' => 25}
- my $h2 = $obj2->attr; # returns {'NAME' => 'Smith', 'AGE' => 44,
- # 'SALARY' => 5000 }
-
- # get all attributes (names and values) as array
- my @a1 = $obj1->attributes; # returns ('NAME', 'Black', 'AGE', 25)
- my @a2 = $obj2->attributes; # returns ('NAME', 'Smith', 'AGE', 44,
- # 'SALARY', 5000 )
-
-So far DBD::Oracle has been tested on a table with 20 embedded Objects, Varrays and Tables
-nested to 10 levels.
-
-Any NULL values found in the embedded object will be returned as 'undef'.
-
-# OTHER DATA TYPES
-
-DBD::Oracle does not _explicitly_ support most Oracle datatypes.
-It simply asks Oracle to return them as strings and Oracle does so.
-Mostly. Similarly when binding placeholder values DBD::Oracle binds
-them as strings and Oracle converts them to the appropriate type,
-such as DATE, when used.
-
-Some of these automatic conversions to and from strings use NLS
-settings to control the formatting for output and the parsing for
-input. The most common example is the DATE type. The default NLS
-format for DATE might be DD-MON-YYYY and so when a DATE type is
-fetched that's how Oracle will format the date. NLS settings also
-control the default parsing of strings into DATE values. An error
-will be generated if the contents of the string don't match the
-NLS format. If you're dealing in dates which don't match the default
-NLS format then you can either change the default NLS format or, more -commonly, use TO\_CHAR(field, "format") and TO\_DATE(?, "format")
-to explicitly specify formats for converting to and from strings.
-
-A slightly more subtle problem can occur with NUMBER types. The
-default NLS settings might format numbers with a fullstop ("`.`")
-to separate thousands and a comma ("`,`") as the decimal point.
-Perl will generate warnings and use incorrect values when numbers,
-returned and formatted as strings in this way by Oracle, are used
-in a numeric context. You could explicitly convert each numeric
-value using the TO\_CHAR(...) function but that gets tedious very
-quickly. The best fix is to change the NLS settings. That can be
-done for an individual connection by doing:
-
- $dbh->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");
-
-There are some types, like BOOLEAN, that Oracle does not automatically -convert to or from strings (pity). These need to be converted
-explicitly using SQL or PL/SQL functions.
-
-Examples:
-
- # DATE values
- my $sth0 = $dbh->prepare( <<SQL_END );
- SELECT username, TO_CHAR( created, ? )
- FROM all_users
- WHERE created >= TO_DATE( ?, ? )
- SQL_END
- $sth0->execute( 'YYYY-MM-DD HH24:MI:SS', "2003", 'YYYY' );
-
- # BOOLEAN values
- my $sth2 = $dbh->prepare( <<PLSQL_END );
- DECLARE
- b0 BOOLEAN;
- b1 BOOLEAN;
- o0 VARCHAR2(32);
- o1 VARCHAR2(32);
-
- FUNCTION to_bool( i VARCHAR2 ) RETURN BOOLEAN IS
- BEGIN
- IF i IS NULL THEN RETURN NULL;
- ELSIF i = 'F' OR i = '0' THEN RETURN FALSE;
- ELSE RETURN TRUE;
- END IF;
- END;
- FUNCTION from_bool( i BOOLEAN ) RETURN NUMBER IS
- BEGIN
- IF i IS NULL THEN RETURN NULL;
- ELSIF i THEN RETURN 1;
- ELSE RETURN 0;
- END IF;
- END;
- BEGIN
- -- Converting values to BOOLEAN
- b0 := to_bool( :i0 );
- b1 := to_bool( :i1 );
-
- -- Converting values from BOOLEAN
- :o0 := from_bool( b0 );
- :o1 := from_bool( b1 );
- END;
- PLSQL_END
- my ( $i0, $i1, $o0, $o1 ) = ( "", "Something else" );
- $sth2->bind_param( ":i0", $i0 );
- $sth2->bind_param( ":i1", $i1 );
- $sth2->bind_param_inout( ":o0", \$o0, 32 );
- $sth2->bind_param_inout( ":o1", \$o1, 32 );
- $sth2->execute();
- foreach ( $i0, $b0, $o0, $i1, $b1, $o1 ) {
- $_ = "(undef)" if ! defined $_;
- }
- print "$i0 to $o0, $i1 to $o1\n";
- # Result is : "'' to '(undef)', 'Something else' to '1'"
-
-## Support for Insert of XMLType (ORA\_XMLTYPE)
-
-Inserting large XML data sets into tables with XMLType fields is now supported by DBD::Oracle. The only special
-requirement is the use of bind\_param() with an attribute hash parameter that specifies ora\_type as ORA\_XMLTYPE. For
-example with a table like this;
-
- create table books (book_id number, book_xml XMLType);
-
-one can insert data using this code
-
- $SQL='insert into books values (1,:p_xml)';
- $xml= '<Books>
- <Book id=1>
- <Title>Programming the Perl DBI</Title>
- <Subtitle>The Cheetah Book</Subtitle>
- <Authors>
- <Author>T. Bunce</Author>
- <Author>Alligator Descartes</Author>
- </Authors>
-
- </Book>
- <Book id=10000>...
- </Books>';
- my $sth =$dbh-> prepare($SQL);
- $sth-> bind_param("p_xml", $xml, { ora_type => ORA_XMLTYPE });
- $sth-> execute();
-
-In the above case we will assume that $xml has 10000 Book nodes and is over 32k in size and is well formed XML.
-This will also work for XML that is smaller than 32k as well. Attempting to insert malformed XML will cause an error.
-
-## Binding Cursors
-
-Cursors can be returned from PL/SQL blocks, either from stored
-functions (or procedures with OUT parameters) or
-from direct `OPEN` statements, as shown below:
-
- use DBI;
- use DBD::Oracle qw(:ora_types);
- my $dbh = DBI->connect(...);
- my $sth1 = $dbh->prepare(q{
- BEGIN OPEN :cursor FOR
- SELECT table_name, tablespace_name
- FROM user_tables WHERE tablespace_name = :space;
- END;
- });
- $sth1->bind_param(":space", "USERS");
- my $sth2;
- $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } ); - $sth1->execute;
- # $sth2 is now a valid DBI statement handle for the cursor
- while ( my @row = $sth2->fetchrow_array ) { ... }
-
-The only special requirement is the use of `bind_param_inout()` with an -attribute hash parameter that specifies `ora_type` as `ORA_RSET`.
-If you don't do that you'll get an error from the `execute()` like: -"ORA-06550: line X, column Y: PLS-00306: wrong number or types of
-arguments in call to ...".
-
-Here's an alternative form using a function that returns a cursor.
-This example uses the pre-defined weak (or generic) REF CURSOR type -SYS\_REFCURSOR. This is an Oracle 9 feature.
-
- # Create the function that returns a cursor
- $dbh->do(q{
- CREATE OR REPLACE FUNCTION sp_ListEmp RETURN SYS_REFCURSOR
- AS l_cursor SYS_REFCURSOR;
- BEGIN
- OPEN l_cursor FOR select ename, empno from emp
- ORDER BY ename;
- RETURN l_cursor;
- END;
- });
-
- # Use the function that returns a cursor
- my $sth1 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
- my $sth2;
- $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } ); - $sth1->execute;
- # $sth2 is now a valid DBI statement handle for the cursor
- while ( my @row = $sth2->fetchrow_array ) { ... }
-
-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
- * package already exists.
- */
-
- CREATE OR REPLACE PACKAGE plsql_example
- IS
- PROCEDURE proc_np;
-
- PROCEDURE proc_in (
- err_code IN NUMBER
- );
-
- PROCEDURE proc_in_inout (
- test_num IN NUMBER,
- is_odd IN OUT NUMBER
- );
-
- FUNCTION func_np
- RETURN VARCHAR2;
-
- END plsql_example;
- /
-
- CREATE OR REPLACE PACKAGE BODY plsql_example
- IS
- PROCEDURE proc_np
- IS
- whoami VARCHAR2(20) := NULL;
- BEGIN
- SELECT USER INTO whoami FROM DUAL;
- END;
-
- PROCEDURE proc_in (
- err_code IN NUMBER
- )
- IS
- BEGIN
- RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
- END;
-
- PROCEDURE proc_in_inout (
- test_num IN NUMBER,
- is_odd IN OUT NUMBER
- )
- IS
- BEGIN
- is_odd := MOD(test_num, 2);
- END;
-
- FUNCTION func_np
- RETURN VARCHAR2
- IS
- ret_val VARCHAR2(20);
- BEGIN
- SELECT USER INTO ret_val FROM DUAL;
- RETURN ret_val;
- END;
-
- END plsql_example;
- /
- /* End PL/SQL for example package creation. */
-
- use DBI;
-
- my($db, $csr, $ret_val);
-
- $db = DBI->connect('dbi:Oracle:database','user','password')
- or die "Unable to connect: $DBI::errstr";
-
- # So we don't have to check every DBI call we set RaiseError.
- # See the DBI docs now if you're not familiar with RaiseError.
- $db->{RaiseError} = 1;
-
- # Example 1 Eric Bartley <bartley@cc.purdue.edu>
- #
- # Calling a PLSQL procedure that takes no parameters. This shows you the
- # basic's of what you need to execute a PLSQL procedure. Just wrap your
- # procedure call in a BEGIN END; block just like you'd do in SQL*Plus.
- #
- # p.s. If you've used SQL*Plus's exec command all it does is wrap the
- # command in a BEGIN END; block for you.
-
- $csr = $db->prepare(q{
- BEGIN
- PLSQL_EXAMPLE.PROC_NP;
- END;
- });
- $csr->execute;
-
-
- # Example 2 Eric Bartley <bartley@cc.purdue.edu>
- #
- # Now we call a procedure that has 1 IN parameter. Here we use bind_param - # to bind out parameter to the prepared statement just like you might
- # do for an INSERT, UPDATE, DELETE, or SELECT statement.
- #
- # I could have used positional placeholders (e.g. :1, :2, etc.) or
- # ODBC style placeholders (e.g. ?), but I prefer Oracle's named
- # placeholders (but few DBI drivers support them so they're not portable). -
- my $err_code = -20001;
-
- $csr = $db->prepare(q{
- BEGIN
- PLSQL_EXAMPLE.PROC_IN(:err_code);
- END;
- });
-
- $csr->bind_param(":err_code", $err_code);
-
- # PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to 'fail'.
- # Because we set RaiseError, the DBI will croak (die) so we catch that with eval.
- eval {
- $csr->execute;
- };
- print 'After proc_in: $@=',"'$@', errstr=$DBI::errstr, ret_val=$ret_val\n";
-
-
- # Example 3 Eric Bartley <bartley@cc.purdue.edu>
- #
- # Building on the last example, I've added 1 IN OUT parameter. We still
- # use a placeholders in the call to prepare, the difference is that
- # we now call bind_param_inout to bind the value to the place holder.
- #
- # Note that the third parameter to bind_param_inout is the maximum size
- # of the variable. You normally make this slightly larger than necessary. - # But note that the Perl variable will have that much memory assigned to
- # it even if the actual value returned is shorter.
-
- my $test_num = 5;
- my $is_odd;
-
- $csr = $db->prepare(q{
- BEGIN
- PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
- END;
- });
-
- # The value of $test_num is _copied_ here
- $csr->bind_param(":test_num", $test_num);
-
- $csr->bind_param_inout(":is_odd", \$is_odd, 1);
-
- # The execute will automagically update the value of $is_odd
- $csr->execute;
-
- print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n";
-
-
- # Example 4 Eric Bartley <bartley@cc.purdue.edu>
- #
- # What about the return value of a PLSQL function? Well treat it the same - # as you would a call to a function from SQL*Plus. We add a placeholder
- # for the return value and bind it with a call to bind_param_inout so
- # we can access its value after execute.
-
- my $whoami = "";
-
- $csr = $db->prepare(q{
- BEGIN
- :whoami := PLSQL_EXAMPLE.FUNC_NP;
- END;
- });
-
- $csr->bind_param_inout(":whoami", \$whoami, 20);
- $csr->execute;
- print "Your database user name is $whoami\n";
-
- $db->disconnect;
-
-You can find more examples in the t/plsql.t file in the DBD::Oracle
-source directory.
-
-Oracle 9.2 appears to have a bug where a variable bound
-with bind\_param\_inout() that isn't assigned to by the executed
-PL/SQL block may contain garbage.
-See [http://www.mail-archive.com/dbi-users@perl.org/msg18835.html](http://www.mail-archive.com/dbi-users@perl.org/msg18835.html)
-
-## Avoid Using "SQL Call"
-
-Avoid using the "SQL Call" statement with DBD:Oracle as you might find that -DBD::Oracle will not raise an exception in some case. Specifically if you use -"SQL Call" to run a procedure all "No data found" exceptions will be quietly -ignored and returned as null. According to Oracle support this is part of the same
-mechanism where;
-
- select (select * from dual where 0=1) from dual
-
-returns a null value rather than an exception.
-
-# CONTRIBUTING
-
-If you'd like DBD::Oracle to do something new or different the best way
-to make that happen is to do it yourself and email to dbi-dev@perl.org a -patch of the source code (using 'diff' - see below) that shows the changes.
-
-## Speak before you patch
-
-For anything non-trivial or possibly controversial it's a good idea
-to discuss (on dbi-dev@perl.org) the changes you propose before
-actually spending time working on them. Otherwise you run the risk
-of them being rejected because they don't fit into some larger plans
-you may not be aware of.
-
-# WHICH VERSION OF DBD::ORACLE IS FOR ME?
-
-From version 1.25 onwards DBD::Oracle only support Oracle clients
-9.2 or greater. Support for ProC connections was dropped in 1.29.
-
-If you are still stuck with an older version of Oracle or its client you might want to look at the table below.
-
- +---------------------+-----------------------------------------------------+
- | | Oracle Version |
- +---------------------+----+-------------+---------+------+--------+--------+
- | DBD::Oracle Version | <8 | 8.0.3~8.0.6 | 8iR1~R2 | 8iR3 | 9i | 9.2~11 |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 0.1~16 | Y | Y | Y | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.17 | Y | Y | Y | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.18 | N | N | N | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.19 | N | N | N | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.20 | N | N | N | Y | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.21~1.24 | N | N | N | N | Y | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
- | 1.25+ | N | N | N | N | N | Y |
- +---------------------+----+-------------+---------+------+--------+--------+
-
-As there are dozens of different versions of Oracle's clients this
-list does not include all of them, just the major released versions of -Oracle.
-
-Note that one can still connect to any Oracle version with the older -DBD::Oracle versions the only problem you will have is that some of
-the newer OCI and Oracle features available in later DBD::Oracle
-releases will not be available to you.
-
-So to make a short story a little longer:
-
-1. If you are using Oracle 7 or early 8 DB and you can manage to get a 9 client and you can use
-any DBD::Oracle version.
-2. If you have to use an Oracle 7 client then DBD::Oracle 1.17 should work
-3. Same thing for 8 up to R2, use 1.17, if you are lucky and have the right patch-set you might
-go with 1.18.
-4. For 8iR3 you can use any of the DBD::Oracle versions up to 1.21. Again this depends on your
-patch-set, If you run into trouble go with 1.19
-5. After 9.2 you can use any version you want.
-6. It seems that the 10g client can only connect to 9 and 11 DBs while the 9 can go back to 7
-and even get to 10. I am not sure what the 11g client can connect to.
-
-# SEE ALSO
-
-- [DBI](https://metacpan.org/pod/DBI)
-
- http://search.cpan.org/~timb/DBD-Oracle/MANIFEST for all files in
- the DBD::Oracle source distribution including the examples in the
- Oracle.ex directory
-
-- DBD::Oracle Tutorial
-
- http://www.pythian.com/blogs/wp-content/uploads/introduction-dbd-oracle.html
-
-- Oracle Instant Client
-
- http://www.oracle.com/technology/tech/oci/instantclient/index.html
-
-- Oracle on Linux
-
- http://www.ixora.com.au/
-
-- Free Oracle Tools and Links
-
- ora\_explain supplied and installed with DBD::Oracle.
-
- http://www.orafaq.com/
-
- http://vonnieda.org/oracletool/
-
-- Commercial Oracle Tools and Links
-
- Assorted tools and references for general information.
- No recommendation implied.
-

[continued in next message]

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