http://qs321.pair.com?node_id=581698


in reply to How to call Oracle stored procedures?

OK,

Thanks for the replies, I Have delved even deeper into this, and it turns out that to get this working the way I want to, I have to call a oracle stored procedure and get it to return a Oracle cursor.

Therefore part of my code in the main program now looks like this.
my $accessroles; $accessroles = $dbh_conn->run_query('check_user', "begin Mrf_Maint. +check_user(?,?); end;", $login_id, $accessroles ); while(my $hashRef = $accessroles->fetchrow_hashref()){ foreach(keys %$hashRef){ print "$_ is $hashRef->{$_}<br>"; } }
and in my database module, the run_query code looks like this
my $self = shift; my $name = shift; my $query = shift; my $login_id = shift; my $accessroles = shift; my $ora = "ORA_RSET"; my $sth = $self->{'_conn'}->prepare($query) || print $DBI::errstr; $sth->bind_param(1, $login_id); $sth->bind_param_inout(2, \$accessroles, 0, { ora_type => 'ORA_RSET +'} ); $sth->execute() || print $DBI::errstr; return $accessroles;
The second parameter that I'm binding is the OUT parameter as well as being of type CURSOR, which is why it also needs a few more parameters in the bind_param_inout call (line 52 in module).

However The error message I now recieve is
Can't bind :p2, ora_type 0 not supported by DBD::Oracle at Database.pm line 52.
Any further suggestions would be great.

Ant

PS, pl/sql code looks like this
CREATE OR REPLACE PACKAGE Mrf_Maint AS TYPE mrf_role IS REF CURSOR; PROCEDURE check_user(mrfid IN VARCHAR, + mrfrole OUT Mrf_Maint.mrf_role); END Mrf_Maint; /
and body
CREATE OR REPLACE PACKAGE BODY Mrf_Maint IS PROCEDURE check_user(mrfid VARCHAR, mrfrole OUT Mrf_Maint.mrf_role) IS crsr Mrf_Maint.mrf_role; BEGIN OPEN crsr FOR SELECT access_role FROM TABLE WHERE field = mrfid; END; END; /

Replies are listed 'Best First'.
Re^2: How to call Oracle stored procedures?
by runrig (Abbot) on Feb 06, 2009 at 16:42 UTC
    DBI types are integers, not strings. That's why it complains that the string 'ORA_RSET' is zero (non-numeric strings == 0). ORA_RSET is a constant exported by DBD::Oracle, so you can say:
    use DBD::Oracle qw(:ora_types); $sth->bind_param_inout(2, \$accessroles, 0, { ora_type => ORA_RSET} );
    Or since constants in perl are just functions, you can just say:
    $sth->bind_param_inout(2, \$accessroles, 0, { ora_type => DBD::Oracle: +:ORA_RSET() } );