Creating and reading from a table in the same SQL script
I am developing a SQL script in SQL Developer which will obfuscate personal data in a schema using Oracle SQL. The script looks into a table called "OBFUS_TABLE" which contains a list of which tables and columns need to be obfuscated and how. It then loops through the table, altering the data as it goes.

I have tested the actual loop and obfuscate process and it works fine, I have also successfully tested the beginning of the script up to just before the loop, which creates OBFUS_TABLE and inserts the values into it. The problem comes when it tries to do the two together, failing on a "table or view does not exist" error when it attempts to execute the loop. Snippet of code below:

        alter session set current_schema = SYSTEM;

        DECLARE
          t_count                   NUMBER;
          t_count2                  NUMBER;
          p_tname                   VARCHAR2(100);
          p_cname                   VARCHAR2(100);
          l_datatype                VARCHAR2(100);
        BEGIN
        
          SELECT COUNT(*) INTO t_count FROM all_tables  WHERE table_name = 'OBFUS_TABLE';
          SELECT COUNT(*) INTO t_count2 FROM all_tables  WHERE table_name = 'OBFUS_LOG';
        
          IF (t_count = 0)
            THEN
              EXECUTE immediate 'create table OBFUS_TABLE( TABLENAME VARCHAR2(200 BYTE), COLUMNNAME VARCHAR2(200 BYTE), DATA_TYPE VARCHAR2(20 BYTE), ACTIVE  VARCHAR(1 BYTE)  )';
          END IF;

          IF (t_count2 = 0)
            THEN
              EXECUTE immediate 'CREATE TABLE OBFUS_LOG (SRC_TABLENAME VARCHAR2(50 BYTE), SRC_TABLE_ROW_COUNT NUMBER, COPY_TABLENAME VARCHAR2(50 BYTE), COPY_TABLE_ROW_COUNT NUMBER, UPDATE_DATE TIMESTAMP(6) )';
          END IF;

        EXECUTE immediate 'INSERT INTO OBFUS_TABLE VALUES (''OB_MYTABLE1'',''SRNM'',''NAME'',''Y'')';
        COMMIT;


          FOR x IN (SELECT TABLENAME, COLUMNNAME, DATA_TYPE FROM OBFUS_TABLE WHERE ACTIVE='Y')
          LOOP
            p_tname    := upper(x.TABLENAME);  -- Table name
            p_cname    := upper(x.COLUMNNAME); -- Column name
            l_datatype := upper(x.DATA_TYPE);
            dbms_output.put_line('Started: '||TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI:SS'));
          END LOOP;
  
        END;


NB: There are actually around 30 insert statements in exactly the same format as the one above. I removed them since they would pad out this post too much, but I have manually checked every insert statement and they're all correct.

I assume the problem is that SQL Developer does a "sanity check" on the code before running, and looks ahead to the loop and realises OBFUS_TABLE doesn't exist, but fails to understand that by the time that piece of code is executed, OBFUS_TABLE will definitely exist.

Is there a way to get around this? I thought maybe a GOTO statement might help but no luck. I would rather keep the solution as one single script rather than two seperate ones, but if the only way around this is to do so then I could do that I suppose. Any help would be much appreciated.
Creating and reading from a table in the same SQL script
===

I am developing a SQL script in SQL Developer which will obfuscate personal data in a schema using Oracle SQL. The script looks into a table called "OBFUS_TABLE" which contains a list of which tables and columns need to be obfuscated and how. It then loops through the table, altering the data as it goes.

I have tested the actual loop and obfuscate process and it works fine, I have also successfully tested the beginning of the script up to just before the loop, which creates OBFUS_TABLE and inserts the values into it. The problem comes when it tries to do the two together, failing on a "table or view does not exist" error when it attempts to execute the loop. Snippet of code below:

        alter session set current_schema = SYSTEM;

        DECLARE
          t_count                   NUMBER;
          t_count2                  NUMBER;
          p_tname                   VARCHAR2(100);
          p_cname                   VARCHAR2(100);
          l_datatype                VARCHAR2(100);
        BEGIN
        
          SELECT COUNT(*) INTO t_count FROM all_tables  WHERE table_name = 'OBFUS_TABLE';
          SELECT COUNT(*) INTO t_count2 FROM all_tables  WHERE table_name = 'OBFUS_LOG';
        
          IF (t_count = 0)
            THEN
              EXECUTE immediate 'create table OBFUS_TABLE( TABLENAME VARCHAR2(200 BYTE), COLUMNNAME VARCHAR2(200 BYTE), DATA_TYPE VARCHAR2(20 BYTE), ACTIVE  VARCHAR(1 BYTE)  )';
          END IF;

          IF (t_count2 = 0)
            THEN
              EXECUTE immediate 'CREATE TABLE OBFUS_LOG (SRC_TABLENAME VARCHAR2(50 BYTE), SRC_TABLE_ROW_COUNT NUMBER, COPY_TABLENAME VARCHAR2(50 BYTE), COPY_TABLE_ROW_COUNT NUMBER, UPDATE_DATE TIMESTAMP(6) )';
          END IF;

        EXECUTE immediate 'INSERT INTO OBFUS_TABLE VALUES (''OB_MYTABLE1'',''SRNM'',''NAME'',''Y'')';
        COMMIT;


          FOR x IN (SELECT TABLENAME, COLUMNNAME, DATA_TYPE FROM OBFUS_TABLE WHERE ACTIVE='Y')
          LOOP
            p_tname    := upper(x.TABLENAME);  -- Table name
            p_cname    := upper(x.COLUMNNAME); -- Column name
            l_datatype := upper(x.DATA_TYPE);
            dbms_output.put_line('Started: '||TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI:SS'));
          END LOOP;
  
        END;


NB: There are actually around 30 insert statements in exactly the same format as the one above. I removed them since they would pad out this post too much, but I have manually checked every insert statement and they're all correct.

I assume the problem is that SQL Developer does a "sanity check" on the code before running, and looks ahead to the loop and realises OBFUS_TABLE doesn't exist, but fails to understand that by the time that piece of code is executed, OBFUS_TABLE will definitely exist.

Is there a way to get around this? I thought maybe a GOTO statement might help but no luck. I would rather keep the solution as one single script rather than two seperate ones, but if the only way around this is to do so then I could do that I suppose. Any help would be much appreciated.