'Pipelined function gives "PLS-00630" for one example but not for another similar example
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.
Solution 1:[1]
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;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | ATLScratch |