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