Table doesn't exists issue with Oracle stored procedure I am new to Oracle and in process of converting SQL Server stored procedures into Oracle stored procedures.
I was able to transform the SQL statements and have tested them individually and they are working fine as expected.

However facing some challenges when trying to execute them in Oracle stored procedures.

For example I have a stored procedure TEST_PROC wherein I am creating a TEST_TABLE_2 using existing table TEST_TABLE_1

BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE TEST_TABLE_2 as
 SELECT 
 COL1,
 COL2,
 COL3
 FROM TEST_TABLE_1';
END;


and then inserting data to some other table with the help of table TEST_TABLE_2

INSERT INTO TEST_TABLE_MAIN
(
COL1,
COL2,
COL3
)
WITH CTE_TEST_TABLE_DATE AS (
SELECT 
a.COL1,
a.COL2,
a.COL3
FROM TEST_TABLE_2 a LEFT JOIN
 (SELECT GROUP_ID FROM TEST_REF_TABLE) b
 on a.COL1= b.GROUP_ID
)
SELECT * FOM CTE_TEST_TABLE_DATE;


As you can see that TEST_TABLE_2 is getting created on the fly in this procedure
and will be getting dropped in next subsequent procedure.

when I am running this TEST_PROC I am facing below issue which is probably due to that table doesn't exists.

exec TEST_PROC;



  PLS-00905 object SYSTEM.TEST_PROC is invalid
The table does not exist at compile time, so with static SQL, the procedure remains invalid.

Run the INSERT from dynamic SQL as well (execute immediate).

Or even better, do not create tables on the fly.