How to dynamically create and immediately use a sequence in PL/SQL code

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

A lesson from this mistake is very simple and important so we shall repeat the rule:

If you create an object dynamically, you can only reference this object in dynamic SQL (or PL/SQL) in the same block.

This applies to all kinds of objects. For example, if you create a table in your procedure, you may only select from this table in dynamic SQL. If you dynamically add a column to a table, you can only update it in dynamic SQL, etc.

***

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”.