Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

How to call Oracle stored procedures?

by ant (Scribe)
on Nov 01, 2006 at 09:24 UTC ( #581638=perlquestion: print w/ replies, xml ) Need Help??
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);

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; }
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.


Comment on How to call Oracle stored procedures?
Select or Download Code
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
    So it looks as if executing Stored Procedures in Oracle
    is a bit iffy.


Re: How to call Oracle stored procedures?
by holcapek (Acolyte) on Nov 01, 2006 at 10:15 UTC
Re: How to call Oracle stored procedures?
by ant (Scribe) on Nov 01, 2006 at 16:16 UTC

    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 line 52.
    Any further suggestions would be great.


    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; /
      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() } );
Re: How to call Oracle stored procedures?
by Mr. Muskrat (Abbot) 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);
      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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://581638]
Approved by davido
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (12)
As of 2015-04-27 18:07 GMT
Find Nodes?
    Voting Booth?

    Who makes your decisions?

    Results (508 votes), past polls