ORACLE expression is of wrong type on pipelined function (multiple joins table)

Question

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?

score 1 · Accepted Answer · answered Aug 27 '18 at 21:39

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>

ORACLE expression is of wrong type on pipelined function (multiple joins table)

1 Answers1