CODEWITHSUNDEEP
oracle-database
jihonsvik
jihonsvik

Reputation: 43

Select all fields from a package datatype

If I have a custom recorddatatype looking like this, declared in a package spec:

TYPE t_params IS record(
param1 BOOLEAN,
param2 BOOLEAN,
param3 boolean);

Is there any views I can query from that would list up all the field/column names (param1, param2, param3)?

For example something like this: SELECT * FROM ALL_FIELDS WHERE DATATYPE = 't_params'

Upvotes: 1

Views: 298

Answers (2)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

In Oracle 11g and lower, you can't. At least, not in a straightforward way.

In Oracle 12c, you can easily select from

  • user_plsql_types
  • user_plsql_type_attrs
  • user_plsql_coll_types

Edit: Assuming that only SQL-types are in use in the record type (which is not the case with your record with booleans) in question, then in Oracle 11g you can do it with a funky workaround.

For the following example I'll use one of my packaged PL/SQL types, rec_parse_dd_file_name_out placed in the utl_dd_intfc_file package.

I'll create a "wrapper" package wrapper_pkg with

  • one wrapper_nt type as a collection of the PL/SQL record type of my interest
  • one table function wrapper_tf returning the pipelined wrapper_nt collection

Note: We don't need a package body for this experiment.

create or replace package wrapper_pkg
as

type wrapper_nt is table of utl_dd_intfc_file.rec_parse_dd_file_name_out;

function wrapper_tf return wrapper_nt pipelined;

end;
/

Now, querying the user_arguments for result type of the wrapper_pkg.wrapper_tf() table function ...

select argument_name, position, data_type, data_length, data_precision, data_scale
from user_arguments X
where package_name = 'WRAPPER_PKG'
    and object_name = 'WRAPPER_TF'
    and in_out = 'OUT'
    and data_level = 2
order by sequence
;

... gives us a nice view on the fields defined in the record type:

ARGUMENT_NAME               POSITION DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE
------------------------- ---------- ---------- ----------- -------------- ----------
ID_DD_FILE                         1 NUMBER              22             38 
ID_DD_BATCH                        2 NUMBER              22             38 
TXT_FILE_NAME                      3 VARCHAR2          2000                
DETECTED_TYPE                      4 VARCHAR2            32                
IS_CORRECT_FILE_NAME               5 CHAR                 1                
TARGET_SYSTEM                      6 VARCHAR2          2000                
DELIVERING_SYSTEM                  7 VARCHAR2          2000                
CONTENT_ABBREVIATION               8 VARCHAR2          2000                
DELIVERING_UNIT                    9 VARCHAR2          2000                
PERIOD                            10 VARCHAR2             3                
IS_DELTA                          11 CHAR                 1                
IS_CORRECTIVE                     12 CHAR                 1                
IS_DAILY                          13 CHAR                 1                
IS_MONTHLY                        14 CHAR                 1                
DELIVERY_DATE_STR                 15 VARCHAR2          2000                
DELIVERY_TIME_STR                 16 VARCHAR2          2000                
DELIVERY_DATE_TIME                17 DATE                                  
FILE_EXTENSION                    18 VARCHAR2          2000

Upvotes: 1

Boneist
Boneist

Reputation: 23588

If the type is declared in the package, then no, you won't be able to do a simple query to find out more information about the type. You might perhaps be able to do some fancy regular expression against the user/all_source table though.

If the type was created at the schema level, eg. create type ..., then you'd be able to use one or more of the following to find out information about them:

select * from user_types;

select * from user_type_attrs;

select * from user_coll_types;

Upvotes: 1

Related Questions