How to get information about a User-Defined Type?

Question

In simplicity, PL/SQL generally follow the following:

DECLARE 
     Variable declaration
BEGIN 
     Program Execution 
EXCEPTION 
     Exception handling
END;

I am quite new to PL/SQL and i am looking at the variable declaration section where i would like to find out more information on SALES_PRODUCT_TY_LIST.

Is there a table i may look up to check on information on SALES_PRODUCT_TY_LIST, such as checking out table column information from all_tab_cols view?

CREATE OR REPLACE PROCEDURE GET_DISCOUNTS
(
  v_have_list SALES_PRODUCT_TY_LIST
)
IS
  QUERY VARCHAR(5000);
...

Thanks.

Solution 1

The Oracle database has an extensive data dictionary (what some other DBMS products call the INFORMATION SCHEMA). You can find all the views here. Alas, the revised ToC structure makes it harder to find something in the 11g documentation unless you already know what you're looking for, so use the index instead. 8-)

Anyway, the views you need to query are ALL_TYPES and ALL_TYPE_ATTRS.

Solution 2

This seems to be user defined collection type. You can find some information about it querying all_types/user_types view:

select * from user_types where type_name = 'SALES_PRODUCT_TY_LIST'

The definition of the type can be found for example by querying all_source/user_source view:

select text from user_source where name = 'SALES_PRODUCT_TY_LIST' order by line

Solution 3

Try this to get DDL:

SELECT dbms_log.substr(dbms_metadata.get_ddl('TYPE', 'SALES_PRODUCT_TY_LIST'), 32767,1) 
FROM DUAL;

see: http://www.myoracleguide.com/s/gen_schema.htm

Solution 4

Ok i found something:

select * 
from all_objects
where object_name like 'SALES%';

View more solutions