Oracle Forms (MOSC)

MOSC Banner

Strange behaviour of REF CURSOR variable

edited Aug 16, 2010 9:46PM in Oracle Forms (MOSC) 5 commentsAnswered
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:
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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center