I need to create a function (or stored procedure???) that takes pivoted data and outputs it. An external process
needs to call the function/procedure and receive the data. The amount of data rows always varies; there is no set limit. However, there is a large amount of data (e.g. 75,000+).
I get a large amount of data from multiple table joins. I then pivot this data such that it will have
108 columns and multiple rows (large amount of rows). I need to create a function (or stored procedure??)
that will output the pivoted data rows. I am having 2 problems:
1) getting the function to compile without using the PIPELINE functionality
2) how to implement the PIPELINE functionality into the code. (I have found various examples on this, but
none have been useful so far.)
Below is a brief outline of the code:
create or replace
FUNCTION NOMT_EXPORT_FUNC
(
PARAM_START_DATE IN DATE
, PARAM_END_DATE IN DATE
) RETURN NomT_DataTbl PIPELINED IS
nom_start_date DATE := PARAM_START_DATE;
nom_end_date DATE := PARAM_END_DATE;
CREATE OR REPLACE TYPE NomT_RowsExport AS OBJECT
(
deal_num number(32,0),
tran_num number(32,0),
ba_tenant varchar2(32),
from_ba varchar2(32),
to_ba varchar2(32),
from_ca varchar2(32),
to_ca varchar2(32),
sched_start_date DATE,
-- **** Columns where v00 is column for 0000, v01 is column for 0015, ... v99 is column for 2545
-- ***** All columns and columns 2500, 2515, 2530, 2545 can be renamed
v00 number(32,6) DEFAULT 0.0,
v01 number(32,6) DEFAULT 0.0,
v02 number(32,6) DEFAULT 0.0,
v03 number(32,6) DEFAULT 0.0,
v04 number(32,6) DEFAULT 0.0,
v05 number(32,6) DEFAULT 0.0,
v06 number(32,6) DEFAULT 0.0,
v07 number(32,6) DEFAULT 0.0,
v08 number(32,6) DEFAULT 0.0,
v09 number(32,6) DEFAULT 0.0,
:
:
:
v91 number(32,6) DEFAULT 0.0,
v92 number(32,6) DEFAULT 0.0,
v93 number(32,6) DEFAULT 0.0,
v94 number(32,6) DEFAULT 0.0,
v95 number(32,6) DEFAULT 0.0,
v96 number(32,6) DEFAULT 0.0,
v97 number(32,6) DEFAULT 0.0,
v98 number(32,6) DEFAULT 0.0,
v99 number(32,6) DEFAULT 0.0
)
CREATE OR REPLACE TYPE NomT_DataTbl
AS TABLE OF NomT_RowsExport;
BEGIN
WITH qtr_hr_vols AS
(
SELECT "all the data needed for the pivot";
)
select * from qtr_hr_vols
PIVOT (
SUM (sched_volume) FOR sched_hour IN ('0000', '0015', '0030', '0045', '0100', '0115', '0130', '0145', '0200',
'0215', '0230', '0245', '0300', '0315', '0330', '0345', '0400',
'0415', '0430', '0445', '0500', '0515', '0530', '0545', '0600',
'0615', '0630', '0645', '0700', '0715', '0730', '0745', '0800',
'0815', '0830', '0845', '0900', '0915', '0930', '0945', '1000',
'1015', '1030', '1045', '1100', '1115', '1130', '1145', '1200',
'1215', '1230', '1245', '1300', '1315', '1330', '1345', '1400',
'1415', '1430', '1445', '1500', '1515', '1530', '1545', '1600',
'1615', '1630', '1645', '1700', '1715', '1730', '1745', '1800',
'1815', '1830', '1845', '1900', '1915', '1930', '1945', '2000',
'2015', '2030', '2045', '2100', '2115', '2130', '2145', '2200',
'2215', '2230', '2245', '2300', '2315', '2330', '2345', '2500',
'2515', '2530', '2545')
)
ORDER BY deal_num, schedule_date;
-- ?????????????????
-- Create a loop here to pipeline the data from the pivot table ?????
-- The amount of data rows is always varied.
RETURN NULL;
END NOMT_EXPORT_FUNC;
1. I don't understand what you mean. Why are you trying to write a pipelined function without pipeline
2. Yes, you need to create a loop which fetches data from your query. Then pipes the results into the output object on each iteration.
You can find several examples at:
http://www.oracle-developer.net/display.php?id=429 https://stevenfeuersteinonplsql.blogspot.co.uk/2015/04/table-functions-introduction-and.html (part 1 of 6 series)
https://oracle-base.com/articles/misc/pipelined-table-functions Of course, another question here is why are you using pipelined functions?
To access this data, the external process will still need to execute a query like:
select * from table(your_pipelined_fn)
So why not get it to run pivot query in the first place?
Or you could look into:
- Returning a ref cursor and letting the external app fetch the results as needed
- Just returning an array of objects (rather than a pipelined function). As you have lots of data you'll probably want to include some form of pagination in this process (fetch first 100 rows, then next 100, etc.). You'll need to figure out how to make the results consistent across calls though...
If you need more detailed help, please post a complete test case which includes
- Table DDL (create table)
- Sample data (insert into)
- The output you expect from your procedure
- An explanation of what you've tried isn't working (ORA errors, a description of why the output is wrong, ...)