Home » SQL & PL/SQL » SQL & PL/SQL » Interaction between VB and Oracle Procedure using Cursor as return type
Interaction between VB and Oracle Procedure using Cursor as return type [message #36989] Wed, 09 January 2002 06:27 Go to next message
Bhagwan Singh
Messages: 23
Registered: December 2001
Junior Member
Hi,
Iam having a peculiar problem.We are using VB6.0 as front end and Oracle 8.1.7 as Backend.
My package below returns a cursor object for any kind of static/dynamic query which I attach to a VB resultset front end.

However, When I see the Recordcount property of VB Recordset using MessageBox function, It gives -1.
Is there any way by which I can modify my Database Procedure and return a Counter alongwith cursor object which shows the number
of rows affected in the cursor?

I can modify my VB front end by simply declaring an integer at VB and running it as under.But I don't want to do that.
While (Not Rs.EOF)
i = i+1
Rs.MoveNext
Wend

Is there any way out by which I can catch hold of cursor row count?
My existing Package is as under.Please let me know the mofications it requires.

CREATE OR REPLACE PACKAGE DEPARTMENT AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE GET_EMPS(o_result_set OUT CURSOR_TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY DEPARTMENT AS
PROCEDURE GET_EMPS(o_result_set OUT CURSOR_TYPE)
AS
BEGIN
OPEN o_result_set FOR SELECT action_type_desc FROM TB_ACTION_TYPE;
END;
END department;

Thanks and Regards,
Bhagwan Singh Mer
Re: Interaction between VB and Oracle Procedure using Cursor as return type [message #36996 is a reply to message #36989] Wed, 09 January 2002 12:11 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
it is not possible to get record count when you get result set as refcursor.

work around is..

declare out parameter in vb and fetch number of records into out parameter in Stored proc before opening ref cursor.
here is modified code..

CREATE OR REPLACE PACKAGE DEPARTMENT AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE GET_EMPS(reccount out number,o_result_set OUT CURSOR_TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY DEPARTMENT AS
PROCEDURE GET_EMPS(recount out number,o_result_set OUT CURSOR_TYPE)
AS
BEGIN
select count(*) into reccount FROM TB_ACTION_TYPE;
OPEN o_result_set FOR SELECT action_type_desc FROM TB_ACTION_TYPE;
END;
END department
Previous Topic: TROUBLE WITH ORA-22992
Next Topic: Re: Interaction between VB and Oracle Procedure using Cursor as return type
Goto Forum:
  


Current Time: Thu Mar 28 07:37:20 CDT 2024