Dave,
Intended behavior. Check any SQL manual, the syntax descriptions won't
speak about an ending semi-colon. It is not part of the statement.
The semi-colon is syntax specific to SQL*Plus, telling it when your
statement ends, so that it can start sending it to the server. It works
only in SQL*Plus.
Brgds,
Mark
Dave Anderson wrote:
> 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
--
Mark Vandenbroeck Mobile : +32-495-59.55.62
Business Process Manager Email : [EMAIL PROTECTED]
EMEA Support Information Systems AIM : markvdb
