CODEWITHSUNDEEP
oracle-databaseplsql
md2614
md2614

Reputation: 489

Pipelined function gives "PLS-00630" error upon compilation

I'm new to table functions. I'm receiving a "PLS-00630 pipelined functions must have a supported collection return type" when trying to compile a package with the function.

The intent of the function is return a list of records from a RUN_LOG audit table. It takes an input date which would return records for that date.

The data types of the PROCS_COMPLETED object matches the RUN_LOG table.

Created these objects:

CREATE OR REPLACE TYPE PROCS_COMPLETED IS OBJECT (
    RECORD_ID      NUMBER
  , RUN_START_DATE DATE
  , RUN_END_DATE   DATE
  , OWNER          VARCHAR2(50)
  , PROC_NAME      VARCHAR2(162)
);

CREATE OR REPLACE TYPE PROCS_COMPLETED_INFO IS TABLE OF PROCS_COMPLETED;

Function declaration in package spec:

FUNCTION GET_PROCS_COMPLETED(ID_DATE IN DATE) RETURN PROCS_COMPLETED PIPELINED;

Function in package body:

FUNCTION GET_PROCS_COMPLETED(ID_DATE IN DATE) RETURN PROCS_COMPLETED PIPELINED AS
BEGIN
  FOR C_PROCS_COMPLETED IN (
                            SELECT r.RECORD_ID
                                 , r.RUN_START_DATE
                                 , r.RUN_END_DATE
                                 , r.OWNER
                                 , r.PROC_NAME
                              FROM RUN_LOG r
                             WHERE TRUNC(r.RUN_START_DATE) = TRUNC(ID_DATE)
                           )
  LOOP
    PIPE ROW (PROCS_COMPLETED_INFO
               (  C_PROCS_COMPLETED.RECORD_ID
                , C_PROCS_COMPLETED.RUN_START_DATE
                , C_PROCS_COMPLETED.RUN_END_DATE
                , C_PROCS_COMPLETED.OWNER
                , C_PROCS_COMPLETED.PROC_NAME
               )
             );
  END LOOP;
  RETURN;
END;

Upvotes: 1

Views: 3838

Answers (1)

mustaccio
mustaccio

Reputation: 18945

If you read the error message carefully, it actually tells you what's wrong:

pipelined functions must have a supported collection return type

(emphasis mine).

So, RETURN PROCS_COMPLETED_INFO.

Another mistake is to attempt to pipe the collection type -- PIPE ROW (PROCS_COMPLETED_INFO(...)) -- while you want to pipe a ROW: PIPE ROW (PROCS_COMPLETED(...))

Upvotes: 2

Related Questions