geraltan has asked for the wisdom of the Perl Monks concerning the following question:

Pls help! Thanks! I have a stored procedure below, how do I get the resultset? 
CREATE OR REPLACE PACKAGE BODY ois_pkg
IS

    FUNCTION fn_tss_get_vsl_band(start_dt_in IN berth_allocation.atb_d
+t%TYPE,
                              end_dt_in IN berth_allocation.atb_dt%TYP
+E)
    RETURN ref_cursor
    AS
        band_cursor ref_cursor;

    BEGIN

        OPEN band_cursor FOR 
            SELECT v.vv_c, v.berth_seq_n, v.abbr_vessel_m, v.abbr_voya
+ge_out_n, h.berth_m, v.atb_dt,v.atu_dt,b.band_m
            FROM v_berth_allocation v, vessel_target t, band b, berth 
+h
            WHERE v.vv_c = t.vv_c AND  v.berth_seq_n = t.berth_seq_n A
+ND t.band_c = b.band_c
            AND v.terminal_c = h.terminal_c AND v.berth_n = h.berth_n 
+AND h.berth_m NOT LIKE 'M%'
            AND (v.atb_dt>=sysdate-5 and v.atb_dt<= end_dt_in)
            AND (v.atu_dt is null OR (v.atu_dt>=start_dt_in and v.atu_
+dt<=sysdate));

        RETURN band_cursor;

    END;

END ois_pkg;
/
sho errors;
/

[download]

Replies are listed 'Best First'.
Re: Getting stored procedure results from Oracle using Perl
by astroboy (Chaplain) on Jul 27, 2005 at 10:59 UTC

    This the the best introductory page I've come across learning about using Oracle stored procedures with Perl.

    Update: After looking at the above page again, I realise that it doesn't show how to get the results from a ref cursor. To do so, just use something like:

    my $sql = "BEGIN :res := my_package.my_function; END;";
my $sth = $dbh->prepare($sql)
$sth->bind_param_inout( ":res", \$result, 0, { ora_type => ORA_RSET } 
+);

    [download]

    $result will have your result set. You'll also need to import the ORA_RSET constant:

    use DBD::Oracle qw(ORA_RSET);

    [download]
[reply]
[d/l]
[select]
      And you can also call 'exec procname()' instead of declaring inline each time as above
[reply]
Re: Getting stored procedure results from Oracle using a Perl
by marto (Cardinal) on Jul 27, 2005 at 09:41 UTC
[reply]
      Thanks Martin. I should rephrase my question, if I created a stored procedure in Oracle using the above codes, how do I call it and have results returned in perl? Thanks!
[reply]
        Hi geraltan,

        Do you have any experience of Perl?
        Are you familiar with Database communication using Perl?
        If the answer is no, I would suggest reading some of this sites fantastic Tutorials.
        You want to connect to Oracle using DBI.
        You will also need DBD::Oracle if you do not already have it.

        There are plenty of examples of retrieving values from stored procedures. This off site reference is one I looked at a while back that contains an easy to follow example.

        Happy reading.

        Martin
[reply]

Back to Seekers of Perl Wisdom