Return data with Oracle Stored Procedure 1

[kvang]

How would I convert the following SQL statement to an Oracle stored procedure?

SELECT A.WORKTYPE, COUNT(*) AS ITEMCOUNT
FROM REPORTLS.RETAIL_PROCESSLOG A, REPORTLS.WORKTYPES B
WHERE A.WORKTYPE = B.WORKTYPE
AND TRUNC(ENDLOCKTIME) >= TO_DATE('&FromDate','MM/DD/YYYY')
AND TRUNC(ENDLOCKTIME) <= TO_DATE('&ToDate','MM/DD/YYYY')
AND DEPARTMENT = '&DEPARTMENT'
GROUP BY A.WORKTYPE

Any help is appreciated. Thanks.

 

[dbtoo2001]

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#22244

 

[engineer2100]

return a REF CURSOR as an OUT parameter from the SP or as Return parameter from a Function.

HTH

Engi

 

[jimirvine]

Pretty much impossible to give you any kind of definitive answer since your question is so vague.

CREATE OR REPLACE PROCEDURE total_guess IS
CURSOR no_idea_what_u_need IS
SELECT A.WORKTYPE, COUNT(*) AS ITEMCOUNT
FROM REPORTLS.RETAIL_PROCESSLOG A, REPORTLS.WORKTYPES B
WHERE A.WORKTYPE = B.WORKTYPE
AND TRUNC(ENDLOCKTIME) >= TO_DATE('&FromDate','MM/DD/YYYY')
AND TRUNC(ENDLOCKTIME) <= TO_DATE('&ToDate','MM/DD/YYYY')
AND DEPARTMENT = '&DEPARTMENT'
GROUP BY A.WORKTYPE;
BEGIN
--Since u have given next to no info
NULL;
END;

What I would say however, is avoid the use of substitution variables in your sql. Use Bind variables. This will make your application more efficient and scaleable.

 

[kvang]

I want to create a stored procedure that would return the same result set as the SQL statement I listed in the first post. Thanks for all the tips so far.

 

[jimirvine]

Still far too little info. Do you want to return it into a refcursor for processing in another procedure, is it being passed out to application code, do you simply want to output the results to screen. Throw us a bone here Give us some context to work with.

 

[kvang]

I am building a WebFOCUS report. I was told that calling a stored procedure would make the data retrieval time faster than hard coding the SQL statement in the report. So I want to convert this SQL statement into a stored procedure so that it would return a result set when the report is pulled.

 

[engineer2100]

so as mentioned return a REF CURSOR

CREATE OR REPLACE PACKAGE PKG_REPORT_GEN IS
 pREPORT_DATA IS REF CURSOR;
END;
/

CREATE OR REPLACE PROCEDURE RETURN_DATA_FOR_REPORT (pData OUT PKG_REPORT_GEN.pREPORT_DATA) IS
BEGIN
OPEN pDATA FOR 
SELECT A.WORKTYPE, COUNT(*) AS ITEMCOUNT
FROM REPORTLS.RETAIL_PROCESSLOG A, REPORTLS.WORKTYPES B
WHERE A.WORKTYPE = B.WORKTYPE
AND TRUNC(ENDLOCKTIME) >= TO_DATE('&FromDate','MM/DD/YYYY')
AND TRUNC(ENDLOCKTIME) <= TO_DATE('&ToDate','MM/DD/YYYY')
AND DEPARTMENT = '&DEPARTMENT'
GROUP BY A.WORKTYPE;
END;

 

[kvang]

Thanks.