Strange behaviour of REF CURSOR variable
Hello,
We are observing very strange behaviour from passing REF CURSOR between database and Forms 10.1.2 . We have an application where blocks are based on stored procedures. We dynamically build filters for queries based on user input. We open a weak ref cursor in database and then assign it to return ref cursor of a procedure which is strong. It works for blocks that are based on stored procedure. But when we use the same procedure in PL/SQL in Forms it fails with ORA-01001 during fetch.
Here is a test database package:
Here is a test database package:
create or replace package test_pkg is -- record TYPE t_dual_rec IS RECORD (a dual.dummy%TYPE); -- cursor TYPE t_dual_cur IS REF CURSOR RETURN t_dual_rec; -- test procedure PROCEDURE test_cast_cursor(p_cur IN OUT t_dual_cur); end; / CREATE OR REPLACE PACKAGE BODY test_pkg IS -- test procedure PROCEDURE test_cast_cursor(p_cur IN OUT t_dual_cur) IS v_cur SYS_REFCURSOR; BEGIN OPEN v_cur FOR 'select dummy from sys.dual'; p_cur := v_cur; END; END; / -- test case set serveroutput on declare v_cur test_pkg.t_dual_cur; v_rec test_pkg.t_dual_rec; begin test_pkg.test_cast_cursor(v_cur); -- here it fails in Forms fetch v_cur into v_rec;
0