I'm working with an ERP database that stores a lot of data in multi-year tables. (Each new year a new table is created to hold the data for that year) We have a requirement to be able to query and report on some of these tables. I am currently using views but the views are just getting bigger and bigger year after year and slower. I created a pipelined table function that executes some dynamic sql and queries the appropriate tables based on a from and to date passed in as parameters. I can call the pipelined function from normal SQL and it works fine. However, the goal is to be able to re-use the table function(s) in many different stored procedures and join with other data. The reporting system we use requires that we use stored procedures returning ref cursors.
I created a test function and test stored procedure (simplified versions for brevity) to attempt to return the table function as a cursor but i'm getting an error when I execute the procedure (PLS-00382: expression is of wrong type). I'm not even sure if a pipeline function can be accessed by a procedure but i've done similar things in SQL Server so there's got to be some way. I've searched hi and low but can't really find anyone having the exact same situation. Please see my code.
Following is the user defined types i've created in my schema:
CREATE OR REPLACE TYPE PUCCONNECT.wo_trans_type AS OBJECT
(GL_YEAR INT,
SUBSYSTEM VARCHAR2(2)
);
CREATE OR REPLACE TYPE PUCCONNECT.wo_trans_table_test AS TABLE OF PUCCONNECT.wo_trans_type;
Following is the function and procedure declarations. The tables i'm selecting from in the function GL101Txx have many columns so i'm only selecting the first 2 to keep things simple. Those first 2 columns have the same definition as the columns defined in my user-defined object "wo_trans_type "
CREATE OR REPLACE FUNCTION PUCCONNECT.WO_MULTIYEAR_TEST(fromdate date, todate date)
RETURN WO_TRANS_TABLE_TEST PIPELINED IS
TYPE ref0 IS REF CURSOR;
cur0 ref0;
v_year_start int;
v_year_end int;
out_rec wo_trans_type
:= wo_trans_type(NULL,NULL);
BEGIN
v_year_start := EXTRACT(year FROM fromdate);
v_year_end := EXTRACT(year FROM todate);
FOR yearNumber in v_year_start..v_year_end LOOP
OPEN cur0 FOR
'SELECT ' || yearNumber || ' "gl_year", GL.SUBSYSTEM
FROM fmsdata.GL101T' || SUBSTR(to_char(yearNumber), 3,2) || ' GL
WHERE (GL.transaction_date BETWEEN ''' || fromdate || ''' AND ''' || todate || ''')';
LOOP
FETCH cur0 INTO out_rec.gl_year, out_rec.subsystem;
EXIT WHEN cur0 %NOTFOUND;
PIPE ROW(out_rec);
END LOOP;
CLOSE cur0;
END LOOP;
RETURN;
END WO_MULTIYEAR_TEST;
And here is the procedure where I attempt to consume the function:
CREATE OR REPLACE PROCEDURE PUCCONNECT."SP_WO_TRANS_PA" (
--table_out out wo_trans_table,
wo_trans_cursor out sys_refcursor
)
AS
BEGIN
OPEN wo_trans_cursor FOR
SELECT gl_year, subsystem
FROM TABLE( PUCCONNECT.WO_MULTIYEAR_TEST('01-jan-2019', '05-may-2019'));
END;
Does anyone know if this is possible through a stored procedure? Is it possible with a non-pipelined table function? Any suggestions or advice for the best method of achieving this and maintaining performance is appreciated.
Here is the full error returned by TOAD when I try and execute the procedure:
[Error] ORA-06550: line 12, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 12, column 6:
PL/SQL: Statement ignored
(1: 0): >> DECLARE
-- Declarations
l_WO_TRANS_CURSOR SYS_REFCURSOR;
BEGIN
-- Call
PUCCONNECT.SP_WO_TRANS_PA (WO_TRANS_CURSOR => l_WO_TRANS_CURSOR);
-- Transaction Control
COMMIT;
-- Output values, do not modify
:1 := l_WO_TRANS_CURSOR;
END;
Error at line 1
ORA-06550: line 12, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 12, column 6:
PL/SQL: Statement ignored
Here is how i'm calling it in TOAD:
DECLARE
-- Declarations
l_WO_TRANS_CURSOR SYS_REFCURSOR;
BEGIN
-- Call
PUCCONNECT.SP_WO_TRANS_PA (WO_TRANS_CURSOR => l_WO_TRANS_CURSOR);
-- Transaction Control
COMMIT;
-- Output values, do not modify
:1 := l_WO_TRANS_CURSOR;
END;
You can rewrite the whole thing in one procedure, something like:
CREATE OR REPLACE PROCEDURE PUCCONNECT."SP_WO_TRANS_PA"(fromdate IN DATE,
todate IN DATE,
wo_trans_cursor OUT SYS_REFCURSOR) AS
v_year_start INT;
v_year_end INT;
v_sql CLOB;
c_union_all CONSTANT VARCHAR2(9) := 'union all';
v_table_append VARCHAR2(38);
v_column_append VARCHAR2(10);
BEGIN
v_year_start := extract(YEAR FROM fromdate);
v_year_end := extract(YEAR FROM todate);
FOR yearnumber IN v_year_start .. v_year_end
LOOP
IF yearnumber != v_year_start
THEN
v_sql := v_sql || chr(10) || c_union_all || chr(10) ||;
END IF;
-- to avoid any sql injection due to the to_char (just in case)
v_table_append := dbms_assert.qualified_sql_name('fmsdata.GL101T' || substr(to_char(yearnumber), 3, 2));
v_column_append := dbms_assert.enquote_literal(to_char(yearnumber));
v_sql := v_sql || 'select to_number(' || v_column_append || ') gl_year, gl.subsystem ' || chr(10)
|| 'from v_table_append' || chr(10)
|| 'where gl.transaction_date between :fromdate and :todate';
END LOOP;
v_sql := v_sql || chr(10) || 'order by 1, 2';
OPEN wo_trans_cursor FOR v_sql
USING fromdate, todate;
END sp_wo_trans_pa;
/
This loops through the years and generates the sql to run in the cursor, before opening the cursor with the relevant bind variables.
I have used bind variables where possible and, where it wasn't possible, dbms_assert to sanitize the concatenated values to avoid any SQL injection vulnerabilities.