1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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,283
    Likes Received:
    364
    Trophy Points:
    83
    Please use the code bbcode when you have code in the posts.
     

Share This Page