KirkGray
asked on
Trigger DDL Fails with PLS-00103: Encountered the symbol ""
I am creating an oracle database using ODBC.. everything works except the triggers.. they all produce this error:
PLS-00103: Encountered the symbol "" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql commit <a single-quoted SQL string>
The symbol "" was ignored.
I have not fooled with this message. Oracle does not like the symbol it identifies as "" (Nothing!)
The strange thing is, when I extract the (Invalid) trigger script using TOAD and post it back into oracle it compiles without errors. Anybody got any ideas about why it's failing using ODBC ?
PLS-00103: Encountered the symbol "" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql commit <a single-quoted SQL string>
The symbol "" was ignored.
I have not fooled with this message. Oracle does not like the symbol it identifies as "" (Nothing!)
The strange thing is, when I extract the (Invalid) trigger script using TOAD and post it back into oracle it compiles without errors. Anybody got any ideas about why it's failing using ODBC ?
There is a big difference in Oracle between sigle and double quote. Double quotes are used to enclose IDENTIFIERS in order to preserve identifier name exactly as it is typed in (casing, characters otherwise not allowed by Oracle name syntax, etc.) Single quote is used to enclose character strings. Therefore, Oracle will interpret '' (single quote, single quote) as empty string - NULL, while "" (double quote, double quote) will be interpreted as empty identifier name and will cause syntax error. I suggest to replace "" with '' and try again. About TOAD. It could be that TOAD has some build-in intelligence around the issue.
Solomon Yakobson
Solomon Yakobson
Hi KirkGray.
As syakobson mentioned, I do NOT think there
is any double quotes in your program. Oracle
displays the unexpected character or keywords
in double quotes while displaying the error.
- Vish
Hi Again KirkGray.
You can also simulate this error, write
a test trigger on test table, and in the
trigger body, just before say 'BEGIN' or
'END' keyword, include one character of
ascii value 1. Try to create the trigger,
you will get the EXACT error!
I strongly suspect that the problem you
are facing is due to a special character
introduced by the generator program,TOAD
works fine, probably it doesnt recognize
this special character :)
- Vish
Hi Again KirkGray.
You can also simulate this error, write
a test trigger on test table, and in the
trigger body, just before say 'BEGIN' or
'END' keyword, include one character of
ascii value 1. Try to create the trigger,
you will get the EXACT error!
I strongly suspect that the problem you
are facing is due to a special character
introduced by the generator program,TOAD
works fine, probably it doesnt recognize
this special character :)
- Vish
ASKER
Sorry, dude.. way off.. There are no double quotes in the trigger code... And I am well aware of the functionallity of double quotes.
vishone: I will test your theory.. I will try the same DDL from Access using a passthrouth query..
vishone: I will test your theory.. I will try the same DDL from Access using a passthrouth query..
Well, dude, post your code.
Solomon Yakobson.
Solomon Yakobson.
ASKER
CREATE TRIGGER TRGDELSITES
After delete ON SITES
FOR EACH ROW
BEGIN
delete from results where siteid = :old.siteid;
END;
Heres an example of one of the triggers that fail to compile when using ODBC. In this case the trigger implements a cascading delete..
Vish: Tried the same calls from Access using Passthrough via ODBC.. it gets the same symtoms...that failed also... What I need is a tool that will trace the sql comming into the server. There MUST be a difference between what is comming out of the ODBC layer to the server. Any More Ideas anyone? I'm just about to try to get an updated ODBC Driver from Oracle. I hope that works, 'cause if it does not I'm going to have to punch a hole through my so far seemless platform independance layer and use Direct Oracle Access (Net80). Wich would sadden me.
Thanx for the help.. any other ideas let me know....
After delete ON SITES
FOR EACH ROW
BEGIN
delete from results where siteid = :old.siteid;
END;
Heres an example of one of the triggers that fail to compile when using ODBC. In this case the trigger implements a cascading delete..
Vish: Tried the same calls from Access using Passthrough via ODBC.. it gets the same symtoms...that failed also... What I need is a tool that will trace the sql comming into the server. There MUST be a difference between what is comming out of the ODBC layer to the server. Any More Ideas anyone? I'm just about to try to get an updated ODBC Driver from Oracle. I hope that works, 'cause if it does not I'm going to have to punch a hole through my so far seemless platform independance layer and use Direct Oracle Access (Net80). Wich would sadden me.
Thanx for the help.. any other ideas let me know....
ASKER
for your information: Here are the relevent error records from user_errors:
SEQUENCE LINE POSITION TEXT
1 1 6 PLS-00103: Encountered the symbol "" when expecting one of the following:
2 2 48 PLS-00103: Encountered the symbol "" when expecting one of the following:
SEQUENCE LINE POSITION TEXT
1 1 6 PLS-00103: Encountered the symbol "" when expecting one of the following:
2 2 48 PLS-00103: Encountered the symbol "" when expecting one of the following:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well done man...
I was looking for sql trace.. where do I get one?
I was looking for sql trace.. where do I get one?
Sorry,
I wrote sql trace, while having in mind SQL*Net trace. To get SQL*Net trace for the client side, edit SQLNET.ORA file in your ORACLE_HOME\Net8\Admin directory and add line TRACE_LEVEL_CLIENT=16 (the highest level) and TRACE_LEVEL_DIRECTORY=trac
Solomon Yakobson.
I wrote sql trace, while having in mind SQL*Net trace. To get SQL*Net trace for the client side, edit SQLNET.ORA file in your ORACLE_HOME\Net8\Admin directory and add line TRACE_LEVEL_CLIENT=16 (the highest level) and TRACE_LEVEL_DIRECTORY=trac
Solomon Yakobson.
Check previous tokens as well as the one given in the
error message. The line and column numbers given in the
error message refer to the end of the faulty language
construct.
I think this could be due to any special character
in the code, probably added by the the generator program
you are using.