0

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

What am I missing here? When I try and create two different but similar (in my mind) packages I get an error for one but not the other. What is the difference in the USER_SCHEDULER_JOBS and USER_ARGUMENTS views that is causing this behavior?

    CREATE OR REPLACE PACKAGE PKG_READ_DICT_1
    AS
      TYPE T_SCHEDULER_JOBS IS TABLE OF USER_SCHEDULER_JOBS%ROWTYPE;
      FUNCTION READ_SCHEDULER_JOBS RETURN T_SCHEDULER_JOBS PIPELINED;
    END;
    /

    CREATE OR REPLACE PACKAGE PKG_READ_DICT_2
    AS
      TYPE T_USER_ARGUMENTS IS TABLE OF USER_ARGUMENTS%ROWTYPE;
      FUNCTION READ_USER_ARGUMENTS RETURN T_USER_ARGUMENTS PIPELINED;
    END;
    /

    OBJOWNER @ DEV > CREATE OR REPLACE PACKAGE PKG_READ_DICT_1
      2  AS
      3    TYPE T_SCHEDULER_JOBS IS TABLE OF USER_SCHEDULER_JOBS%ROWTYPE;
      4    FUNCTION READ_SCHEDULER_JOBS RETURN T_SCHEDULER_JOBS PIPELINED;
      5  END;
      6  /
    OBJOWNER @ DEV > SHOW ERRORS
    No errors.

    OBJOWNER @ DEV > CREATE OR REPLACE PACKAGE PKG_READ_DICT_2
      2  AS
      3    TYPE T_USER_ARGUMENTS IS TABLE OF USER_ARGUMENTS%ROWTYPE;
      4    FUNCTION READ_USER_ARGUMENTS RETURN T_USER_ARGUMENTS PIPELINED;
      5  END;
      6  /

    Warning: Package created with compilation errors.

    OBJOWNER @ DEV > SHOW ERRORS
    Errors for PACKAGE PKG_READ_DICT_2:
    4/12     PLS-00630: pipelined functions must have a supported collection
             return type

Forgive me if I've overlooked something obvious here but I've been stuck down this rabbit hole now for several hours and need a way out.

ATLScratch
  • 1
  • 2
    `user_arguments` has one column of type `long`. That data type has been depricated for quite some time. My wager would be that is the problem. – Justin Cave May 13 '22 at 18:16

1 Answers1

0

Thanks Justin. When I rewrote to exclude DEFAULT_VALUE (type LONG) it worked.

    CREATE OR REPLACE PACKAGE PKG_READ_DICT_2
    AS
      TYPE USER_ARGUMENTS_REC IS RECORD (
        OBJECT_NAME   VARCHAR2(128),
        ARGUMENT_NAME VARCHAR2(128),
        POSITION      NUMBER
      );
      TYPE T_USER_ARGUMENTS IS TABLE OF USER_ARGUMENTS_REC;
      FUNCTION READ_USER_ARGUMENTS RETURN T_USER_ARGUMENTS PIPELINED;
    END;
ATLScratch
  • 1