First create a package:
CREATE OR REPLACE PACKAGE my_pkg
AUTHID DEFINER
AS
TYPE names_table IS TABLE OF VARCHAR2(100);
FUNCTION get_names
RETURN names_table
PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY my_pkg
AS
FUNCTION get_names
RETURN names_table
PIPELINED
IS
BEGIN
FOR i IN 1 .. 5001
LOOP
PIPE ROW ('Name ' || i);
END LOOP;
RETURN;
END;
END my_pkg;
/
Then query, but do this in an IDE that only allows the first few rows to be shown/loaded - e.g. SQL Developer, TOAD,…:
select * from table(MY_PKG.get_names());
And finally recompile:
alter package my_pkg compile;
I get the following (SQL Developer on Linux, 23.4 Free installed as RPM), and my database connection is gone, too:
alter package my_pkg compile
Error report -
ORA-00600: internal error code, arguments: [17285], [0x7FE20BE1DB58], [1], [0x07D951CF0], [], [], [], [], [], [], [], []
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
exceptions. It indicates that a process has encountered a low-level,
unexpected condition. The first argument is the internal message
number. This argument and the database version number are critical in
identifying the root cause and the potential impact to your system.
I might add that this error also occurs on 19.20 and 19.22 (yeah, I tried it) and has received quite some attention - in several disguises - on MOS, too. Seems to be due to the pipelined table still “active” (not all rows/data were retrieved) and thus preventing its definition from being recompiled/recreated.
Time to get it solved once and for all?
