Oracle PLSQL - Declare a cursor on a non-existing table

Question

Hi,

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

This table is a temporary table, and is created by a pre process. It will exist on runtime, but at compile time its another story.

For my select / updates an other DML operations, I've used

EXECUTE IMMEDIATE 'operation from tmp_table'

but I can't find a workaround for cursors.

Is there a 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

So far not compiling:

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

Should use CREATE PROCEDURE, thanks.

Thanks in advance.

Answer 1

You should be able to define your cursor like this:

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';

---

For further information see the Oracle documentation of the OPEN-FOR Statement.

Example using a stored procedure

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

Answer 2

You can use DBMS_SQL to get even more flexibility than the ref cursor method described by Peter Lang. But it means more work, too.

Answer 3

Creating temporary tables as required is usually not considered good practice in Oracle, where Global Temporary Tables are better and would not cause this problem