I have a long DDL data migration script which has 15 transaction blocks. For each block, it takes data from one table and then inserts into another table.
But now I have come across a scenario in which the database does not have that table. In this case my script does not compile even.
What I actually want is to check and return early if the table does not exist. If it does, then only continue further execution.
But the script does not even compiles since it has statement which contains a table name which is non-existent in that particular database.
After some googling, I found that we can use those statements in EXECUTE IMMEDIATE block. I have tried this, but haven't been able to get it compiled.
Here is the block -
DECLARE
V_OBJECT_NAME1 VARCHAR2(50);
V_STRING VARCHAR2(1000);
V_CONTINUE Boolean;
BEGIN
V_CONTINUE := true;
SELECT TABLE_NAME INTO V_OBJECT_NAME1 FROM USER_TABLES WHERE TABLE_NAME = 'OOLD_SFWID_CHG_LOG_HEADER';
EXCEPTION WHEN NO_DATA_FOUND THEN
V_CONTINUE := false;
IF V_CONTINUE then
--scubbber code
V_STRING := 'DECLARE
CURSOR C1 IS
SELECT * FROM OOLD_SFWID_CHG_LOG_HEADER;
BEGIN
OPEN C1;
CLOSE C1;
END;';
EXECUTE IMMEDIATE V_STRING
END IF;
END;
Please suggest how can we write it..
