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.
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
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.
and in my database module, the run_query code looks like thismy $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>"; } }
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).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;
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
and bodyCREATE 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; /
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 |
In Section
Seekers of Perl Wisdom