PLS-00540: Object not supported in this context while trying to compile package specification

Question

I'm trying to compile a package specification. I would like to include some type definitions and declare a function:

TYPE col_type AS OBJECT (
col_name VARCHAR2(50)
);

TYPE col_sub_type
IS TABLE OF
col_type;

FUNCTION get_col_tab RETURN col_sub_type;

And finally, the get_col_tab function:

FUNCTION get_col_tab RETURN col_sub_type AS
l_type  col_sub_type := col_sub_type();
BEGIN
FOR i IN (SELECT DISTINCT TABLE_NAME t_name FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_1') LOOP
l_type.extend;
l_type(l_type.last) := col_type(i.t_name);
END LOOP;
RETURN l_type;
END;

But when compiling the package specification I get the following error:

PLS-00540: Object not supported in this context

As I understand I cannot use a type OBJECT in package specification. Is there a workaround or another way to do this?

Thanks in advance.

Answer 1

Within PL/SQL, you have to use record instead of object.

TYPE col_type IS RECORD (
col_name VARCHAR2(50)
);

TYPE col_sub_type
IS TABLE OF
col_type;

FUNCTION get_col_tab RETURN col_sub_type;

While there are some functional differences, if you're just looking to create a group of column definitions, they are effectively the same.

---

One of those differences is that the record type does not support constructors (either implicit or explicit). This means you'll need to assign each field individually:

l_type(l_type.last).col_name := i.t_name;

An alternative is to use bulk collect:

SELECT DISTINCT table_name t_name
BULK   COLLECT INTO l_type
FROM   all_tab_columns
WHERE  table_name = 'TABLE_1';

Answer 2

Why do you use an object?

Simply do

TYPE col_sub_type IS TABLE OF VARCHAR2(50);

and then

FUNCTION get_col_tab RETURN col_sub_type AS
l_type  col_sub_type;
BEGIN

   SELECT DISTINCT TABLE_NAME 
   BULK COLLECT INTO l_type
   FROM ALL_TAB_COLUMNS 
   WHERE TABLE_NAME = 'TABLE_1';

   RETURN l_type;
END;