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,
8 comments:
Wow ..Thx a lot
Wow! You save me a lot of time!
Thanks. This saved me hours of frustration!.
Thanks! That is exactly what I needed.
thanks a lot .............
@Bogdan:
You need to replace all Chr(13), Chr(10) combinations in your scripts with just Chr(10).
[Or remove Chr(13).]
You can do this using many languages and script environments.
E.g. using Javascript:
myOldTrigger.replace(String.fromCharCode(13), "");
Thanks a lot for sharing!!
Post a Comment