Metadata regarding record types at the PL / SQL package level

Question

Suppose you have a PL / SQL package with a specific type of RECORD :

 CREATE OR REPLACE PACKAGE TEST_PACKAGE AS TYPE PERSON_RECORD_TYPE IS RECORD ( first_name VARCHAR2(1000), last_name VARCHAR2(1000) ); END; 

Is there a way to get the list of fields contained in TEST_PACKAGE.PERSON_RECORD_TYPE ? For example, are there ALL_* views with this information?

I do not need record types at the circuit level, only record types at the package level.

Answer 1

If PERSON_RECORD_TYPE is used as an argument or result type of some procedure or function, you can query ALL_ARGUMENTS. The information here is slightly encrypted (the hierarchy of multilevel encapsulation of records and collections is encoded in the POSITION, SEQUENCE and DATA_LEVEL columns), but it is present.

I do not think that such a question indicates the wrong architecture. For automatic PLSQL code generation, this is a completely legitimate query, unfortunately, with very weak support for the PLSQL language.

Answer 2

Here are some similar questions about getting information from the package code.

Find global package variables from a data dictionary

Get package methods and parameters from Oracle

I see this as a similar problem, such as the first. You cannot access these fields through a view. There is a textual parsing solution that is ugly, or you may need a workaround for this.

In any case, I think this is something wrong in your architecture if you need it.

Answer 3

The jOOQ code generator internally uses the following query to reliably search all PL/SQL RECORD package types:

 SELECT "x"."TYPE_OWNER", "x"."TYPE_NAME", "x"."TYPE_SUBNAME","a".subprogram_id, "a"."ARGUMENT_NAME" "ATTR_NAME", "a"."SEQUENCE" "ATTR_NO", "a"."TYPE_OWNER" "ATTR_TYPE_OWNER", nvl2("a"."TYPE_SUBNAME", "a"."TYPE_NAME", NULL) "package_name", COALESCE("a"."TYPE_SUBNAME", "a"."TYPE_NAME", "a"."DATA_TYPE") "ATTR_TYPE_NAME", "a"."DATA_LENGTH" "LENGTH", "a"."DATA_PRECISION" "PRECISION", "a"."DATA_SCALE" "SCALE" FROM "SYS"."ALL_ARGUMENTS" "a" JOIN ( SELECT "a"."TYPE_OWNER", "a"."TYPE_NAME", "a"."TYPE_SUBNAME", MIN("a"."OWNER") KEEP (DENSE_RANK FIRST ORDER BY "a"."OWNER" ASC, "a"."PACKAGE_NAME" ASC, "a"."SUBPROGRAM_ID" ASC, "a"."SEQUENCE" ASC) "OWNER", MIN("a"."PACKAGE_NAME") KEEP (DENSE_RANK FIRST ORDER BY "a"."OWNER" ASC, "a"."PACKAGE_NAME" ASC, "a"."SUBPROGRAM_ID" ASC, "a"."SEQUENCE" ASC) "PACKAGE_NAME", MIN("a"."SUBPROGRAM_ID") KEEP (DENSE_RANK FIRST ORDER BY "a"."OWNER" ASC, "a"."PACKAGE_NAME" ASC, "a"."SUBPROGRAM_ID" ASC, "a"."SEQUENCE" ASC) "SUBPROGRAM_ID", MIN("a"."SEQUENCE") KEEP (DENSE_RANK FIRST ORDER BY "a"."OWNER" ASC, "a"."PACKAGE_NAME" ASC, "a"."SUBPROGRAM_ID" ASC, "a"."SEQUENCE" ASC) "SEQUENCE", MIN("next_sibling") KEEP (DENSE_RANK FIRST ORDER BY "a"."OWNER" ASC, "a"."PACKAGE_NAME" ASC, "a"."SUBPROGRAM_ID" ASC, "a"."SEQUENCE" ASC) "next_sibling", MIN("a"."DATA_LEVEL") KEEP (DENSE_RANK FIRST ORDER BY "a"."OWNER" ASC, "a"."PACKAGE_NAME" ASC, "a"."SUBPROGRAM_ID" ASC, "a"."SEQUENCE" ASC) "DATA_LEVEL" FROM ( SELECT lead("a"."SEQUENCE", 1, 99999999) OVER ( PARTITION BY "a"."OWNER", "a"."PACKAGE_NAME", "a"."SUBPROGRAM_ID", "a"."DATA_LEVEL" ORDER BY "a"."SEQUENCE" ASC ) "next_sibling", "a"."TYPE_OWNER", "a"."TYPE_NAME", "a"."TYPE_SUBNAME", "a"."OWNER", "a"."PACKAGE_NAME", "a"."SUBPROGRAM_ID", "a"."SEQUENCE", "a"."DATA_LEVEL", "a"."DATA_TYPE" FROM "SYS"."ALL_ARGUMENTS" "a" WHERE "a"."OWNER" IN ('TEST') -- Possibly replace schema here ) "a" WHERE ("a"."TYPE_OWNER" IN ('TEST') -- Possibly replace schema here AND "a"."OWNER" IN ('TEST') -- Possibly replace schema here AND "a"."DATA_TYPE" = 'PL/SQL RECORD') GROUP BY "a"."TYPE_OWNER", "a"."TYPE_NAME", "a"."TYPE_SUBNAME" ) "x" ON (("a"."OWNER", "a"."PACKAGE_NAME", "a"."SUBPROGRAM_ID") = (("x"."OWNER", "x"."PACKAGE_NAME", "x"."SUBPROGRAM_ID")) AND "a"."SEQUENCE" BETWEEN "x"."SEQUENCE" AND "next_sibling" AND "a"."DATA_LEVEL" = ("x"."DATA_LEVEL" + 1)) ORDER BY "x"."TYPE_OWNER" ASC, "x"."TYPE_NAME" ASC, "x"."TYPE_SUBNAME" ASC, "a"."SEQUENCE" ASC 

In your case, the result would be something like:

 TYPE_NAME TYPE_SUBNAME ATTR_NAME ATTR_TYPE_NAME LENGTH ---------------------------------------------------------------------- TEST_PACKAGE PERSON_RECORD_TYPE FIRST_NAME VARCHAR2 1000 TEST_PACKAGE PERSON_RECORD_TYPE LAST_NAME VARCHAR2 1000 

Current restrictions:

• Only types that are referenced by at least one other type and / or procedure will be found in the query. This restriction is inherited from the ALL_ARGUMENTS dictionary in the request.
• %ROWTYPE not returned correctly because the row type does not refer to the columns TYPE_NAME / TYPE_SUBNAME .

Further information here: https://blog.jooq.org/2016/11/08/use-jooq-to-read-write-oracle-plsql-record-types