DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: many rows procedure into perl

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Question many rows procedure into perl

    Hi

    How do I fetch the values from oracle procedure returning many rows into a perl script? I have no problem with one row... Please advice, thanks!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You talking ref cursors?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2000
    Posts
    387
    Hi,

    Ref cursors in perl will code all sql in the script, I do not want that. Basically, I have written a procedure which returns many values. How do i execute this procedure in a perl script?

  4. #4
    Join Date
    Jan 2000
    Posts
    387
    For example, the following codes only return one row from the procedure. How do i return many rows from the procedure to the perl code?

    my $sth=$dbh->prepare("begin testproc(:in1,:in2,:in3,: out1,: out2,: out3); end;");
    $sth->bind_param("in1",1);
    $sth->bind_param("in2",1.1,2,1);
    $sth->bind_param("in3","hello");
    my $out1;
    my $out2;
    my $out3;
    $sth->bind_input_param("out1",\$out1,20);
    $sth->bind_input_param("out2",\$out2,20);
    $sth->bind_input_param("out3",\$out3,20);

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    From the code, I don't have the slightest idea of what you are trying to do. What does testproc look like?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jan 2000
    Posts
    387
    Hi

    I have changed my procedure (testproc) to return the multiple rows of data as a ref cursor type. I can execute it in sqlplus as shown below :

    var data ref cursor
    begin
    testproc(:data)
    end;

    In this case, how to I execute this procedure from perl?

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    perldoc DBD::Oracle

    search for bind_param_inout or ORA_RSET
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Jan 2000
    Posts
    387
    Thats what I have tried using the bind parameters. However, how do i declare the variable as ref cursor? I got an error that wrong type being used when excuting it in perl...

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Jan 2000
    Posts
    387
    Thanks, I read that before, I just want to confirm that the following codes means that I have to use my ref cursor procedure (define cursor type and including sql statements) inside the perl script and cannot be called directly from perl script like a normal procedure right?

    #!/usr/local/bin/perl -w

    use strict;

    use DBI;

    use DBD::Oracle qw(ra_types);

    my $dbh = DBI->connect('dbi:Oracle:','scott','tiger') or die $DBI::errstr;

    my $sth1 = $dbh->prepare(q{create or replace package types as

    type cursorType is ref cursor;

    end;});

    $sth1->execute;

    > >

    $sth1 = $dbh->prepare(q{


    create or replace function sp_ListEmp return types.cursorType

    as l_cursor types.cursorType;

    begin

    open l_cursor for select ename, empno from emp order by ename;

    return l_cursor;

    end;});

    $sth1->execute;

    $sth1 = $dbh->prepare(q{

    BEGIN
    :cursor := sp_ListEmp;
    END;

    });

    my $sth2;

    $sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );

    $sth1->execute();

    while ( my @row = $sth2->fetchrow_array ) { print join("|",@row),"\n"; }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width