performing stored procedures with the DBI

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

I am trying to get a stored procedure to run using the following perl modules- DBI and DBD:Oracle. I am using perl 5.6.1, Windows NT, Oracle 8i, DBI 1.20, and DBD:Oracle-1.12.

I am able to connect and perform select statements using 'prepare', but when I try the following stored procedure:

$dbh->do("exec XML_BULK_LOADER.clear_database_values ('tester')");

I get the following error message:

DBD::Oracle::db do failed: ORA-0090: invalid SQL statement (DBD: error possibly near (*) indicator at char 1 in '(*)exec XML_BULK_LOADER.clear_database_values ('tester')') at ....

Do 'exec' statements work with the DBI? If not, is there a workaround?

Comment on performing stored procedures with the DBI Select or Download Code

Replies are listed 'Best First'.
Re: performing stored procedures with the DBI by mortis (Pilgrim) on Dec 18, 2001 at 02:44 UTC
According to the documentation (perldoc) for DBD::Oracle, all you should have to do is wrap you code with a BEGIN and END (Oracle's BEGIN/END PL/SQL, not Perl's BEGIN/END): `$dbh->do("BEGIN exec XML_BULK_LOADER.clear_database_values('tester') + END");` [download]	[reply] [d/l]