Semicolons are not a part of SQL. They are a part of most SQL processing
programs like sqlplus, mysql, etc..., to identify the end of the query, and
that's it. DBI knows the end of the query since it's all contained in a
string passed to prapare or do, so no semicolon is needed. By using a
semicolon in invalidates the SQL statement.
Ilya
-----Original Message-----
From: Dave Anderson
To: [EMAIL PROTECTED]
Sent: 06/13/2001 8:34 AM
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
