Colin,
> Many thanks for the reply. I have coded this using
> separate statements in the SQL string as you suggest.
> The examples show multiple statements
> but it appears that this is not possible.
1) you're confusing "sql strings", "statements", and "pl/sql blocks"
2) you need to understand DML vs. DDL and the limits of plsql.
3) you should understand this is an Oracle issue, not a DBI issue.
1) A "sql statement" is a _single_ statement, "create table....",
"insert into....", etc. Example in DBI:
$dbh->prepare( q{ delete from SNAFU } );
A "pl/sql block" is a group of statements wrapped in "begin.. end;".
For example, this is a block in DBI:
$dbh->prepare( q{
BEGIN
update table FUBAR set COL = 100;
delete from SNAFU;
insert into SNAFU values ('abc');
END;
});
A pl/sql block must begin with BEGIN (or DECLARE) and it must
be terminated with "END;" (including semi-colon, case-insensitive).
2) SQL can be divided into:
DML - Data Manipulation Language (insert, update, delete)
DDL - Data Definition Language (create, drop, etc)
You can _only_ put DML inside a pl/sql block, not DDL.
So your problem was:
1) you tried to put DDL into a pl/sql block (Oracle disallows)
2) you didn't have a valid block (no begin/end;)
3) As the above should make clear (albeit tersely), this is
entirely an Oracle issue, not a DBI issue. For example, the exact
same features/limitations using any interface to Oracle.
Cheers,
Mark
--- [EMAIL PROTECTED] wrote:
>
> Dave,
>
> Many thanks for the reply. I have coded this using separate
> statements in
> the SQL string as you suggest. The examples show multiple statements
> but it appears that this is not possible.
>
> Regards,
> Colin
>
> -----Original Message-----
> From: Dave K [mailto:[EMAIL PROTECTED]]
> Sent: 07 February 2003 13:50
> To: [EMAIL PROTECTED]
> Subject: Re: DBD::Oracle multiple SQL statements - PL/SQL example
>
>
> Colin,
>
> When I try to test this the following error from 'plsql_errstr'
> indicates
> that it is not possible to have multiple statements in a SQL string.
> The
> PL/SQL statements are not being treated individually.
> No. Your package spec is trying to contain a package body.
>
> Errors for PACKAGE PLSQL_EXAMPLE:
> 6.6: PLS-00103: Encountered the symbol "CREATE"
>
> Because your package specification needs to be seperate from the
> package
> body.
>
> Review the syntax for package creation. If you have Procedure Builder
> on
> your system, run the script then go to procedure builder and look at
> the
> result.
> Good luck.
>
>
>
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
