Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

LOB Types and REF Cursors with Oracle Perl DBI

Oracle Tips by Mladen Gogala

Mladen Gogala is the author of "Easy Oracle PHP".

See the whole article: Perl DBI and Oracle

Mladen Gogala is available to assist you with your toughest Perl DBI problems.  Just call.

LOB Types and REF Cursors with Oracle Perl DBI

REF cursors are an extremely handy way of returning sets of records. Before proceeding with Perl scripting, let's first create PL/SQL code that will help us demonstrate the concept:

CREATE OR REPLACE PACKAGE dbi AS
  type refcsr IS ref CURSOR;
  PROCEDURE test(dno IN NUMBER,   csr OUT refcsr);
END;
/
 
CREATE OR REPLACE PACKAGE BODY dbi AS
  PROCEDURE test(dno IN NUMBER,   csr OUT refcsr) IS
  BEGIN
 
    OPEN csr FOR
    SELECT ename,
      job,
      sal,
      hiredate
    FROM emp
    WHERE deptno = dno;
  END;
END;
/

 

Let's say we want to execute the following PL/SQL code in our  Perl script:

 

BEGIN

       DBI.TEST(:DNO,:CSR);

END;

                      

The key to success lies in understanding  what exactly is our REF cursor variable. REF Cursor variables are Oracle's way of returning a statement handle.  I strongly believe that an example is worth a thousand words, so here it is:

Example 4

 

#!/usr/bin/perl -w
use strict;
use DBI;
 
# Note inclusion of DBD::Oracle data types. Without it, we cannot
# work with REF Cursor variables.
 
use DBD::Oracle qw(:ora_types);
 
my $db = DBI->connect( "dbi:Oracle:Local", "scott", "tiger" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
 
my $dno=10;
my $csr;
my ($ename, $job, $sal, $hiredate);
 
my $SEL = "begin
                               dbi.test(:DNO,:CSR);
           end;";
 
my $sth = $db->prepare($SEL);
 
 
$sth->bind_param_inout( ":DNO", \$dno, 20);
# Now comes the important part.....
$sth->bind_param_inout( ":CSR", \$csr, 0, { ora_type => ORA_RSET } );
$sth->execute();
print "\nNow, we have a valid handle....\n\n";
# Observe that here we are fetching from csr, a handle which was
# prepared and executed by the DBI.TEST procedure.
while ( ($ename, $job, $sal, $hiredate)=$csr->fetchrow_array()) {
        write;
}
no strict;
format STDOUT_TOP =
Employee         Job        Hiredate       Salary
----------------------------------------------------
.
 
format STDOUT =
@<<<<<<<<<<      @<<<<<<<   @<<<<<<<<<     @<<<<<<<
$ename,          $job,      $hiredate,     $sal
.
 
END {
    $db->disconnect if defined($db);
}

 

When executed, this code will produce the following output:

 

bash-3.00$ ./ttt

Now, we have a valid handle....

Employee         Job                           Hiredate       Salary

-------------------------------------------------------------------

CLARK            MANAGER    09-JUN-81      2450

KING             PRESIDEN   17-NOV-81      5000

MILLER           CLERK      23-JAN-82      1300

So, what can we see in this example?  First, we had to explicitly include DBD::Oracle, with the type definitions that are not normally exported by the module. Second, we had to do an "inout" bind, with size set to 0 and with a hash reference which tells DBI  the oracle type of the variable being bound. Third, REF Cursor variables are, in essence, statement handles. When a PL/SQL procedure returns REF Cursor variable, it is just the same as if the handle was produced by using $db->prepare.

What is the most frequent use of REF Cursor variables? I'm a database administrator and I've never had to use them, I only needed to support them when the real programmers were using them. The most frequent situations involving use of the REF Cursor variables were migration projects where the source database used result sets as a return from a stored procedure. Databases that are known for encouraging such development style are  Sybase and SQL Server. Any migration of a program which runs against SQL Server or Sybase to a Perl script that runs against Oracle database is likely to need REF Cursor variables. Of course, this part of the article would not be complete without explanation of the "real programmers" reference. For those among you who want to become real programmers, here are some definitions:

http://www.toodarkpark.org/textfile.cgi/computers/humor/real-programmers

I can only wish you luck with your further career endeavors.  Real programmers aside, let's return to DBI and Oracle. REF Cursor is a special column type that warrants inclusion of DBD::Oracle directly, in order to gain access to the definition of types. The same is true for LOB data types. LOB is an abbreviation for "Large Objects".  LOB types are used when we need to store large documents, images or other types of media files into the database.

Good thing about the LOB data types is that it is mostly handled automatically by the DBI itself. There are two separate cases which need handling: inserting LOB data and selecting LOB data. To demonstrate loading LOB data into the database, we'll need some database infrastructure:

 

SQL> create table dbi_lob (
  2  id number(10,0) constraint dbi_lob_pk primary key,
  3  name varchar2(128) not null,
  4  data blob);
 
Table created.
 
SQL> create sequence dbi_lob_seq start with 1 nocycle nomaxvalue;
 
Sequence created.
 
SQL> create trigger dbi_log_pk_trg
  2  before insert on dbi_lob
  3  for each row
  4  begin
  5  select dbi_lob_seq.nextval into :new.id
  6  from dual;
  7  end;
  8  /
 
Trigger created.

Unfortunately, normal  Oracle demo tables, belonging to SCOTT do not contain LOB columns. No problems, we have just created everything needed for such a demo. The next script will read the file name from the command line argument and read the whole file into a memory buffer. That memory buffer will then be inserted as a BLOB into the database.

Example 5

#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
use Getopt::Long;
my ( $user, $passwd, $db ) = ( "scott", "tiger", "local" );
my ( $file, $id );
my $stat = GetOptions( "u|user=s"     => \$user,
                       "p|password=s" => \$passwd,
                       "d|database=s" => \$db,
                       "f|file=s"     => \$file,
                       "h|help|?"     => \&usage
);
if ( !defined($file) or !$stat ) { usage(); }
my $dbh = db_connect( $user, $passwd, $db );
my $INS = "INSERT INTO DBI_LOB(NAME,DATA) VALUES (:FL,:BUFF)
                    RETURNING ID INTO :ID";
my $sth = $dbh->prepare($INS);
$sth->bind_param( ":FL", $file );
$sth->bind_param_inout( ":ID", \$id, 20 );
my $buff = gobble($file);
$sth->bind_param( ":BUFF", $buff, { ora_type => ORA_BLOB } );
 
$sth->execute();
 
print "BLOB ID=$id inserted\n";
$dbh->commit();
 
END {
    $dbh->disconnect() if defined($dbh);
}
 
sub db_connect {
    my ( $username, $passwd, $db ) = ( @_, $ENV{"TWO_TASK"} );
    my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd )
        || die( $DBI::errstr . "\n" );
    $dbh->{AutoCommit}    = 0;
    $dbh->{RaiseError}    = 1;
    $dbh->{ora_check_sql} = 0;
    $dbh->{RowCacheSize}  = 16;
     return ($dbh);
}
 
sub usage {
    print qq(
      USAGE:$0
               -u <username>  -p <password > -d <database>
               -f <file name>
           
      -------
      This script loads file defined by -f argument into database, as a
      BLOBS. This script was written as an educational tool and is free
      to use and modify as needed.
);
    exit(0);
}
 
sub gobble {
    my $file = shift;
    local $/ = undef;
    if ( !defined($file) ) { usage(); }
    open( FL, "<", $file ) or die "Cannot open file $file for reading:$!\n";
    my $buff = <FL>;
    close FL;
    return ($buff);
}
 

The execution of this script produces very unspectacular, if expected results:

 

$ ./load_lobs -f adodb480.tgz

BLOB ID=1 inserted

$ ./load_lobs -f php-5.1.2.tar.bz2

BLOB ID=2 inserted

 

So, lets check our work:

 

$ ls -l adodb480.tgz php-5.1.2.tar.bz2
-rw-r--r--  1 500 6319905 Mar 30 21:04 php-5.1.2.tar.bz2
-rw-r--r--  1 500  452518 Mar 30 21:04 adodb480.tgz
 
SQL> column name format a25
SQL> select name,length(data) as filesize from dbi_lob;
 
NAME                        FILESIZE
------------------------- ----------
adodb480.tgz                  452518
php-5.1.2.tar.bz2            6319905

 

The number of bytes in the database is equal to the number of bytes produced by the "ls -l" command, which means that both files are successfully loaded into my little home database.

This script inserts binary files into our little DBI_LOB table. The important things are the following:

Bind of the large memory buffer, containing file content ("$buff") was done by value, rather then by reference.  That way, I didn't have to specify length of the data,  just the type was sufficient. BLOB variables aren't much different from VARCHAR2 or NUMBER variables, they're just bigger and the only thing that needs to be done is to specify the type, to warn Oracle that something big is coming its way.

RETURNING INTO clause saves us trip to over the network, but the target needs to be bound  by reference, using "bind_param_inout" method.

I advocated the naming convention about naming placeholders and variables and violated it two scripts later.  Why did I do that? The answer is very simple: placeholder :FILE is reserved and cannot be used.  The truth of the matter is that I've hit the error below:

DBD::Oracle::st execute failed: ORA-01745: invalid host/bind variable name (DBD ERROR: error possibly near <*> indicator at char 48 in 'INSERT INTO DBI_LOB(ID,NAME,DATA) VALUES (NULL,:<*>file,:buff) RETURNING ID INTO :id')

I distinctly remember using the bind variable :FILE earlier in my career, but there was no other recourse here except changing the placeholder name. Things like that depend upon the version of DBI, the version of DBD::Oracle and the version of Oracle RDBMS used. Obviously, there are still things to discover about this particular combination. This error forced me to break my own rule. Such is life.

That concludes the section about inserting LOB variables into database. How do we get them out of the database?  Database is not a black hole, what comes in, has to come out sooner or later.

Few lines earlier, I said that LOB variables can be thought of as big VARCHAR2 variables. That especially applies to selecting them from the database. We can use normal binds, LOB variable is treated as any other variable would be. There are, however, some things  that need careful handling. It is time to learn about some other properties of the database handles. Here they are, taken directly from the DBI online documentation:

 

LongReadLen (unsigned integer, inherited)

The LongReadLen attribute may be used to control the maximum length of 'long' type fields (LONG, BLOB, CLOB, MEMO, etc.) which the driver will read from the database automatically when it fetches each row of data.

The LongReadLen attribute only relates to fetching and reading long values; it is not involved in inserting or updating them.

A value of 0 means not to automatically fetch any long data. Drivers may return undef or an empty string for long fields when LongReadLen is 0.

The default is typically 0 (zero) bytes but may vary between drivers. Applications fetching long fields should set this value to slightly larger than the longest long field value to be fetched.

Some databases return some long types encoded as pairs of hex digits. For these types, LongReadLen relates to the underlying data length and not the doubled-up length of the encoded string.

Changing the value of LongReadLen for a statement handle after it has been prepare'd will typically have no effect, so it's common to set LongReadLen on the $dbh before calling prepare.

For most drivers the value used here has a direct effect on the memory used by the statement handle while it's active, so don't be too generous. If you can't be sure what value to use you could execute an extra select statement to determine the longest value.

LongTruncOk (boolean, inherited)

The LongTruncOk attribute may be used to control the effect of fetching a long field value which has been truncated (typically because it's longer than the value of the LongReadLen attribute).

By default, LongTruncOk is false and so fetching a long value that needs to be truncated will cause the fetch to fail. (Applications should always be sure to check for errors after a fetch loop in case an error, such as a divide by zero or long field truncation, caused the fetch to terminate prematurely.)

If a fetch fails due to a long field truncation when LongTruncOk is false, many drivers will allow you to continue fetching further rows.

These attributes are typically set for the database handle, immediately after the connection is established. This is the reason for having separated connecting to database in a separate subroutine in the last script. The example which will fetch LOB data from the database will use the same skeleton, command line arguments, usage and everything else. It will only modify  subroutine db_connect and  the SQL executed within the script. Cut and paste between scripts works really well with Perl and that is one more reason for loving it.

So, in the example 6,  LongTruncOk will be set to false and LongReadLen will be set to 5 MB The first file (ID=1) is around 400k in length and it will not make any problems. The other file (ID=2) is, however, more then 6 MB long and will help us to demonstrate what happens when the length of the LOB is larger then LongReadLen. Without further ado, here it is:

 

Example 6

#!/usr/bin/perl -w
use strict;
use DBI;
use Getopt::Long;
my ( $user, $passwd, $db ) = ( "scott", "tiger", "local" );
my ( $id, $blob, $file ) = (1);
my $stat = GetOptions( "u|user=s"     => \$user,
                       "p|password=s" => \$passwd,
                       "d|database=s" => \$db,
                       "i|id=s"       => \$id,
                       "h|help|?"     => \&usage
);
if ( !$stat ) { usage(); }
my $dbh = db_connect( $user, $passwd, $db );
my $SEL = "SELECT name,data FROM dbi_lob WHERE id=:ID";
my $sth = $dbh->prepare($SEL);
$sth->bind_param( ":ID", $id );
$sth->execute();
($file,$blob)=$sth->fetchrow_array();
spew($file);
print "File $file written successfully. Len=",length($blob),"\n";
 
END {
    $dbh->disconnect() if defined($dbh);
}
 
sub db_connect {
    my ( $username, $passwd, $db ) = ( @_, $ENV{"TWO_TASK"} );
    my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd )
        || die( $DBI::errstr . "\n" );
    $dbh->{AutoCommit}    = 0;
    $dbh->{RaiseError}    = 1;
    $dbh->{ora_check_sql} = 0;
    $dbh->{RowCacheSize}  = 16;
    $dbh->{LongReadLen}   = 5242880;
    $dbh->{LongTruncOk}   = 0;
    return ($dbh);
}
 
sub usage {
    print qq(
      USAGE:$0
               -u <username>  -p <password > -d <database>
               -i id

           

      -------

      This script unloads blob with id defined by -i parameter from the

      database. This script was written as an educational tool and is

      free to use and modify as needed.

);
    exit(0);
}
 
sub spew {
    my $file = shift;
    if ( !defined($file) ) { usage(); }
    open( FL, ">", $file ) or die "Cannot open file $file for writing:$!\n";
    printf FL ("%s",$blob);
    close FL;
}

Unloading of adodb480.tgz goes without a problem:

$ rm -i adodb480.tgz
rm: remove regular file `adodb480.tgz'? y
 
$ ./unload_lobs  -i 1
File adodb480.tgz written successfully. Len=452518
 
$ tar ztvf adodb480.tgz|head -5
-rw-rw-rw- 0/0           12458 2006-03-10 03:58:55 adodb/adodb-active-record.inc.php
-rw-rw-rw- 0/0            8304 2006-03-10 03:58:54 adodb/adodb-csvlib.inc.php
-rw-rw-rw- 0/0           21041 2006-03-10 03:58:55 adodb/adodb-datadict.inc.php
-rw-rw-rw- 0/0            8541 2006-03-10 03:58:56 adodb/adodb-error.inc.php
-rw-rw-rw- 0/0            2748 2006-03-10 03:58:56 adodb/adodb-errorhandler.inc.php

Not only is the file size correct, tar and gunzip utilities also work correctly. Now, let's try with the id=2,  the source code for PHP 5.1.2, which exceeds 5 MB, the value that we've set our LongReadLen to:

$ ./unload_lobs -i 2

DBD::Oracle::st fetchrow_array failed: ERROR fetching field 3 of 2. LOB value truncated from 6319905 to 5242880. DBI attribute LongReadLen too small and/or LongTruncOk not set [for Statement "SELECT name,data FROM dbi_lob WHERE id=:ID" with ParamValues: :id='2'] at ./unload_lobs line 19.

DBD::Oracle::st fetchrow_array failed: ERROR fetching field 3 of 2. LOB value truncated from 6319905 to 5242880. DBI attribute LongReadLen too small and/or LongTruncOk not set [for Statement "SELECT name,data FROM dbi_lob WHERE id=:ID" with ParamValues: :id='2'] at ./unload_lobs line 19.

DBI killed our script because RaisError was set and LongTruncOk was not set. This is a desired outcome. If LongTruncOk was set, the script would have produced a shortened version of the original file which would be useless.

Unfortunately, LongReadLen can not be set to -1 or any other catch-22 value, to prevent the handler from kicking in. If you're unloading LOB columns from database into a Perl script, the script must have  enough memory space available to accommodate the largest of the LOB columns. In all other respects, LOB columns are just like the other VARCHAR2 or NUMBER columns, just larger. In this case, size does matter.

This is not the end of the story. We can work with LOB columns on piece by piece base, using either DBMS_LOB database package or specific abilities for DBD::Oracle, as described in the online documentation. This is, however, an advanced use of BLOB fields, which has no place in an introductory document like this. Advanced LOB handling capabilities of DBD::Oracle are beyond the intended scope of this document.

Question of Life, Universe & Everything

This is the ending part of this, hopefully useful, document in which the author rides into sunset, after few remarks and wisecracks. To achieve the true Hollywood happy end, I must list and comment on the unsupported features and the most appropriate use of  Perl and Oracle combination. Without further ado, here are the unsupported features that I consider important:

l       Array interface

l       Transparent application failover (TAF).

l       Direct load/unload

For a complete treatment of PHP and Oracle, see Mladen Gogala's great new book "Easy Oracle PHP". 

This unique book focuses on removing the guesswork from creating dynamic web pages with Oracle content, and it's only $19.95.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.