Metadata regarding PL/SQL package-level record types

Question

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

CREATE OR REPLACE PACKAGE TEST_PACKAGE AS

    TYPE PERSON_RECORD_TYPE IS RECORD
    (
        first_name VARCHAR2(1000),
        last_name  VARCHAR2(1000)
    );

END;

Is there any way to obtain a list of fields contained within TEST_PACKAGE.PERSON_RECORD_TYPE? For example, are there any ALL_* views with this information?

I am not interested in schema-level record types, only package-level record types.

Answer 1

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

I don't think such a question points to wrong architecture. For automatic PLSQL code generation this is completely legitimate request, unfortunately with very weak PLSQL language support.

Answer 2

Solution after Oracle 18c

I think this didn't work prior to 18c (correct me if I'm wrong), but now we can query the ALL_PLSQL_TYPE_ATTRS view:

SELECT type_name, attr_name, attr_type_name, length
FROM all_plsql_type_attrs
WHERE package_name = 'TEST_PACKAGE'
ORDER BY owner, package_name, type_name, attr_no;

To get something like this:

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

Solution prior to Oracle 18c

jOOQ's code generator internally uses the following query to reliably find all package level PL/SQL RECORD 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 will 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 limitations:

• The query will find only those types that are referenced by at least one other type and/or procedure somewhere. This is a limitation inherited from the ALL_ARGUMENTS dictionary view in the query.
• %ROWTYPE types are not returned correctly because the row type is not referenced from the TYPE_NAME / TYPE_SUBNAME columns.

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