Problem in Executing PL/SQL block with Perl

jessica_hynes's Avatar, Join Date: Jul 2007
Light Poster
Hi All,

As I am never used Oracle with Perl...I am unable to get the values which I
am fetching in PL/SQL variables..

Can anyone please look into this problem?

Here my code

Code:
#!/usr/bin/perl

use DBI qw(:sql_types);


$dbh=DBI->connect("$datasource","$user","$pwd");



my($input_booking_seq, $fax_header, $file_no,$input_print_rates, $myNoteId, $printer_name);



eval {

$sth = $dbh->prepare($sql = q{
DECLARE
user_id VARCHAR2(15);
input_booking_seq VARCHAR2(20);
fax_header VARCHAR2(10);
file_no VARCHAR2(15);
BEGIN

select user_id,passkey, '//FAX(fax=' || passkey1, passkey2
into user_id,input_booking_seq,fax_header,file_no
from edit_table
where edit_table.tag = 'BOOKING'
and edit_table.key = 'PRINT' and id=3;

END;
}) or die $dbh->errstr;


$sth->bind_param(":user_id",$user_id);
$sth->bind_param(":input_booking_seq",$input_booking_seq);
$sth->bind_param(":fax_header",$fax_header);
$sth->bind_param(":file_no",$file_no);

$sth->execute;

};


if($@){

print "Failed : $DBI::errstr \n";
$dbh->errstr;

}else{

print "Returned $user_id,$input_booking_seq,$fax_header,$file_no \n";

}

$dbh->disconnect;
I want to see the values which I am fetching from select query..

Last edited by shabbir; 9Jul2007 at 19:11.. Reason: Code block
0
jessica_hynes's Avatar, Join Date: Jul 2007
Light Poster
Now I am able to get the values but I am not able to print those values my code Hi,I am trying to print the values in perl while working with oraclemy code is

Code:
$sth = $dbh->prepare(q{ 

DECLARE 

user_id VARCHAR2(50); 
input_booking_seq VARCHAR2(50); 
fax_header VARCHAR2(50); 
file_no VARCHAR2(50); 

BEGIN 

select user_id,passkey, '//FAX(fax=' || passkey1, passkey2 
into user_id,input_booking_seq,fax_header,file_no 
from edit_table 
where edit_table.tag = 'BOOKING' 
and edit_table.key = 'PRINT' and passkey = '1533805'; 

dbms_output.put_line('The values are '||input_booking_seq); 
END; 
}) or die $dbh->errstr; 

$dbh->func( 1000000, 'dbms_output_enable' ); 
$dbh->func( $values, 'dbms_output_put' ); 
$values = $dbh->func('dbms_output_get' ); 
$sth->execute; 


print "$values \n";
But I am not getting any value

Please Suggest

Last edited by shabbir; 10Jul2007 at 16:52.. Reason: Code block
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Please use the code bbcode when you have code in the posts.