Home > Blockchain >  alternative tables all_types for package define types in oracle db
alternative tables all_types for package define types in oracle db

Time:04-07

I use oracle 19. The following type is declared in the package.

    CREATE OR REPLACE package MYUSER.mytest is
      TYPE MY_TABLE IS TABLE OF VARCHAR2(30);

      FUNCTION mytable_test ( in_param IN MY_TABLE , out_param OUT MY_TABLE , inout_param IN OUT MY_TABLE )
               RETURN MY_TABLE ;
end mytest;

I need to understand this type of object or collection. I usually do this with the following query:

SELECT  * FROM all_types WHERE OWNER = 'MYUSER', TYPE_NAME  = 'MY_TABLE';

But it doesn't work for types declared inside the package. How can I do it?

CodePudding user response:

Look in all_plsql_types instead:

SELECT * FROM all_plsql_types
WHERE OWNER = 'MYUSER'
AND TYPE_NAME = 'MY_TABLE';

As you know it's a collection type you can dig in a bit further with all_plsql_coll_types:

SELECT * FROM all_plsql_coll_types
WHERE OWNER = 'MYUSER'
AND TYPE_NAME = 'MY_TABLE';

db<>fiddle

Documentation for all_plsql_types and all_plsql_coll_types.

  • Related