Re: Cannot execute stored proc from oraperl

tule_at_netcom.com (Tu Le) wrote:
>My oraperl script returns error:
>
>ORA-00900: invalid SQL statement at net.pl line 17
>
>whenever I try to execute a stored procedure from oraperl
>
>Here is my script:

[ snip snip ]

>#
># execute stand-alone stored procedure get_emp
>#
>$csr1=&ora_open($lda,'EXECUTE get_emp(:1)') || die $ora_errstr; # <<<< line 17
>$status = &ora_bind($csr1,"KING") || die $ora_errstr;
>print $status,"=",$status,"\n";

[ snip snip ]

>Obviously, oraperl does not understand 'execute' statement.
>I assume that 'execute' is SQL statement, anything I pass through
>oraperl should be passed to SQL parser. Why does not SQL parser
>regconize 'execute' as a legal command?

EXECUTE is not an SQL statement. It is an SQL*Plus statement. What it does is to dress the call up into a PL/SQL block and pass it to the server for parsing. You can do the same, as follows:

$csr1=&ora_open($lda,'BEGIN get_emp(:1); END;') || die $ora_errstr;

The next problem you will meet is that there is no way to pass results back from a PL/SQL block to oraperl. Oraperl was designed for Oracle 6. It is only by chance that PL/SQL works at all.

You can have an INSERT statemeint in a PL/SQL block, and fetch the value back in a subsequent SQL SELECT.

>Can anyone help?

I hope I have. It is a pity that bandwith has already been wasted by people who couldn't help. Received on Wed Jan 24 1996 - 00:00:00 CET