Oracle PLSQL - declare cursor over non-existent table

Question

I want to declare a cursor on a table that does not exist. Of course, my procedure does not compile.

This table is a temporary table and is created by the preliminary process. It will exist at runtime, but at compile time its a different story.

To select / update other DML operations I used

EXECUTE IMMEDIATE 'operation from tmp_table'

but I cannot find a workaround for cursors.

Is there any way?

Basically, I want this to compile

 drop table test; /*from this on should compile*/ DECLARE cursor c is select * from test; BEGIN for reg in c LOOP /*...*/ END LOOP; END; 

Update

Not compiling yet:

 SQL> declare 2 c sys_refcursor; 3 BEGIN 4 open c for 'select * from pepito'; -- 'pepito' does not exist 5 close c; 6 end; 7 / declare * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at line 4 

Must use CREATE PROCEDURE, thanks.

Thanks in advance.

Answer 1

You should define your cursor as follows:

 DECLARE c SYS_REFCURSOR; BEGIN OPEN c FOR 'SELECT * FROM dual'; CLOSE c; END; 

You can also bind arguments:

 OPEN c FOR 'SELECT * FROM dual WHERE DUMMY = :1' USING 'X'; 

---

See the Oracle OPEN-FOR Statement documentation for more information.

Stored Procedure Example

 CREATE OR REPLACE PROCEDURE test IS c SYS_REFCURSOR; BEGIN OPEN c FOR 'SELECT * FROM fdfdfdfdfd'; CLOSE c; END; / 

Answer 2

Creating temporary tables as needed is usually not considered good practice in Oracle, where global temporary tables are better and will not cause this problem.

Answer 3

You can use DBMS_SQL to get even more flexibility than the ref cursor method described by Peter Lang. But it also means a lot of work.