WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('something is wrong with the rec');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in the big loop '||sqlerrm);
[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
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;
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.
You are using too many BEGIN & END's in your code.
Could you do something simplier?? Here is what is compiling without errors:
3 v_test VARCHAR2(30);
4 CURSOR x IS SELECT * FROM dba_tables;
7 FOR rec IN x LOOP
8 v_test := rec.table_name;
10 END LOOP;
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE('Error in the big loop '||sqlerrm);
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.