Getting error while invoking a SQL Procedure

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

Hi, I invoked a SQL stored procedure and passed certain values from the perl script to it so as to display certain information later on a web page. But I am getting an error. Also, I am using some private modules. <code>

rolling back: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) at db1.pl line 109

Can't call method "execute" without a package or object reference at db1.pl line 111. Please tell me how to invoke a procude when using modules.
Rocko

Comment on Getting error while invoking a SQL Procedure

Replies are listed 'Best First'.
Re: Getting error while invoking a SQL Procedure by graff (Chancellor) on Sep 07, 2009 at 17:22 UTC
I'm puzzled that the line in your code that you refer to as #109 is assigning this value to $sql: `BEGININTRA.SP_LOG_CONSOLE (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);END;` [download] but the error message you quoted seems to say that the value of $sql is actually: `BEGIN CBT.SP_LOG_CONSOLE(?, ?, ?, ?, ?, ?, ?, ?, ?,?);END;` [download] (minus a few line-feed characters, which I assume are not important to the question). Are you really showing us the right code (and/or the right error message)? And why are you using a "BEGIN...END" here anyway? Apart from that, this may be more a question of SQL syntax, not Perl. You haven't said anything about what the "data_sources_tools::putData()" function does. Have you looked into that to see whether you are using it correctly?	[reply] [d/l] [select]
Re^2: Getting error while invoking a SQL Procedure by Rocko19 (Acolyte) on Sep 08, 2009 at 06:50 UTC
Apologies for any confusion. I need to call a procedure and pass certain parameters to it. I am not able find out the correct way to do it.	[reply]
Re^3: Getting error while invoking a SQL Procedure by graff (Chancellor) on Sep 08, 2009 at 13:24 UTC
I think you don't need any special treatment with "BEGIN" and "END" -- the sql statement should just be the procedure call, and when you execute it, you just have to make sure you pass the right number of params in the right order: `my $sql = "CBT.SP_LOG_CONSOLE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; my $sth = $dbh->prepare( $sql ); my @params = ...; # you need 10 values here $sth->execute( @params );` [download] If that comes back with an error from Oracle, maybe there's a problem with the stored procedure at the server side.	[reply] [d/l]
Re^4: Getting error while invoking a SQL Procedure by mje (Curate) on Sep 08, 2009 at 13:56 UTC
Re: Getting error while invoking a SQL Procedure by mje (Curate) on Sep 07, 2009 at 16:01 UTC
The error would indicate otherwise but your code is missing a space after BEGIN in: `$sql = "BEGIN"."INTRA.SP_LOG_CONSOLE (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);". +"END;";` [download] Otherwise looks ok to me.	[reply] [d/l]