Triggers, triggers and more triggers.
Thousands of them. Ran the scripts into Oracle using ColdFusion instead of SQLPlus only to find out that I now had thousands of invalid triggers.
A little puzzling, as the same scripts worked like a charm everywhere else. I go use the oracle web enterprise manager to see whehter I can recompile them and make a few valid. Nada!
Everytime the Enterrpise manager tries to compile the trigger I get the error:
PLS-00103: Encountered the symbol “”
Here is a sample trigger with issues:
CREATE OR REPLACE TRIGGER INS_MySuperTrigger BEFORE INSERT ON
MySuperTriggerTable REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
newid NUMBER;
BEGIN
If :new.MySuperTrigger_ID IS NULL THEN
SELECT SEQMySuperTrigger.NextVal INTO newid FROM dual;
:new.MySuperTrigger_ID := newid;
End If;
END;
This is nuts. I use the generate SQL button of Enterprise manager to generate the SQL and copy and paste it into the Oracel SQL Developer UI, run the code without mods, and bingo; the trigger is valid and happy as a peach.
What gives? Long hours wasted with different websites and options, casing, single and double quotes, Egyptian prayer beads…, you name it, I tried it.
Until pulling a protocol sniffer to see what the difference is between Oracle Enterprise Manager and Oracle SQL Developer on the wire.
Come to find out Oracle does not recognize Windows CRLF (Chr(13) + Chr(10)) as blank space, if you replace all the CRLF with LF this works like a charm. Seemingly, web based Oracle Enterprise Manager does not do this translation, while the Oracle SQL Developer tool does. Yack! Lesson learned, I now run the scripts through a parser before running them to Oracle via JDBC and get valid triggers all the time.
Cheers,