Home » Developer & Programmer » Forms » Using Ref Cursors in Forms 6i
Using Ref Cursors in Forms 6i [message #191338] Tue, 05 September 2006 15:28 Go to next message
pbeach
Messages: 1
Registered: September 2006
Junior Member
I'm sure someone somewhere has already addressed this question. But i'll be darn if I could located it.

I have a database package (called Banner_Data) with a function that returns a ref cursor. The function creates dynamic sql and initiates the open of the cursor.

I have a forms 6i that makes a call to the function, with the intent of fetching the data associated with the ref cursor.

When I execute the fetch, I get ora-01001 Invalid Cursor. I've made the query extremely simple and I get the same error. My function now looks like:
TYPE T_REF_CURSOR is REF CURSOR is defined in the package.

FUNCTION TEST_A1 RETURN banner_data.t_ref_CURSOR is

c_cursor banner_data.t_ref_CURSOR;

begin
 OPEN c_cursor FOR 'select sysdate, 1,2,3 from dual';
 return( c_cursor );
end test_a1;

Inside my Forms 6i procedure I have:
banner_data_cursor  BANNER_DATA.T_REF_CURSOR;
a		    DATE;
c1		    NUMBER;
c2		    NUMBER;
c3		    NUMBER;

BEGIN

IF (banner_data_cursor%ISOPEN) THEN
  CLOSE banner_data_cursor;	
END IF;

banner_data_cursor := TEST_A1;
LOOP
  FETCH banner_data_cursor INTO a,c1,c2,c3;
  IF (banner_data_cursor%NOTFOUND) THEN
	EXIT;
  END IF;
END LOOP;
CLOSE banner_data_cursor;

IT ALWAYS ERRORS ON THE FETCH. I've ran a produre at the database level (pl/sql) and it works fine.

Is my problem that forms 6i don't like me using ref cursors?
I found if I strongly typed the cursor to match the data coming back then it works fine. But then I can't use dynmic sql in the database package and I'm forced to always return the same data.

Any advice?

[Updated on: Mon, 11 September 2006 02:32] by Moderator

Report message to a moderator

Re: Using Ref Cursors in Forms 6i [message #191765 is a reply to message #191338] Fri, 08 September 2006 01:33 Go to previous message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Modify ur Forms 6i procedure like
banner_data_cursor BANNER_DATA.T_REF_CURSOR;
v_banner_data_cursor  banner_data_cursor%rowtype;
BEGIN
  IF (Banner_Data_CurSor%ISOPEN) THEN
    CLOSE Banner_Data_CurSor;
  END IF;
  
  Banner_Data_CurSor := Test_a1;
  
  LOOP
    FETCH Banner_Data_CurSor INTO v_Banner_Data_CurSor;
    
    IF (Banner_Data_CurSor%NOTFOUND) THEN
      EXIT;
    END IF;
  END LOOP;
  
  CLOSE Banner_Data_CurSor;
END;
and try to run, but i m not very much sure about this.

Sandy

[Updated on: Mon, 11 September 2006 02:35] by Moderator

Report message to a moderator

Previous Topic: Calling Report from Menu (Oracle Forms 10g
Next Topic: Get Backup
Goto Forum:
  


Current Time: Sun Dec 04 16:34:22 CST 2016

Total time taken to generate the page: 0.09369 seconds