0

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Martins
  • 39
  • 5

2 Answers2

3

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Isn't it should be USER_COLL_TYPES rather than ALL_COLL_TYPE for current user's schema. – XING Aug 18 '16 at 14:57
  • The question refers to `all_types` so I stuck with that. But yes, if you're only working within one schema, `user_coll_types` would be fine. – Alex Poole Aug 18 '16 at 14:58
  • I've included an example with a UDT too; the OP's table type *is* a UDT, but I assume you mean a table of other UDT, which I've done as a really simple object (the OP also referred to "relationship between table types and specific object types"). – Alex Poole Aug 18 '16 at 15:02
  • Thanks, this is what I need! I will just add all_type_attrs, to get all attributes from object. – Martins Aug 19 '16 at 05:17
  • Maybe you can also tell me in which table I can find types that is defined inside packages, if it is possible? – Martins Aug 19 '16 at 05:22
  • @Martins - if you're on 11g or higher you can see them in `user_identifiers`; you may need to [explicitly turn on tracking](http://stackoverflow.com/a/6413976/266304) though. – Alex Poole Aug 19 '16 at 10:11
  • Thanks again! Managed to create a procedures that create printing functions that prints types and table types as varchar2. – Martins Aug 19 '16 at 15:09
0

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;
XING
  • 9,608
  • 4
  • 22
  • 38
  • The question is asking how to find the element type for a given collection; this is doing the reverse, starting with a known type and finding all matching collections types. Why use a procedure for this, rather than just running the cursor query directly? The join is also a bit pointless, you can just query `user_coll_types` on its own for this. – Alex Poole Aug 18 '16 at 17:13
  • Question has 2 parts . First how to.find element type. You answered it very well. Second creating a procedure for the same. Check the last line." I do need it to generate proceedures to print table types as varchar2" – XING Aug 18 '16 at 17:49
  • Mmm, I read that as printing the type of any table as a string, rather than restricting to tables of varchar2(n), as the rest of the question is about objects. It isn't clear though... – Alex Poole Aug 18 '16 at 17:55
  • Thanks for the answer! First answer is more closer to what I need, but for me most important was to find out relationship between table type and type. – Martins Aug 19 '16 at 05:17