I was recently approached by a developer who showed me a piece of code that raised ORA-06550 exception for “no apparent reason”. Here is a simplified version of that code:
DECLARE v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM user_sequences WHERE sequence_name='SEQ'; IF v_cnt=0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1'; END IF; DBMS_OUTPUT.PUT_LINE(seq.NEXTVAL); END;
The logic behind this PL/SQL block is quite apparent: check if sequence “SEQ” exists; create it if it does not exist, and then (when it definitely exists) call its NEXTVAL attribute.
This code will only work if the sequence already exists before you run this block. If it does not exist, the code will not compile – it will not be executed at all, because the line “DBMS_OUTPUT.PUT_LINE(seq.NEXTVAL);” – references seq object that does not yet exist.
The fix is very simple – if you create the object dynamically, you can only reference it in dynamic SQL (or PL/SQL) in the same block:
DECLARE v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM user_sequences WHERE sequence_name='SEQ'; IF v_cnt=0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1'; END IF; EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(seq.NEXTVAL); END;'; END;
Note, that our dynamic PL/SQL is a block itself as it runs in its own context. If you try the following line, it will throw an exception:
EXECUTE IMMEDIATE 'DBMS_OUTPUT.PUT_LINE(seq.NEXTVAL);';
ORA-00900: invalid SQL statement ORA-06512: at line 10 ORA-06512: at "SYS.DBMS_SQL", line 1721
If you find this post useful, please press the LIKE button and subscribe.
My Oracle Group on Facebook:
Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.