Creating and reading from a table in the same SQL script

Question

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.

Tony Andrews · Accepted Answer · 2012-05-24 10:10:57Z

5

You will need to use dynamic SQL for the select like this:

declare
    ...
    l_tname    varchar2(100);
    l_cname    varchar2(100);
    l_datatype varchar2(100);
    rc         sys_refcursor;
begin
    ...
    open rc for 'SELECT TABLENAME, COLUMNNAME, DATA_TYPE  
                 FROM OBFUS_TABLE WHERE ACTIVE=''Y''';
    loop
        fetch rc into l_tname, l_cname, l_datatype;
        exit when rc%notfound;
        dbms_output.put_line('Started: '||TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI:SS'));
    end loop;
    close rc;
end;

edited May 24, 2012 at 10:10

answered May 23, 2012 at 16:46

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Neoptolemus Over a year ago

Ah! Thank you for the response. I'm still fairly new to SQL so I haven't even heard of dynamic SQL before, but I'll do a bit of research. It was driving me insane because the code in the loop does exactly the same thing I'm trying to do here: looks for a table, creates it if necessary and then reads and writes to it, and that never gave me any problems. It must be the way the code is structured in the loop. Never mind!

Neoptolemus Over a year ago

Ok, I've had a good crack but I can't get the code to work, and the sites I looked at online seem to be geared to someone a little more experienced and their code looks very different to yours. Would you be able to show me what the loop should look like in full? I sort of understand what the first part of your code is doing, but I'm having trouble passing the data in rc to the loop.

Neoptolemus Over a year ago

Thanks for the update. It now seems to be working, but considering that OBFUS_TABLE is only 4 columns and 30 rows big and all its doing it iterating through without any further action, the query seems to be taking an agonisingly long time to run. I tried aborting it the first time after 10 minutes and it just locked up SQL developer completely, forcing me to shut the laptop down. I've now left it around 20 minutes and it's still running. I think I may have to split my solution into two seperate scripts just to be on the safe side and maybe revisit this when I have more experience...

Tony Andrews Over a year ago

Oops - I have added a crucial missing line in the loop: exit when rc%notfound; !

Tony Andrews Over a year ago

... it was looping forever before.

|

Collectives™ on Stack Overflow

Creating and reading from a table in the same SQL script

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related