0

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

3
  • 1
    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. ??? I see that you use TEST_TABLE_2 as existing static table. The table which is getting created on the fly in this procedure and will be getting dropped is CTE table CTE_TEST_TABLE_DATE.
    – Akina
    Jan 13, 2020 at 8:34
  • 1
    PLS-00905 object SYSTEM.TEST_PROC is invalid Oracle tells that the object named TEST_PROC in SYSTEM namespace is invalid (I think it is invalid for to execute it as a procedure, but I could be wrong in that point), and says nothing about any table.
    – Akina
    Jan 13, 2020 at 8:36
  • 1
    Are you dropping test_table_1 too? If any referencing objects in procedure does not exist or dropped at compile time or run time the object becomes invalid.Check if test_table_1 exists then compile procedure.How are you handling exceptions?
    – user168186
    Jan 13, 2020 at 9:15

4 Answers 4

4

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.

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.

1

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.

2
  • How to use execute immediate with into clause.. Jan 13, 2020 at 14:38
  • 1
    thats exactly the part that you need to reformulate on your original approach ...
    – eagle275
    Jan 13, 2020 at 14:59
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.