Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Unanswered: PL-Sql compilation Error

    I have this part of code in a stored procedure.

    DECLARE
    CURSOR x IS SELECT * FROM z;

    BEGIN
    FOR rec IN x LOOP
    BEGIN
    PROCESS CODE;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('something is wrong with the rec');
    END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Finish');
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in the big loop '||sqlerrm);

    END;
    /

    [I get this errors and I can't figure out why]

    Compilation errors for PROCEDURE y

    Error: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

    begin declare end 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 execute commit forall
    <a single-quoted SQL string>
    Line: 9 --The line number may be wrong
    Text: EXCEPTION

    Error: PLS-00103: Encountered the symbol "END" when expecting one of the following:

    begin function package pragma procedure form
    Line: 14 --The line number may be wrong
    Text: END LOOP;


    Please Help
    Mo

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PL-Sql compilation Error

    Well, there is no syntax error in your code (apart from "PROCESS CODE"). I changed "PROCESS CODE;" to "NULL;" and "FROM z" to "FROM mytable", and it compiled and ran successfully. So the problem must lie in the code that you didn't post here!

    What IS wrong with the code is that you are trapping and then effectively ignoring exceptions. If any exception is raised, your main EXCEPTION block will write a message to standard output, but it will not roll back the work. There will possibly be uncommitted changes leaving your transaction in an inconsistent state (depending on what "PROCESS CODE" actually does). It would be preferable not to have the main EXCEPTION block at all and just let PL/SQL raise the exception.

  3. #3
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Thumbs down Still got the error Andrew

    I commented all the code in the proceduer and left the part below and still got the message below.

    CREATE OR REPLACE PROCEDURE abc

    BEGIN
    null;
    BEGIN
    null;
    EXCEPTION
    WHEN OTHERS THEN
    null;
    END;

    EXCEPTION
    WHEN OTHERS THEN
    null;
    END abc;



    Compilation errors for PROCEDURE abc

    Error: PLS-00103: Encountered the symbol "EXCEPTION" 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 execute commit forall
    <a single-quoted SQL string>
    Line: 7
    Text: EXCEPTION
    Mo

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Still got the error Andrew

    You need to add "IS" or "AS" on the first line:

    CREATE OR REPLACE PROCEDURE abc IS

    or

    CREATE OR REPLACE PROCEDURE abc AS

  5. #5
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106

    Question

    I Had IS there but forget to paste it, sorry.
    Mo

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by mkasem
    I Had IS there but forget to paste it, sorry.
    Well when I added IS to your code, it compiled OK:

    SQL> l
    1 CREATE OR REPLACE PROCEDURE abc IS
    2
    3 BEGIN
    4 null;
    5 BEGIN
    6 null;
    7 EXCEPTION
    8 WHEN OTHERS THEN
    9 null;
    10 END;
    11
    12 EXCEPTION
    13 WHEN OTHERS THEN
    14 null;
    15* END abc;
    SQL> /

    Procedure created.

    SQL> show error
    No errors.

    So I don't know why you are getting a problem! Maybe there's something else you didn't paste as well?

  7. #7
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    I am starting to think that it is the software I am using to compile. I use PL-SQL Developer. May be it does not understand this code format.
    Mo

  8. #8
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    Still no luck.
    Mo

  9. #9
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    You are using too many BEGIN & END's in your code.

    Could you do something simplier?? Here is what is compiling without errors:

    SQL> DECLARE
    2
    3 v_test VARCHAR2(30);
    4 CURSOR x IS SELECT * FROM dba_tables;
    5
    6 BEGIN
    7 FOR rec IN x LOOP
    8 v_test := rec.table_name;
    9
    10 END LOOP;
    11
    12
    13 EXCEPTION
    14 WHEN OTHERS THEN
    15 DBMS_OUTPUT.PUT_LINE('Error in the big loop '||sqlerrm);
    16
    17 END;
    18 /

    PL/SQL procedure successfully completed.

    SQL>



    Hope that helps,

    clio_usa
    OCP - DBA

  10. #10
    Join Date
    Oct 2002
    Location
    Hartford, CT
    Posts
    106
    Thank you all for your help, I did exactly that and it helped me pin point the errors. I was able to find some syntax errors in the middle of the code that was causing the error but the exception could not raise it. And I guess this why the compiler was flagging the exception line for errors.

    Again, thanks a lot and have a nice thanks giving if you are in the US.
    Mo

  11. #11
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    Thanks for the feedback. Many people once get the tip or fix, don't even post if it worked or not, so the others know that was validated and proven to work.

    Any way, glad you got it working and good luck.


    Hope that helps,

    clio_usa
    OCP - DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •