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.)
run_query is another sub routine in a Perl module and looks like this.
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
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.
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.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; }
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
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: How to call Oracle stored procedures?
by ColtsFoot (Chaplain) on Nov 01, 2006 at 09:39 UTC | |
Re: How to call Oracle stored procedures?
by holcapek (Sexton) 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 | |
by runrig (Abbot) on Feb 06, 2009 at 16:42 UTC | |
Re: How to call Oracle stored procedures?
by Mr. Muskrat (Canon) on Nov 01, 2006 at 20:34 UTC | |
by Anonymous Monk on Feb 06, 2009 at 11:55 UTC |
Back to
Seekers of Perl Wisdom