| |
 |
|
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. |
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
