How to call Oracle stored procedures?

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

Fellow Monks,

I have a webpage that access a Oracle database, retrieves information and displays it. The sql is stored in the Perl scripts and executed and it all works really nicely.

However I want the Sql to be removed form the Perl, and use PL/SQL stored procedures instead. I've written the PL/SQL procedure and it compiles fine and works nicely.

I then took out the code for executing the sql script and replaced with code to execute a PL/SQL procedure.

I recieve the following error
ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute)

The code looks like this in the main Perlscript.($login_id is defined higher up.)

my $sth = $dbh_conn->run_query('check_user', 'exec Mrf_Maint.check_use
+r ?', $login_id);
[download]

run_query is another sub routine in a Perl module and looks like this.

sub run_query {

   my $self = shift;
   my $name = shift;
   my $query = shift;   
   
   my $sth = $self->{'_conn'}->prepare($query) || print $DBI::errstr; 
+  
   $sth->execute(@_) || print $DBI::errstr;

   return $sth;

}
[download]

Please note I want this sub routine to be used from several webpages/applications so I don't know how many params will be coming in for each call, therefore I pass in @_ to the execute which then binds the params automamtically for me.

Why does the code work for a SQL script embedded into the Perl but not work for a stored procedure call. I've also granted all the the PL/SQL stored procedure.

Any pointers really will be appreciated. Thanks in advance.

Ant

Comment on How to call Oracle stored procedures? Select or Download Code

Replies are listed 'Best First'.
Re: How to call Oracle stored procedures? by ColtsFoot (Chaplain) on Nov 01, 2006 at 09:39 UTC
From perldoc DBI Example 5 requires that you provide a stored procedure (SP_ISNULL in this example) that acts as a function: it checks whether a value is null, and returns 1 if it is, or 0 if not. Here is a table that indicates which examples above are known to work on various database engines: `-----Examples------ 0 1 2 3 4 5 6 - - - - - - - Oracle 9 N Y N Y Y ? Y Informix IDS 9 N N N Y N Y Y MS SQL N N Y N Y ? Y Sybase Y N N N N N Y AnyData,DBM,CSV Y N N N Y Y* Y` [download] So it looks as if executing Stored Procedures in Oracle is a bit iffy. HTH	[reply] [d/l]
Re: How to call Oracle stored procedures? by holcapek (Sexton) on Nov 01, 2006 at 10:15 UTC
Can't test it right now (no Oracle instance nearby), but AFAIK I've been calling stored procedures this way: `$dbh->do("begin my_plsql_func; end;");` [download] And as I see, others do the same: http://orafaq.com/faq/how_does_one_invoke_stored_procedures_with_dbi	[reply] [d/l]
Re: How to call Oracle stored procedures? by ant (Scribe) on Nov 01, 2006 at 16:16 UTC
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>"; } }` [download] 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;` [download] 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; /` [download] 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; /` [download]	[reply] [d/l] [select]
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} );` [download] 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() } );` [download]	[reply] [d/l] [select]
Re: How to call Oracle stored procedures? by Mr. Muskrat (Canon) on Nov 01, 2006 at 20:34 UTC
I've been using DBIx::ProcedureCall for a while now and have no complaints. Perhaps something like the following untested code would work for you. `... use Data::Dumper; use DBIx::ProcedureCall qw(Mrf_Maint.check_user:packaged:cursor:fetch) +; ... $accessroles = Mrf_Maint::check_user( $login_id, [ \$accessroles, 0 ] +); print Dumper($accessroles);` [download]	[reply] [d/l]
Re^2: How to call Oracle stored procedures? by Anonymous Monk on Feb 06, 2009 at 11:55 UTC
if you are trying to embed or execute a sql procedure in ant script use the following <sql ...............> <transaction>call mystoredprocedure();</transaction> </sql> -by nagaraj mamedi	[reply]


Your skill will accomplish what the force of many cannot
	PerlMonks