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 ?

KirkGrayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vishoneCommented:

 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.

0
syakobsonCommented:
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
0
vishoneCommented:

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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

vishoneCommented:

 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
0
vishoneCommented:

 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
0
KirkGrayAuthor Commented:
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..
0
syakobsonCommented:
Well, dude, post your code.

Solomon Yakobson.
0
KirkGrayAuthor Commented:
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....

0
KirkGrayAuthor Commented:
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:
0
syakobsonCommented:
I did verify trigger creation from MS Access. It does fail with the error you indicated. I enabled sql trace and found the problem. It is caused by end-of-line difference between Access and Oracle. If query you issue in Access is multiline query, Access passes to Oracle end-of-line as 0D0A (CRLF - carriage return+line feed), while Oracle's end-of-line is 0A (line feed) only. Oracle SQL has no problem with it and simply ignores 0D, while PL/SQL does not like it. So all you need to do is type whole PL/SQL portion (BEGIN...END;) of CREATE TRIGGER command on one line. As soon as did it, trigger was created.

Solomon Yakobson.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KirkGrayAuthor Commented:
Well done man...

I was looking for sql trace.. where do I get one?



0
syakobsonCommented:
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=trace-file-location. Then any connection will produce trace written to trace file (one file for all connections made from the client). You will see all the packets. I compared Access CREATE TRIGGER packet to SQL*Plus one. Then I saw the difference.

Solomon Yakobson.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.