in reply to Getting error while invoking a SQL Procedure

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?

Replies are listed 'Best First'.
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]
      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]

        You don't need the BEGIN/END but if you just take them out it won't work. You'd have to prefix the sql with "call" and you don't want to do that as call gobbles up NO_DATA_FOUND errors (documented by Oracle somewhere).

[reply]

In Section Seekers of Perl Wisdom