Table doesn't exists issue with Oracle stored procedure

Question

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

Answer 1

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.

Answer 2

Oracle is not the same as SQL Server. Almost certainly, creating a table on-the-fly, populating it and then dropping it is the wrong approach to take in Oracle.

Why not simply have whatever it is that you're using to populate the table as a subquery in the CTE part of your insert-as-select? That's likely to be far more efficient.

E.g.:

INSERT INTO test_table_main (col1, col2, col3)
WITH     test_table_2 AS (<subquery that previously populated the old test_table_2 table>),
  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;

If you absolutely must have a table to store the results into, create a global temporary table (GTT) in the database, and then you have a table that is always there, but only shows data per session (e.g. session 1 can only see session 1 data, session 2 can only see session 2 data, etc).

That does away with the need for dynamic SQL, and the need to create/drop a table each time the code is run.

However, I would recommend going for a single SQL statement if at all possible, rather than temporarily storing data in a table.

Answer 3

I am not entirely certain if this http://doc.nuodb.com/Latest/Content/Creating-Database-Objects-in-Stored-Procedures.htm affects your problem - but you can at least try to build around your problem by splitting your procedure into 2 steps ..

Some alternatives for working around this issue:

-Split the stored procedure into two separate stored procedures as follows: The first stored procedure creates the database objects. This stored procedure must be compiled and executed first or the second stored procedure will fail to compile. The second stored procedure contains all SQL SELECT and DML statements. As long as the first stored procedure has been compiled and executed, this second stored procedure will compile and execute successfully.

-In the stored procedure, use an EXECUTE IMMEDIATE statement to execute the SQL SELECT and DML statements that refer to the object just created. All EXECUTE IMMEDIATE statements are compiled at runtime. Therefore, the CREATE PROCEDURE or ALTER PROCEDURE commands do not fail due to any database objects not being valid.

If this stored procedure is to be executed multiple times without dropping the database object, the stored procedure will need an input parameter passed at execution time to control this behavior.

-Create nested stored procedures such that the first stored procedure, when it executes, will drop and then create the database object. The first stored procedure then calls the second stored procedure, which references the database object using SQL DML statements. The requirement of this option is that the database object that is referenced needs to be created, albeit temporarily, prior to creating and compiling the stored procedure that references it. Once the stored procedures are created, the database object can be dropped at any time. When the database object is dropped, either by the DROP TABLE command, or by executing the first stored procedure, the second stored procedure, which references the dropped database object, becomes invalid. Even after the database object is recreated in the first stored procedure, this second stored procedure remains invalid. It is the EXECUTE statement in the first stored procedure that will cause the second stored procedure to become valid and execute successfully.

Answer 4

I think the procedure is getting dropped saying it couldnt find the procedure name in the Oracle dictionary.

Could you please elaborate on the procedure that you use or the full code.

Thanks