How to call Oracle stored procedures?by ant (Scribe)
|on Nov 01, 2006 at 09:24 UTC||Need Help??|
ant has asked for the
wisdom of the Perl Monks concerning the following question:
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.
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.