Problem in Executing PL/SQL block with Perl

Discussion in 'Perl' started by jessica_hynes, Jul 9, 2007.

  1. jessica_hynes

    jessica_hynes New Member

    Joined:
    Jul 3, 2007
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    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 a moderator: Jul 9, 2007
  2. jessica_hynes

    jessica_hynes New Member

    Joined:
    Jul 3, 2007
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    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 a moderator: Jul 10, 2007
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,336
    Likes Received:
    377
    Trophy Points:
    83
    Please use the code bbcode when you have code in the posts.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice