ALL_ARGUMENTS missing information?

I made a interesting discovery earlier this week when I was doing some analysis for some AskTom database procedures when prepping some regression tests. Here is a quick summary so that you won’t get the surprise I got.

In 11g, if you create a packaged type and procedure that references that type, for example as per below:

SQL> create or replace
  2  package PKG is
  3    TYPE  my_RefCur IS REF CURSOR   -- packaged type
  4           RETURN  emp%ROWTYPE;
  5  end;
  6  /

Package created.

SQL> create or replace
  2  procedure my_proc( rc out PKG.my_RefCur) is  -- reference to that type
  3  begin
  4    open rc for
  5        select * from emp;  -- the true query
  6  end;
  7  /

Procedure created.

Then the database stores extra information about the interaction in the dictionary. From the query to ALL_ARGUMENTS below, you can see that the database stores the fact that the procedure MY_PROC returns a parameter called RC (level 0), but because this is a strongly typed REF CURSOR, we also can see that this RC parameter when “drilled into” consists of additional elements (EMPNO, ENAME, etc), which are the columns of the EMP table because the RC definition in the package is “RETURN emp%ROWTYPE”.

SQL> select argument_name, in_out, data_level
  2  from   all_arguments
  3  where  object_name = 'MY_PROC'
  4  order by data_level, sequence;

ARGUMENT_NAME                  IN_OUT    DATA_LEVEL
------------------------------ --------- ----------
RC                             OUT                0
                               OUT                1
EMPNO                          OUT                2
ENAME                          OUT                2
JOB                            OUT                2
MGR                            OUT                2
HIREDATE                       OUT                2
SAL                            OUT                2
COMM                           OUT                2
DEPTNO                         OUT                2

I occasionally used that information in ALL_ARGUMENTS to check on how columns might be used in stored procedures etc. However, in 19c, when I ran the same query, I now see this:

SQL> select argument_name, in_out, data_level
  2  from   all_arguments
  3  where  object_name = 'MY_PROC'
  4  order by data_level, sequence;

ARGUMENT_NAME                  IN_OUT    DATA_LEVEL
------------------------------ --------- ----------
RC                             OUT                0

The drill down information is no longer stored, only the top level definition. It turns out that this change is documented here in the changes to 18c and beyond. If you had multiple levels of nested complex types, then populating all that information into ALL_ARGUMENTS could easily become the dominant factor in compilation time for a PL/SQL routine.

If you need that information, then in 18c onwards you can set event event 10946 at level 65536 to get the old behaviour back. But you’ll take a slight performance hit on compilation.