Home » SQL & PL/SQL » SQL & PL/SQL » INVALID DATATYPE for Collection (10.2.0.4.0 - 64bi)
INVALID DATATYPE for Collection [message #382567] Fri, 23 January 2009 00:55 Go to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi All,

I am trying to sort collection, but getting INVALID DATATYPE error,

This is what I tried.

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         


Please correct me where I am wrong. Sorry for not providing line number for the same. The error is comming for TAB_SCRIPT.

Thanks
Trivendra
Re: INVALID DATATYPE for Collection [message #382572 is a reply to message #382567] Fri, 23 January 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL does not know PL/SQL type.
Create permanent type with CREATE TYPE.

In addition, if your function is pipelined it should contains at least one PIPE ROW call.
RETURN is useless in pipelined function, better remove it (note it was mandatory in earlier 9i versions).

Regards
Michel
Re: INVALID DATATYPE for Collection [message #382574 is a reply to message #382567] Fri, 23 January 2009 01:16 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks Michel, for this information.

I will take care of the same, and will try to sort collection with Creating SQL Type and will do some neccessary changes (As given by you).

Thanks and Regards
Trivendra
Previous Topic: need help in PLSQL
Next Topic: how to chnage a customer_id
Goto Forum:
  


Current Time: Fri Dec 02 18:25:51 CST 2016

Total time taken to generate the page: 0.11836 seconds