Oracle-Specific Methods

Oracle-Specific Methods

There are a number of special Perl DBI methods that support the use of DBD::Oracle and its handling of particular Oracle datatypes and operations.

DBMS_OUTPUT Methods

Four additional functions, available within DBD::Oracle, are provided for use in accessing Oracle's DBMS_OUTPUT built-in package; they are listed in Table B-6.

Table B-6. DBD::Oracle's private methods for Perl DBI

DBD::Oracle function

Description

plsql_errstr

Provides debug text from potential PL/SQL compilation errors

dbms_output_enable

Enables the DBMS_OUTPUT package for use with Perl

dbms_output_get

Provides access to the DBMS_OUTPUT.GET_LINE function

dbms_output_put

Provides access to the DBMS_OUTPUT.PUT_LINE function

The following code illustrates the use of some of these private methods:

#! perl -w
use strict;
use DBI;
  
# Step 1: Connect to orcacle database, orcl.
  
my $dbh = DBI->connect('dbi:Oracle:orcl', 'scott', 'tiger'), 
                       {RaiseError => 1, AutoCommit => 0} );
  
# Step 2: Enable the later collection of DBMS_OUTPUT information.
  
$dbh->func( 1_000_000, 'dbms_output_enable');
  
# Step 3: Prepare and run some anonymous PL/SQL containing some
# output from DBMS_OUTPUT.
  
my $sth = $dbh->prepare(q{
   DECLARE 
      hello_string VARCHAR2(50);
   BEGIN
      SELECT 'Hello ' || USER || '! :-)'
        INTO hello_string
        FROM DUAL;
      dbms_output.put_line( hello_string );
   END;
                         });
$sth->execute;
  
# Step 4: Get the output and print it out.
  
print $dbh->func( 'dbms_output_get' ), "\n";
  
$dbh->disconnect;

Let's see what's going on here.

First of all, set up the connection to the target database.
Now we use our private dbms_output_enable method to adjust the memory necessary to pick up DBMS_OUTPUT.PUT_LINE calls later.
We prepare a very simple piece of anonymous PL/SQL to print out a message from our sponsor.
Now we use a second private method, dbms_output_get, to pick up the relevant message via DBD::Oracle so we can print it out:
```
$ perl hello_dbd.pl
Hello SCOTT! :-)
```

Handling LOBs

When DBD::Oracle fetches LOBs (large objects), they are treated as LONGs and are subject to the LongReadLen and LongTruncOk handle attributes described earlier. Note that at the time of this writing, only single-row LOB updates were supported, and the ability to pass LOBS to PL/SQL blocks was not available. Consider the following examples:

To insert or update a large LOB, DBD::Oracle has to know about this operation in advance. To do this in Oracle8 you need to set the ora_type attribute — for example:
```
$sth->bind_param($field_num, $lob_value, { ora_type => ORA_CLOB });
```
The ORA_CLOB or alternative ORA_BLOB constants are imported with:
```
use DBD::Oracle qw(:ora_types);
```
To make scripts work for both Oracle7 and Oracle8 (and later), Oracle7's DBD::Oracle treats the LOBs as LONGs without error. Specify them as ORA_CLOB or ORA_BLOB, as above, and DBI will be able to handle the LOBs properly.
In inserts or update, where there are multiple LOB fields of the same type in a particular table, you must tell DBD::Oracle which field the LOB parameter relates to:
```
$sth->bind_param(1, $myLobValue, 
                 { ora_type => ORA_CLOB, ora_field= > 'my_column1' });
```
At the time of this writing there is no direct way to write LOBs in chunks via DBD::Oracle. The official back-door workaround is to use DBMS_LOB.WRITEAPPEND or, with some earlier versions of Perl DBI, the undocumented feature blob_read. Note, though, that it is always better to stick with documented DBI functions to remain on the safe side. (See Chapter 7, for mention of a possible future solution to this LOB problem via Oracle::OCI.)

Binding Cursors

DBD::Oracle returns cursors from PL/SQL blocks as shown here:

use DBI;
use DBD::Oracle qw(:ora_types);
  
my $dbh = DBI->connect('dbi:Oracle:orcl', 'scott', 'tiger'), 
                       {RaiseError => 1, AutoCommit => 0} );
  
my $sth = $dbh->prepare(q{
   BEGIN OPEN :cursor FOR
      SELECT deptno, dname, loc 
         FROM dept
         FROM user_tables WHERE loc = :loc;
   END; });
  
$sth->bind_param(":loc", "BOSTON");
  
my $sth_curs;
$sth->bind_param_inout(":cursor", \$sth_curs, 0, 
                       { ora_type => ORA_RSET } );
$sth->execute;
  
# $sth_curs can now be used like any other statement handle...
  
while ( @row = $sth_curs->fetchrow_array ) {

...

Notice how ora_type is set to ORA_SET; this is mandatory. See the curref.pl script in the Oracle.ex directory in DBD::Oracle's source distribution for more examples.