Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 657
Tom Avatar asked Dec 21 '09 19:12

Tom


People also ask

How do you declare a cursor variable?

A cursor variable is like an explicit cursor that is not limited to one query. To create a cursor variable, either declare a variable of the predefined type SYS_REFCURSOR or define a REF CURSOR type and then declare a variable of that type. You cannot use a cursor variable in a cursor FOR LOOP statement.

How do you make an implicit cursor?

Whenever a DML statement like INSERT, UPDATE or DELETE is executed, an implicit cursor is generated by default, to process these statements. It is also created when a SELECT statement returning only a single row is executed.

How do I declare a Rowtype variable in PL SQL?

The %ROWTYPE attribute, used to declare PL/SQL variables of type record with fields that correspond to the columns of a table or view, is supported by the Db2® data server. Each field in a PL/SQL record assumes the data type of the corresponding column in the table. A record is a named, ordered collection of fields.

Can we use %type in cursor?

You can use %TYPE to provide the datatype of a record variable. Also, in the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable.


2 Answers

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;
/
like image 81
Peter Lang Avatar answered Sep 22 '22 14:09

Peter Lang


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

like image 32
David Aldridge Avatar answered Sep 20 '22 14:09

David Aldridge