I am migrating tables with nested tables/array to another database so i am trying to create a target structure table with the data element of the nested table and array. I am able to roughly get the column and data type details for nested table from ALL_NESTED_TABLES and ALL_NESTED_TABLE_COLS. However, there could be VARRAYS in the structure as well. HOw do i get the details of a VARRAY. Table ALL_VARRAYS identify the varray but doesnt give the data element its using such as RAW or VARCHAR.
I pick a standard example in a oracle database which is MGMT_JOB_VALUE_PARAMS. I am assuming this is a standard table. Which has three columns.
SOURCE_ID
PARAM_VALUES MGMT_JOB_PARAM_LIST (nested table)
ACTION
MGMT_JOB_PARAM_LIST is broken down further to
NESTED_TABLE_ID RAW
MGMT_JOB_PARAM_RECORD Varray
MGMT_JOB_VECTOR_PARAMS Varray
Both MGMT_JOB_PARAM_RECORD and MGMT_JOB_VECTOR_PARAMS is available in table ALL_VARRAYS.
I dont have direct access to the Oracle database and only able to call native SQL to select the data. I am already able to select data for nested table since I am able to create the structure and field names properly.
Hope somebody can help.
Thanks. Anand Muthu
Looked through various table to find the data structure for varray. ALL_TYPES looked promising but unable to find the type above in this table.
The all_varrays view specifies the type is uses in type_owner.type_name. Use that to look up the collection type info in all_coll_types (not all_types which is more generic and not specific to collections). What you are looking for will be in the elem_type_name column.