CODEWITHSUNDEEP
sqloracle-databaseselecttypes
Martins
Martins

Reputation: 49

Oracle - Select in sys views relation between table type (collection) and object

It is possible select all user defined types in oracle from view sys.all_types. Also it is possible to select all type attributes from sys.all_type_attrs.

Still, I can't find a way to select or find relationship between table types and specific object types.

For example, CREATE OR REPLACE TYPE "VARCHAR2_TT" AS TABLE OF VARCHAR2(2000) is defined table type. How can I use select to find out type of VARCHAR2_TT?

I do need it to generate procedures to print table types as varchar2.

Upvotes: 0

Views: 466

Answers (2)

XING
XING

Reputation: 9886

The below is the procedure to display all "table types" as "varchar2" in current user's schema. Ofcourse you can replace these user tables to ALL or DBA for broader prospects.

Create or replace procedure display_type 
as
    cursor type_usrr is 
    select UT.type_name typname
    from user_types  UT,
         user_COLL_TYPES UCT
    where UT.type_name = UCT.type_name
    and UT.typecode = 'COLLECTION'
    and UCT.COLL_TYPE = 'TABLE'
    and UCT.ELEM_TYPE_NAME = 'VARCHAR2' ;

Begin

for recs in type_usrr
loop
dbms_output.put_line(recs.typname);
end loop;

end;

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191265

Once you know it's a collection (which all_types tells you), you can look at the all_coll_types view:

ALL_COLL_TYPES describes all named collection types (varrays and nested tables) accessible to the current user.

In this case you'd see, picking a couple of columns:

select coll_type, elem_type_name, length from all_coll_types where type_name = 'VARCHAR2_TT';

COLL_TYPE                      ELEM_TYPE_NAME                     LENGTH
------------------------------ ------------------------------ ----------
TABLE                          VARCHAR2                             2000

For other types of collections some of the other columns will be relevant.

If you had a collection of objects you'd see the object type instead; here as a varray rather than a nested table, just to show the upper bound is available too:

create or replace type my_obj_t as object(id number);
/
create or replace type my_obj_tt as varray(5) of my_obj_t;
/

select coll_type, upper_bound, elem_type_name, length from all_coll_types where type_name = 'MY_OBJ_TT';

COLL_TYPE                      UPPER_BOUND ELEM_TYPE_NAME                     LENGTH
------------------------------ ----------- ------------------------------ ----------
VARYING ARRAY                            5 MY_OBJ_T

... and so on.

You can also get the DDL to recreate the type with select dbms_metadata.get_ddl('TYPE', 'VARCHAR2_TT') from dual but that doesn't seem to be what you want here.

Upvotes: 3

Related Questions