oracle-databaseplsqltypespackagepipelined-function

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


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?


Solution

  • 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>