CODEWITHSUNDEEP
sqloracle-databaseplsqltoad
Deejay
Deejay

Reputation: 75

ORA-06550: ORA-00942: table or view does not exist error, when table is referenced on the same script that creates it

I'm trying to repopulate a table using a created backup table in the same script.
But it returns an error (ORA-06550: line 14, column 46: ORA-00942: table or view does not exist) when I run (F5) it on Toad.

Here is what the code looks like:

SET serveroutput ON

BEGIN

    EXECUTE IMMEDIATE 'CREATE TABLE SAMPLE_TABLE1_BACKUP AS SELECT * FROM SAMPLE_TABLE1';

    EXECUTE IMMEDIATE 'TRUNCATE TABLE SAMPLE_TABLE1';

    BEGIN 
    FOR rec IN (SELECT * FROM SAMPLE_TABLE1_BACKUP)
        LOOP
            INSERT INTO SAMPLE_TABLE1 (column1, column2, column3)
            VALUES (rec.column1, rec.column2, rec.column3);
        END LOOP rec;
    END;
                
    EXECUTE IMMEDIATE 'DROP TABLE SAMPLE_TABLE1_BACKUP';

END;

Upvotes: 0

Views: 653

Answers (1)

Littlefoot
Littlefoot

Reputation: 143088

Certainly, it won't work. Table doesn't exist at compile time. INSERT will also have to be dynamic.

Upvotes: 1

Related Questions