How to get information about a User-Defined Type?

Oh Chin Boon picture Oh Chin Boon · Sep 19, 2011 · Viewed 25k times · Source

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.

Answer

APC picture APC · Sep 19, 2011

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.