Using Ref Cursors in Forms 6i [message #191338] |
Tue, 05 September 2006 15:28 |
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 |
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
|
|
|