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

Question

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.

You get the exception since you actually run the code. Replace your `declare` by a `CREATE PROCEDURE test_cursor AS` to compile the code. — Peter Lang, Dec 21 '09 at 20:01
"DECLARE..BEGIN..END;/" means "compile and run this anonymous block". This is why you're getting ORA-00942. CREATE PROCEDURE ...;/" means "compile and create this procedure". You won't get ORA-00942 then, until you call the procedure. — Jeffrey Kemp, Dec 22 '09 at 05:29

score 8 · Accepted Answer · edited Dec 21 '09 at 21:30

8

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

edited Dec 21 '09 at 21:30

Gary Myers

34,963
3
49
74

answered Dec 21 '09 at 19:34

Peter Lang

54,264
27
148
161

Thanks, but dual is an existing table. I need to do that for a not existing table at run time. – Tom Dec 21 '09 at 19:38
typo there, its compile time. – Tom Dec 21 '09 at 19:40
I'm not sure what you mean. You can also change the provided source by 'SELECT * FROM some_not_existing_table'. It will compile fine and break at runtime if the table still does not exist. – Peter Lang Dec 21 '09 at 19:44
I'm getting a 'table or view does not exist' in sqlplus – Tom Dec 21 '09 at 19:51
IMO it should work as expected. Tom, I think you are doing it wrong somehow. – Erich Kitzmueller Dec 21 '09 at 19:57
@ammoQ I agree it compiles when using dual, but not when using an unexisting table. I added ann example to my question – Tom Dec 21 '09 at 20:03
A stored procedure does compile. CREATE OR REPLACE PROCEDURE test IS c SYS_REFCURSOR; BEGIN OPEN c FOR 'SELECT * FROM fdfdfdfdfd'; CLOSE c; END; / It will not execute, and a top-level anonymous block will fail when it tries to execute – Gary Myers Dec 21 '09 at 21:29

score 5 · Answer 2 · answered Dec 21 '09 at 22:55

5

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

answered Dec 21 '09 at 22:55

David Aldridge

51,479
8
68
96

1

+1 Global Temporary Tables are so much better than creating/destroying tables on the fly. Creating tables on the fly is much more error-prone and harder to maintain. – Klas Lindbäck May 03 '11 at 08:04

score 3 · Answer 3 · answered Dec 21 '09 at 19:58

3

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.

answered Dec 21 '09 at 19:58

Erich Kitzmueller

36,381
5
80
102

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

update

3 Answers3