Urget Help regarding DBD needed

[Vuppaladhadiam, Rajasekhar (Cognizant)]

Hi all

 

I have a requirement like this:

 

1. There is a XML File which is being opened and read from a perl script.
2. The perl script combines all the XML file contents into one string.
3. Using Compress::Zlib, this XML string is compressed into a data variable, say $xml_object (now it is a compressed binary string)
4. This xml_object variable needs to be stored as a BLOB onto Oracle database.

 

The Oracle database (version 8.15) is on Solaris 8 OS.

Perl - 5.6.1

DBD:Oracle - 1.12

 

One simple way is to write it to a file and store it in (probably file compression could be used). But that will not help in this case as it is against the design proposed. When the code uses a direct SQL call like this, it works very well.

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);

 

sub store_in_db
{
  my($title, $doc)= @_;
  my $max_len = 2000000;

 

  # Connect to the database.
  my $dbh = DBI->connect( qq{dbi:Oracle:oracle8}, qq{scott}, qq{tiger},
   { PrintError => 1, RaiseError => 1, AutoCommit => 0 } )
   or die $DBI::errstr;

 

  my $sth=$dbh->prepare(qq{INSERT INTO TABLE_XYZ(:1, :2)});

 

  # Bind variables to columns.
  $sth->bind_param(1, $title ); 
  $sth->bind_param(2, $doc, {ora_type => ORA_BLOB} );

 

  my $rv = $sth->execute() or die "store_in_db failed";

 

  $dbh->commit;
  $sth->finish;
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

However, this is causing some serious performance issues and so we intend to push this SQL onto a SQL package onto the Oracle DB side.

 

The package is like this

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

create or replace PACKAGE SP1
IS
  PROCEDURE store_in_db_sp( title IN Char, xml_object IN BLOB);
END;

 

create or replace
PACKAGE BODY SP1
IS
  PROCEDURE store_in_db_sp( ptitle IN Char, pxml_object IN BLOB)
  IS
    err EXCEPTION
    status NUMBER;
  BEGIN
    INSERT INTO TABLE_XYZ(ptitle, pxml_object);
  END;
END;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

and we are accessing it as

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

use strict;
use DBI;
use DBD::Oracle qw(:ora_types);

 

sub store_in_db
{
  my($title, $doc)= @_;
  my $max_len = 2000000;

 

  # Connect to the database.
  my $dbh = DBI->connect( qq{dbi:Oracle:oracle8}, qq{scott}, qq{tiger},
   { PrintError => 1, RaiseError => 1, AutoCommit => 0 } )
   or die $DBI::errstr;

 

  my $sth=$dbh->prepare(qq{
            BEGIN
              SP1.store_in_db_sp(:1, :2);
            END;
        });

 

  # Bind variables to columns.
  $sth->bind_param(1, $title ); 
  $sth->bind_param(2, $doc, {ora_type => ORA_BLOB} );
  #
  # Even if used as the following line, I am getting the same problem
  #
  # $stmt->bind_param(2,$doc,{ ora_type => ORA_BLOB,ora_field=>'XML_OBJ'};

 

  my $rv = $sth->execute() or die "store_in_db failed";

 

  $dbh->commit;
  $sth->finish;
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

It is not working and the error got is

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ORA-01858: a non-numeric character was found where a numeric was expected
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

First of all, if someone can tell me is this would yield a better performance, it would be great. Also, please suggest a solution to this problem please.

 

If this will not work, please suggest the alternatives .. anything that might yield a better performance than the direct SQL used as above.

 

With the Christmas around, I know it is not right to disturb the user group so much :( however, not being able to put this solution through will cause a lot of trouble !! So, kindly reply asap.

 

Thanks and Regards
Raju

 

 

 

 

This e-mail and any files transmitted with it are for the sole use of the intended 
recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and 
destroy all copies of the original message. 
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or 
copying of this email or any action taken in reliance on this e-mail is strictly 
prohibited and may be unlawful.

                Visit us at http://www.cognizant.com