Why can't we use a strong cursor with a dynamic SQL statement?
I'm trying to use strong ref cur with dynamic sql stat, but it throws an error, but when I use weak cursor it works. Please explain what the reason is, and please forward me any oracle server architect link that contains a question on how compilation and parsing is done on an Oracle server. THIS is a bug along with the code.
ERROR at line 6:
ORA-06550: line 6, column 7:
PLS-00455: cursor 'EMP_REF_CUR' cannot be used in dynamic SQL OPEN statement
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored
declare
type ref_cur_type IS REF CURSOR RETURN employees%ROWTYPE; --Creating a strong REF cursor,employees is a table
emp_ref_cur ref_cur_type;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_ref_cur FOR 'SELECT * FROM employees';
LOOP
FETCH emp_ref_cur INTO emp_rec;
EXIT WHEN emp_ref_cur%NOTFOUND;
END lOOP;
END;
a source to share
Here is a routine with a strongly typed ref cursor:
SQL> create or replace procedure p1 is
2 type dept_rc is ref cursor return dept%rowtype;
3 my_ref_cursor dept_rc;
4 begin
5 open my_ref_cursor for
6 select * from dept;
7 end;
8 /
Procedure created.
SQL>
This next statement fails because the signature of the EMP record does not match the signature of the DEPT table.
SQL> create or replace procedure p1 is
2 type dept_rc is ref cursor return dept%rowtype;
3 my_ref_cursor dept_rc;
4 begin
5 open my_ref_cursor for
6 select * from emp;
7 end;
8 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: SQL Statement ignored
6/9 PLS-00382: expression is of wrong type
SQL>
But if we change the projection to match the DEPT table, we get success again:
SQL> create or replace procedure p1 is
2 type dept_rc is ref cursor return dept%rowtype;
3 my_ref_cursor dept_rc;
4 begin
5 open my_ref_cursor for
6 select deptno, ename, job from emp;
7 end;
8 /
Procedure created.
SQL>
So why can't we use a strongly typed ref cursor with dynamic SQL?
SQL> create or replace procedure p1 is
2 type dept_rc is ref cursor return dept%rowtype;
3 my_ref_cursor dept_rc;
4 begin
5 open my_ref_cursor for
6 'select * from dept';
7 end;
8 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: Statement ignored
5/10 PLS-00455: cursor 'MY_REF_CURSOR' cannot be used in dynamic SQL
OPEN statement
SQL>
As the compiler cannot parse the string in dynamic SQL expression. Therefore, it cannot assert that the columns in the query projection are the same in number and data type of the ref cursor signature. Hence, it cannot confirm the contract between the ref cursor variable and the request. It is even easier to understand why this is not possible when you consider that a dynamic SQL statement can be assembled from a USER_TAB_COLUMNS query.
a source to share
Another possibility is to declare and define a record type object as a container for your query results. This can be useful if the query is a JOIN query, returning columns from multiple joined tables.
SQL> create or replace procedure p1 is
/* Declare you destination data structure row container */
TYPE TestRecTyp IS RECORD (
deptno varchar(50),
ename varchar(50),
job varchar(50)
);
/* Define an instance of the record type */
testrec TestRecTyp;
type dept_rc is ref cursor; /*return dept%rowtype;*/
my_ref_cursor dept_rc;
begin
open my_ref_cursor for 'select deptno,ename,job from emp';
LOOP
FETCH my_ref_cursor INTO testrec;
EXIT WHEN my_ref_cursor%NOTFOUND;
/* Do some operations with testrec*/
END LOOP;
end;
NOTE. You can use the above method in a dynamically built SQL statement by replacing "select deptno, ename, job from emp" with a variable such as v_sql, and update that variable with an SQL statement within the procedure.
a source to share