| Interaction between VB and Oracle Procedure using Cursor as return type [message #36989] |
Wed, 09 January 2002 06:27  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
