Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to return resultset (Oracle 10g)
Procedure to return resultset [message #282520] Thu, 22 November 2007 04:26 Go to next message
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 #282525 is a reply to message #282520] Thu, 22 November 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Return a REF CURSOR instead of a TABLE

Regards
Michel
Re: Procedure to return resultset [message #282747 is a reply to message #282525] Fri, 23 November 2007 04:59 Go to previous message
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
Previous Topic: How to duplicate Record
Next Topic: External Table Error Trapping
Goto Forum:
  


Current Time: Sat Dec 10 18:49:13 CST 2016

Total time taken to generate the page: 0.07915 seconds