I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package.
I am using SQLDeveloper 3.0 with an Oracle 11g database.
I have already developed a stored procedure 'acctg_detail_proc()' that generates a detailed list of accounting transactions within a specified period. What I am trying to do is create a summary report procedure 'acctg_summary_proc()' using the detailed data returned by acctg_detail_proc().
Since I am new to PL/SQL, all I have learned so far is how to retrieve the detailed data via a ref cursor, then LOOP through it, FETCHing each individual detail row. I want to figure out how acctg_summary_proc() can substitute that call to acctg_detail_proc() for a table name in a SELECT statement with a GROUP-BY clause. Here is the source code for an anonymous block where I tried to test it:
SET SERVEROUTPUT ON; DECLARE start_date VARCHAR2(50) := '04/01/2012'; end_date VARCHAR2(50) := '04/30/2012'; c_acctg_refcur SYS_REFCURSOR; BEGIN acctg_rpt_pkg.acctg_detail_proc(start_date, end_date, c_acctg_refcur); SELECT date_posted, debit_acct, credit_acct, SUM(dollar_amt) FROM c_acctg_refcur GROUP BY date_posted, debit_acct, credit_acct; CLOSE c_acctg_refcur; END;
When I try to execute this code, I get the following error:
PL/SQL: ORA-00942: table or view does not exist
I realize I could use a nested SELECT statement instead of a table name, but I want to avoid duplication of source code. Is there any way to 'alias' a ref cursor so I can reference its data in a SELECT statement?
Here is some further background info: The called sub-procedure has ~600 lines of code, and selects 40 columns of data from a de-normalized VIEW. The corporate DBAs will not let me create any VIEWs that contain WHERE clauses, so that is not an option for me.
Thanks in advance, Ken L.