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;
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.
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.
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.
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.
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.
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;
/
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With