I'm writting a Oracle Package with a pipelined function to get multiple records, the query is about multiple joined tables, which that will save into another table, I created a TYPE RECORD and the TABLE TYPE for the type record, then I created the pipelined function with their query, but when I compiled the package I get an error "expression is of wrong type".
Here are the definition package:
CREATE OR REPLACE PACKAGE MYPACKAGE_PKG AS
TYPE GLD_R IS RECORD (
ID NUMBER,
PRCCVE NUMBER(7,0),
LOTCVE NUMBER(7,0),
EPCSEQ NUMBER(18,0),
EPCBNK CHAR(3)
);
TYPE GLD_T IS TABLE OF GLD_R;
FUNCTION MY_FUNCTION(_NUM NUMBER) RETURN GLD_T PIPELINED;
END;
Here are the body package:
CREATE OR REPLACE PACKAGE BODY MYPACKAGE_PKG AS
FUNCTION MY_FUNCTION(_NUM NUMBER) RETURN GLD_T PIPELINED
AS
CURSOR T_CUR IS
SELECT
T1.ID,
T2.COLUMN01,
T2.COLUMN02,
T3.COLUMN01,
T3.COLUMN02
FROM
TABLE01 T1
INNER JOIN TABLE02 T2 ON
T1.COLUMN03 = T2.ID
INNER JOIN TABLE03 T3 ON
T1.COLUMN04 = T2.ID
WHERE
T1.COLUMN01 = _NUM
BEGIN
FOR REC IN T_CUR LOOP
PIPE ROW (REC);
END LOOP;
END MY_FUNCTION;
END;
Can you say me, what are I'm doing wrong?
Should be something like this:
Tables first (so that compilation wouldn't fail):
SQL> create table table01 (id number,column01 number, column03 number, column04 number);
Table created.
SQL> create table table02 (id number, column01 number, column02 number);
Table created.
SQL> create table table03 (id number, column01 number, column02 number);
Table created.
Package specification:
SQL> CREATE OR REPLACE PACKAGE MYPACKAGE_PKG AS
2
3 TYPE GLD_R IS RECORD (
4 ID NUMBER,
5 PRCCVE NUMBER(7,0),
6 LOTCVE NUMBER(7,0),
7 EPCSEQ NUMBER(18,0),
8 EPCBNK CHAR(3)
9 );
10
11 TYPE GLD_T IS TABLE OF GLD_R;
12
13 FUNCTION MY_FUNCTION(p_NUM NUMBER) RETURN GLD_T PIPELINED;
14
15 END;
16 /
Package created.
Package body:
SQL> CREATE OR REPLACE PACKAGE BODY MYPACKAGE_PKG AS
2
3 FUNCTION MY_FUNCTION(p_NUM NUMBER) RETURN GLD_T PIPELINED
4 AS
5 CURSOR T_CUR IS
6 SELECT
7 T1.ID,
8 T2.COLUMN01 t2c01,
9 T2.COLUMN02 t2c02,
10 T3.COLUMN01 t3c01,
11 T3.COLUMN02 t3c02
12 FROM
13 TABLE01 T1
14 INNER JOIN TABLE02 T2 ON
15 T1.COLUMN03 = T2.ID
16 INNER JOIN TABLE03 T3 ON
17 T1.COLUMN04 = t3.id -- not T2.ID
18 WHERE
19 T1.COLUMN01 = p_NUM;
20
21 myrec gld_r;
22 BEGIN
23 FOR REC IN T_CUR LOOP
24 myrec.id := rec.id;
25 myrec.prccve := rec.t2c01;
26 myrec.lotcve := rec.t2c02;
27 myrec.epcseq := rec.t3c01;
28 myrec.epcbnk := rec.t3c02;
29 PIPE ROW (myrec);
30 END LOOP;
31 END MY_FUNCTION;
32
33 END;
34 /
Package body created.
SQL>