CREATE OR REPLACE PACKAGE pg_test_sort
IS
   TYPE tab_script IS TABLE OF VARCHAR2 (4000);

   FUNCTION get_script
      RETURN tab_script PIPELINED;
END;
/

SHOW error

Package created.
No errors.

CREATE OR REPLACE PACKAGE BODY pg_test_sort
IS
   FUNCTION get_script
      RETURN tab_script PIPELINED
   IS
      TYPE type_table_rows IS RECORD (
         table_name   VARCHAR2 (30),
         num_rows     NUMBER (10)
      );

      TYPE tab_table_rows IS TABLE OF type_table_rows;

      l_type_obj_tab   tab_table_rows := tab_table_rows ();
      l_sort_script    tab_script     := tab_script ();
      l_row_count      PLS_INTEGER;
      l_tab_name       VARCHAR2 (30);
      l_counter        PLS_INTEGER    DEFAULT 1;
      l_counter1       PLS_INTEGER;
   BEGIN
      FOR j IN (SELECT     LEVEL num_rows, CHR (65 + LEVEL) table_name
                      FROM DUAL
                CONNECT BY LEVEL <= 10)
      LOOP
         l_type_obj_tab.EXTEND;
         l_type_obj_tab (l_counter).table_name := j.table_name;
         l_type_obj_tab (l_counter).num_rows := l_row_count;
         l_counter := l_counter + 1;
      END LOOP;

      SELECT CAST (MULTISET (SELECT   table_name, num_rows
                                 FROM TABLE (l_type_obj_tab)
                             ORDER BY num_rows DESC
                            ) AS tab_script
                  )
        INTO l_sort_script.script
        FROM DUAL;

      RETURN;
   END;
END;
/

SHOW error

Warning: PACKAGE BODY created with compilation errors.
Errors for PACKAGE BODY PG_TEST_SORT

LINE/COL ERROR                                                            
-------- -----------------------------------------------------------------
33/34    PL/SQL: ORA-00902: invalid datatype                              
30/7     PL/SQL: SQL Statement ignored