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.
PLS-00905 object SYSTEM.TEST_PROC is invalid