The semicolon is a SQL*Plus thing -- not SQL itself. So you
don't use it in DBI which is providing a straight SQL interface.
> -----Original Message-----
> From: Dave Anderson [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 13, 2001 10:34 AM
> To: [EMAIL PROTECTED]
> Subject: Help with semi colons and Oracle blocks through dbi
>
>
> Hi,
>
> I'm getting well confused about this -
>
> This statement
>
> $sth = $dbh->prepare("drop table ADDRESS");
>
> $sth->execute();
>
> works, while this one
>
> $sth = $dbh->prepare("drop table ADDRESS;");
>
> $sth->execute();
>
> gives DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement
> (DBD ERROR: OCIStmtExecute)
>
> I have a script for ddl stuff, with various statements ended by semi
> colons. These give me invalid sql statements in dbi, even though they
> work with sql plus. So I had my perl script strip out the semi colons,
> and each time it does that, execute the current block. That all works
> fine through dbi, until I come to create a trigger:
>
> $sth = $dbh->prepare("CREATE OR REPLACE TRIGGER ADDRESS_ID_TRIG
> BEFORE INSERT
> ON ADDRESS
> FOR EACH ROW
> BEGIN
> SELECT ADDRESS_SEQ.NEXTVAL
> INTO :NEW.ADDRESS_ID
> FROM DUAL");
>
> $sth->execute();
>
> $sth = $dbh->prepare("END");
>
> $sth->execute();
>
> This also gives DBD::Oracle::st execute failed: ORA-00900: invalid SQL
> statement (DBD ERROR: OCIStmtExecute) for the second execute. the
> original sql, with DUAL; END; works fine through sqlplus.
>
> I must be doing this all wrong. Can anyone help?
>
> many thanks in advance
> Dave
>
