Home » Open Source » Programming Interfaces » Reading LOBS using Perl - help!!
Reading LOBS using Perl - help!! [message #142988] Tue, 18 October 2005 11:35 Go to next message
Tobermorie_Moose
Messages: 4
Registered: October 2005
Location: London,, UK
Junior Member
Hi all,

I am trying to read a BLOB (LOB) from Oracxle 9i using Perl but am having issues understanding the basics of what I'm supposed to do. I do not understand where to use LongTruncOk & LongReadLen.

Can anyone help?

#############################################
I am using the following ppm's:

DBD-Oracle [1.16] Oracle database driver for the DBI module
DBI [1.48] Database independent interface for Perl
#############################################



#############################################
The following PERL is being run:



$query="select ts_value from ts_blob where ts_id = 46378627124";

print "\nQUERY7:- Run";
$sel5=$dbh->prepare($query);
$sel5->execute||die "Error in DB Execution : $DBI::errstr";
$Cell05 = $sel5->fetchrow_array;



Where $dbh is a standard db connection.
#############################################



#############################################
Which returns the error:

DBD::Oracle::st fetchrow_array failed: ERROR fetching field 2 of 1. LOB value truncated from 7421 to 80. DBI attribute LongReadLen too small and/or LongTruncOk not set [for Statement "select ts_value from ts_blob where ts_id = 46378627124"]
#############################################

Thing is - i just don't know what or how to set the values it talks about. I'm not a Perl expert and am having issues getting back into it ( I did it years ago - and have forgotten everything I knew).

If anyone can help - or give me sample code that shows how it is done that would be great.

I appreciate you reading this far.
Re: Reading LOBS using Perl - help!! [message #143004 is a reply to message #142988] Tue, 18 October 2005 13:59 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
The DBI api documentation on perl.com or some similar site should specify details for how to set various attributes, such as letting you truncate a lob if that is what you want to do.
icon12.gif  Re: Reading LOBS using Perl - help!! [message #144155 is a reply to message #142988] Tue, 25 October 2005 05:30 Go to previous messageGo to next message
Tobermorie_Moose
Messages: 4
Registered: October 2005
Location: London,, UK
Junior Member
For anyone in the same position as me in the future googling this topic - here is how to do it, pls forgive the code being incomplete it is intended as an example only:

This is using Oracle DBI

Using Oracle I am selecting my Blobs ID's and lengths - with something like this:

SELECT B.ts_id, length(B.ts_value).......

Where B.ts_value is the Blob

Then I loop through each Blob returned and set the LongReadLen variable - set as thus:

my( $TSID, $BLOBLEN, $STORENAME, $STARTDATE); #This is the return from the full query above
$sel5->bind_columns(\$TSID,\$BLOBLEN, \$STORENAME, \$STARTDATE); #Make my data storage

$HitCount = 0;

while($sel5->fetch()){

#Populate the Array with blobs one at a time & change the variable
$dbh2->{LongReadLen} = $BLOBLEN * 1024; #BLOBLEN is the result from the query above

$query = "SELECT ts_value from ts_blob where id = $TSID";

#This allows you dynamically set the LongReadLen as you wish for each return

#I then populate the Blob into $CELL05 and split it on a [ symbol into an array
#I then search through the array for the value in the Blob

@raw_data = split(/\[/, $Cell05);
foreach $line_data (@raw_data)
{

$sel3 = substr($line_data,63,30);
if (substr($line_data,63,30) eq "This is the line you want]" )
{
#print "\n$line_data-----------$TSID";
@raw_data2 [$HitCount] = $STARTDATE;
@raw_data3 [$HitCount] = $STORENAME;
$HitCount = $HitCount + 1;
}
else
{
#print "\nNOPE SECOND TIME";
}
}


Took me ages to work this out - shouldn't have but it did - I hope it helps you. Cool Cool
Re: Reading LOBS using Perl - help!! [message #311082 is a reply to message #144155] Thu, 03 April 2008 06:50 Go to previous message
RoopaRK
Messages: 1
Registered: April 2008
Location: Bangalore
Junior Member
Hi,

I am going through something similar...
trying to get the BLOB xmls using PERL.

Was happy to c that you have resolved this issue..
but would be very helpful if u send the complete code.

for ($i=0; $i<=$#INPUT; $i++)
{
$dbh->{LongReadLen} = 512 * 1024;
$dbh->{LongTruncOk} = 1;
my $stmt = "select GSRL_IMG_REC from net_rslt_gsrl where REGN_NBR=$INPUT[$i] and INFO_SRC_CD=7786",{ ing_readonly => 1} ;
my $sth = $dbh->prepare($stmt);
$sth->execute();
my $gdr_xml = $sth->fetchrow_array();
#open (FILE5,">>report_gdr.log");
#print FILE5 "$gdr_xml\n";
@line2[$i] = $gdr_xml;
print "\n gdr: $line2[$i] gdr";
}

My code looks something like this...
Previous Topic: Error while trying to retrieve text for error ORA-01019
Next Topic: implementing the ondblclick feature in php with an oracle database.
Goto Forum:
  


Current Time: Thu Mar 28 16:47:49 CDT 2024