MSDAORA.1 vs OraOLEDB.Oracle.1

Concerning the following SQL:

CREATE TRIGGER TR_EXAMPLE BEFORE INSERT ON EXAMPLE FOR EACH ROW BEGIN SELECT SEQ_EXAMPLE.NEXTVAL INTO :NEW.ID FROM DUAL; END; When using the driver MSDAORA.1 this works fine, but when using the driver oraOLEDB.Oracle.1 I get the error
"No value given for one or more parameters"

The problem seems to surround the : (colon). If I remove the : then the statement "works" as far as connection.execute command doesn't return an error. However, the create trigger syntax is incorrect and the trigger doesn't actually compile.

It seems as though the MSDAORA.1 driver is passing the SQL text as is to the oracle engine and OraOLEDB is not.
But, this seems strange that Microsoft's driver would pass it 'as is' and Oracle's would change it somehow.

Anyway, the question is, what would be the correct sql syntax when using OraOLEDB in this particular case

Also, why would there be a difference anyway, it doesn't make sense to me that you should have to be aware of what driver you are using.

gkelly Received on Fri Jun 20 2003 - 14:57:12 CDT