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/
Suggested Reading:
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”.