Thursday, March 26, 2009

Oracle: PLS-00103: Encountered the symbol ""

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:

vIns said...

Wow ..Thx a lot

alexsmail said...

Wow! You save me a lot of time!

Alfredo said...

Thanks. This saved me hours of frustration!.

TJ said...

Thanks! That is exactly what I needed.

romita said...

thanks a lot .............

Bogdan Condurache said...
This comment has been removed by the author.
bman said...

@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), "");

Sascha said...

Thanks a lot for sharing!!