| Procedure to return resultset [message #282520] |
Thu, 22 November 2007 04:26  |
toastmax
Messages: 16
Registered: November 2006
|
Junior Member |
|
|
I have create a package that contains a pipeline function, it is called with a select statement as so:
SELECT * FROM
TABLE(
PARSE_ATTENDANCE.ENUM_MARKS(
CURSOR(
SELECT STUD_ID, BASE_ID, START_DATE, MARKS
FROM DX_XML_ATTENDANCE WHERE STUD_ID = 9999999
and START_DATE >= TODATE('25/08/2007', 'DD/MM/YYYY')
This works fine, but I have been stuggling for some time to find a way to use this in production for Access and Crystal Reports, and I have found that you can use Stored Procedures in Crystal, so that is what I want to do.
What do I need to do to return a Table/Resultset in a procedure, the result set needed will have more than one column, this is what I have done so far, but I have got stumped :
CREATE OR REPLACE PROCEDURE SHOW_MARKS(S_ID IN NUMBER, S_DTE IN DATE, O_RS OUT TABLE)
AS
BEGIN
CURSOR C_TMP IS SELECT * FROM
TABLE(
PARSE_ATTENDANCE.ENUM_MARKS(
CURSOR(
SELECT STUD_ID, BASE_ID, START_DATE, MARKS
FROM DX_XML_ATTENDANCE WHERE STUD_ID = 9999999
and START_DATE >= TODATE('25/08/2007', 'DD/MM/YYYY');
BEGIN
--THIS IS THE BIT I AM NOT SURE OF.
FETCH C_TMP INTO O_RS;
EXIT WHEN C_TMP%NOTFOUND
END LOOP;
CLOSE C_TMP;
END;
The package PARSE_ATTENDANCE.ENUM_MARKS does return a user defined type, do I need to fetch the cursor into this instead.
Any ideas?
|
|
|
|
|
|
| Re: Procedure to return resultset [message #282747 is a reply to message #282525] |
Fri, 23 November 2007 04:59  |
toastmax
Messages: 16
Registered: November 2006
|
Junior Member |
|
|
Thanks for that, it seemed to to the trick, for reference heres the syntax, this procedure is wrapped inside a package :
PROCEDURE SHOW_MARKS(S_ID IN NUMBER, S_DTE IN DATE,
O_MKS OUT PARSE_ATTENDANCE.R_CURS)
AS
BEGIN
OPEN O_MKS FOR
SELECT * FROM
TABLE(
PARSE_ATTENDANCE.ENUM_MARKS(
CURSOR(
SELECT STUD_ID, BASE_ID, START_DATE, MARKS
FROM DX_XML_ATTENDANCE WHERE STUD_ID = S_ID
and START_DATE >= S_DTE
)));
END SHOW_MARKS;
Many thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
