Section 7.4. Coding with Oracle::OCI

7.4 Coding with Oracle::OCI

In the following sections, we're going to present essentially the same example coded three different ways; each creates a simple table description:

Example 7-1 shows how the example looks using pure Oracle::OCI code. You'll notice that this is a lot more code than a typical Perl DBI script. Essentially, we use OCI functions on a one-to-one basis, so even just logging in to a database can take a whole page of code, whereas DBI does it in one line.
Example 7-2 shows how much shorter the example can be if you use pure Perl DBI, where a single DBI function takes the place of as many as ten Oracle::OCI functions. ^[4]

^[4] Basically, this is what DBI has been doing for us all along. It's just that we've had no need to worry about it before; it's all been kept under the covers.
Example 7-3 shows a blended approach. We combine, in a single Perl script, both Perl DBI and Oracle::OCI . Where we can use DBI commands within Oracle::OCI , we do so to save typing pages of code. The only places where we actually need to use one-to-one OCI mappings are the cases where we journey beyond Perl DBI. ^[5] (The earlier Figure 7-1 shows the relationship between Perl DBI and Oracle::OCI .)

^[5] We're only doing this because we're hunting for that last iota of extra functionality or performance. In general, we let the standard Army infantry of Perl DBI make up the bulk of the Normandy invasion forces. We bring in the Airborne troops of Oracle::OCI just do that little bit extra at the end.

7.4.1 Pure Oracle::OCI Code

This first example (Example 7-1) shows pure unadulterated Oracle::OCI code. Notice how just logging on takes over a page of code. (We'll work through the steps after the example.)

Example 7-1. rawOCI.pl ” Oracle::OCI in action

 #!/usr/bin/perl -w    # Pure-ish Oracle::OCI    use strict;    use DBI qw(neat); use Oracle::OCI qw(:all);  # Step 1: Get the environment right, and set up your target   # database and user, before we initialize  . $ENV{ORACLE_SID} = 'ORCL';  my $dbuser = $ENV{ORACLE_USERID}  'scott/tiger';    # The following call to new_ptr(  ) and bless are 'scaffolding'  # which this version of Oracle::OCI requires, but these will not be  # needed in future versions.  This will reduce the clutter  # and bring the code much closer to the equivalent OCI C code! :-)    sub new_ptr {     my $class = shift;     my $modifiable = do { my $foo = shift  0 };     return bless $modifiable => $class; }    # Initialize the environment via OCI.    my $envhp = new_ptr('OCIEnvPtr');    OCIInitialize (OCI_OBJECT, 0, 0, 0, 0); OCIEnvInit($envhp, OCI_DEFAULT, 0, 0);  # Step 2: Allocate the various handles  . # Get the Error Handle    OCIHandleAlloc ($$envhp, my $errhp=0, OCI_HTYPE_ERROR, 0, 0); bless $errhp => 'OCIErrorPtr';    # Get the Server Contexts etc.    OCIHandleAlloc ($$envhp, my $svrhp=0, OCI_HTYPE_SERVER, 0, 0); bless $svrhp => 'OCIServerPtr'; OCIHandleAlloc ($$envhp, my $svchp=0, OCI_HTYPE_SVCCTX, 0, 0); bless $svchp => 'OCISvcCtxPtr';  # Step 3: Now Attach and set the attribute server context   # within the Service context, before logging on  . OCIServerAttach ($svrhp, $errhp, 0, 0, OCI_DEFAULT); OCIAttrSet     ($$svchp, OCI_HTYPE_SVCCTX, $$svrhp, 0, OCI_ATTR_SERVER, $errhp);    OCIHandleAlloc($$envhp, my $authp=0, OCI_HTYPE_SESSION, 0, 0); bless $authp => 'OCISessionPtr';    my ($user, $pass) = split /\//, $dbuser; my @user_buf_len = oci_buf_len($user); my @pass_buf_len = oci_buf_len($pass);   OCIAttrSet  ($$authp, OCI_HTYPE_SESSION, @user_buf_len,               OCI_ATTR_USERNAME, $errhp);   OCIAttrSet  ($$authp, OCI_HTYPE_SESSION, @pass_buf_len,              OCI_ATTR_PASSWORD, $errhp);   # Finally, meine kleine Freunden, we begin a session...    my $status = OCISessionBegin ($svchp, $errhp, $authp,                                OCI_CRED_RDBMS, OCI_DEFAULT); warn get_oci_error($errhp, $status) unless $status == OCI_SUCCESS;    OCIAttrSet ($$svchp, OCI_HTYPE_SVCCTX, $$authp, 0,              OCI_ATTR_SESSION, $errhp);  # Step 4: Now prepare the description of the target table  ,  # and start some data processing  . OCIHandleAlloc($$envhp, my $dschp, OCI_HTYPE_DESCRIBE, 0, 0); bless $dschp => 'OCIDescribePtr';    my $tablename = $ARGV[0]; OCIDescribeAny ($svchp, $errhp, oci_buf_len($tablename),                  OCI_OTYPE_NAME, 1, OCI_PTYPE_TABLE, $dschp);    # Get the parameter descriptor.    OCIAttrGet ($dschp, OCI_HTYPE_DESCRIBE, my $parmp, 0, OCI_ATTR_PARAM,              $errhp, 'OCIDescribePtr');    # Get the table list, number of columns and description.    OCIAttrGet ($parmp, OCI_DTYPE_PARAM, my $collst, 0,              OCI_ATTR_LIST_COLUMNS, $errhp, 'OCIParamPtr');    OCIAttrGet ($parmp, OCI_DTYPE_PARAM, my $numcols, 0, OCI_ATTR_NUM_COLS,              $errhp, 'OCIParamPtr'); my $errstr;    # Describe the target table.    printf ("\n------------------\n"); printf ("TABLE : %s \n", $tablename); printf ("------------------\n");    my %col_attr = (    OCI_ATTR_NAME       =>  "ColName",     OCI_ATTR_IS_NULL    =>  "NULL?", );    foreach my $colnum (1..$$numcols) {       my $col_parmdp_int = 0;    my $col_parmdp = bless $col_parmdp_int => 'OCIParamPtr';    OCIParamGet($collst, OCI_DTYPE_PARAM, $errhp, $col_parmdp, $colnum);       my $describe_attr = {       OCI_ATTR_NAME       =>  0,        OCI_ATTR_IS_NULL    =>  1,    };    printf "\n";    foreach my $attr (sort keys %$describe_attr) {       my $type = $describe_attr->{$attr};       no strict 'refs';       $status = OCIAttrGet( $col_parmdp, OCI_DTYPE_PARAM,                              oci_buf_len(my $tmp, 90),                              &$attr, $errhp, $type);       warn "$attr: ".get_oci_error($errhp, $status, 'OCIAttrGet')           if $status;          warn get_oci_error($errhp, $status)           if $status;          printf "%-20s: %s\n", $col_attr{$attr}, neat($tmp);     } }  # Step 5: Logout and detach from the server  . OCIHandleFree($$dschp, OCI_HTYPE_DESCRIBE); OCISessionEnd($svchp, $errhp, $authp, 0); OCIServerDetach($svrhp, $errhp, OCI_DEFAULT );  # Step 6: Clean up memory and deallocate handles  . OCIHandleFree($$svrhp, OCI_HTYPE_SERVER); OCIHandleFree($$svchp, OCI_HTYPE_SVCCTX); OCIHandleFree($$errhp, OCI_HTYPE_ERROR); OCIHandleFree($$authp, OCI_HTYPE_SESSION);  # Bye, Bye !!! :-)

You can see that this is quite a bit of work. Let's go through the code and examine what it is we're doing:

We first ensure that our passwords and all the other usual suspects are sorted out before initializing the OCI environment.
Having initialized , we can now allocate all of our various memory handles.
We attach to the server, log in, and establish various attributes.
Now we can do some processing. In this case, we describe the various columns of a target table. We format and print the results as we go.
In the final stages, we end the session and then detach from the server.
We can now deallocate all of the memory handles.

Once completed, we can run the script with the target table supplied:

 $ perl rawOCI.pl  DEPT

We received the following output:

 ------------------ TABLE : DEPT  ------------------    NULL?               : 0 ColName             : 'DEPTNO'    NULL?               : 1 ColName             : 'DNAME'    NULL?               : 1 ColName             : 'LOC'

7.4.2 Pure Perl DBI and DBD::Oracle

We did an awful lot of work in Example 7-1 ” two pages of code just to get a few simple column descriptions! What if we'd done something similar in Perl DBI instead? We'll do just that in Example 7-2.

Example 7-2. rawDBI.pl

 #!/usr/bin/perl -w    use strict;    # Pure-ish DBI    use DBI qw(neat);  # Step 1: Get the environment right, and set up your target   # database and user  . $ENV{ORACLE_SID} = 'ORCL';  my $dbuser = $ENV{ORACLE_USERID}  'scott/tiger';  # Steps 2 & 3: We initialize and log onto the database  . my ($user, $pass) = split /\//, $dbuser; my $dbh = DBI->connect("dbi:Oracle:", $user, $pass,                        {RaiseError => 1});  # Step 4: Now prepare the description of the target table  . my $sth = $dbh->prepare("select * from $ARGV[0]"); $sth->execute;    # Describe the target table.    printf ("\n------------------\n"); printf ("TABLE : %s \n", $ARGV[0]); printf ("------------------\n");    my %col_attr = (    NAME => "ColName",    NULLABLE => "NULL?", );    foreach my $colnum (0..($sth->{NUM_OF_FIELDS} - 1)) {       printf "\n";    foreach my $attr (sort keys %col_attr) {       my $tmp = $sth->{ $attr }->[$colnum];       printf "%-20s: %s\n", $col_attr{$attr}, neat($tmp);    } } $sth->finish;  # Steps 5 & 6: Logout, clean-up and check out  . $dbh->disconnect;  # Bye, Bye !!! 8)

There's much less work involved using just Perl DBI and DBD::Oracle , especially with logging on and logging off. Just for the record, we obtained the following results:

 $ perl rawDBI.pl DEPT    ------------------ TABLE : DEPT  ------------------    ColName             : 'DEPTNO' NULL?               : ''    ColName             : 'DNAME' NULL?               : 1    ColName             : 'LOC' NULL?               : 1

So what's the point of Oracle::OCI ” the same result for four times the effort? Has the stardust of Perl lost its magic? No, it has become more powerful than you can imagine. What we're trying to do is get the finely-grained OCI stuff we mentioned earlier without having to do it all in hundreds of lines of C. We'll see how shortly.

7.4.3 Mixing and Matching Oracle::OCI, Perl DBI, and DBD::Oracle

When you need certain OCI functionality that isn't available in Perl DBI, the most effective thing to do is to mix and match. Where we can save code using Perl DBI, we can do that, and where we really need OCI functionality, we can do that too ” all within the same script. As the earlier Figure 7-1 showed, the various interface modules ” Oracle::OCI , Perl DBI, and DBD::Oracle ” are all tightly integrated. When you set up a $dbh database handle with Perl DBI, for example, you get access to all of the memory handles Oracle::OCI also requires. Take a look at Example 7-3 to see how we use the best of both the DBI and OCI worlds . In fact, this is why we think Oracle::OCI may potentially become the very best way of accessing the entire OCI API in any language. (See Section 7.5 at the end of this chapter.)

Example 7-3. blendOciDbi.pl ” Combining DBI and Oracle::OCI

 #!/usr/bin/perl -w    use strict;    # Blended DBI and OCI    use DBI qw(neat); use Oracle::OCI qw(:all);  # Step 1: Get the environment right, and set up your target   # database and user  . $ENV{ORACLE_SID} = 'ORCL';  my $dbuser = $ENV{ORACLE_USERID}  'scott/tiger';  # Steps 2 & 3: We initialize and log onto the database  . my ($user, $pass) = split /\//, $dbuser; my $dbh = DBI->connect("dbi:Oracle:$ENV{ORACLE_SID}", $user, $pass);  # Step 4: Now prepare the description of the target table, this time   # using OCI, after we've established our connection with DBI  .  # Notice the frequent use of the Perl DBI $dbh variable  . my $tablename = $ARGV[0];    bless $dbh => 'OCIEnvPtr'; OCIHandleAlloc($dbh, my $dschp, OCI_HTYPE_DESCRIBE, 0, 0); bless $dschp => 'OCIDescribePtr'; OCIDescribeAny ($dbh, $dbh, oci_buf_len($tablename), OCI_OTYPE_NAME,                  1, OCI_PTYPE_TABLE, $dschp);    # Get the parameter descriptor.    OCIAttrGet ($dschp, OCI_HTYPE_DESCRIBE, my $parmp, 0, OCI_ATTR_PARAM,              $dbh, 'OCIDescribePtr');    # Get the table list, number of columns and description.    OCIAttrGet ($parmp, OCI_DTYPE_PARAM, my $collst, 0,              OCI_ATTR_LIST_COLUMNS, $dbh, 'OCIParamPtr');    OCIAttrGet ($parmp, OCI_DTYPE_PARAM, my $numcols, 0,              OCI_ATTR_NUM_COLS, $dbh, 'OCIParamPtr'); my $errstr;    # Describe the target table.    printf ("\n------------------\n"); printf ("TABLE : %s \n", $tablename); printf ("------------------\n");    my %col_attr = (    OCI_ATTR_NAME       =>  "ColName",     OCI_ATTR_IS_NULL    =>  "NULL?", );    my $status; foreach my $colnum (1..$$numcols) {       my $col_parmdp_int = 0;    my $col_parmdp = bless $col_parmdp_int => 'OCIParamPtr';    OCIParamGet($collst, OCI_DTYPE_PARAM, $dbh, $col_parmdp, $colnum);       my $describe_attr = {       OCI_ATTR_NAME       =>  0,        OCI_ATTR_IS_NULL    =>  1,    };       printf "\n";    foreach my $attr (sort keys %$describe_attr) {       my $type = $describe_attr->{$attr};       no strict 'refs';       $status = OCIAttrGet( $col_parmdp, OCI_DTYPE_PARAM,                              oci_buf_len(my $tmp, 90),                              &$attr, $dbh, $type);       warn "$attr: ".get_oci_error($dbh, $status, 'OCIAttrGet')           if $status;       warn get_oci_error($dbh, $status) if $status;       printf "%-20s: %s\n", $col_attr{$attr}, neat($tmp);     } }  # Steps 5 & 6: Logout, clean-up and check out  . $dbh->disconnect;  # Bye, Bye !!! >=8+)

When we ran the code in Figure 7-3 in blendOciDbi.pl , we received this output:

 $ perl  blendOciDbi.pl DEPT    ------------------ TABLE : DEPT  ------------------    NULL?               : 0 ColName             : 'DEPTNO'    NULL?               : 1 ColName             : 'DNAME'    NULL?               : 1 ColName             : 'LOC'

With more than 530 OCI functions to choose from, we're confident you'll find exactly what you're looking for when you combine Perl DBI and Oracle::OCI. For instance, the following bioinformatics code snippet takes LOB processing a helpful bit further than DBI can go. We fetch a LOB locator with DBI, and then process its genetic information with Oracle::OCI :

 my $lob_locator = $dbh->selectrow_array("select my_lob " .                                           "from human_genome " .                                          "where id = 'insulin' " .                                            "for update",                                         { ora_auto_lob => 0 });  # Start Oracle::OCI   OCILobGetLength($dbh, $dbh, $lob_locator, my $lob_len = 0);   OCILobTrim($dbh, $dbh, $lob_locator, $lob_len - 2);   # Update the Bioinformatics genetic code inside the LOB   my ($offset, $amount, $buffer) = ($lob_len/2, 44, '');   OCILobRead($dbh, $dbh, $lob_locator, $amount, $offset  ,  oci_buf_len($buffer, 200, $amount), 0, 0, 0, 0 );   $buffer =~ s/ATGC/ACTG/g;   OCILobWrite($dbh, $dbh, $lob_locator, $amount, $offset  ,  oci_buf_len($buffer), OCI_ONE_PIECE, 0, 0, 0, 1 );   # Back to DBI  ...