Here are a few examples:
- ALL_OBJECTS - information about all database objects which can be accessed by the current user.
- ALL_ARGUMENTS - information about every argument of every packaged subprogram and schema-level program unit for which the current user has EXECUTE authority.
- ALL_IDENTIFIERS - information about identifiers in program units, gathered by the Oracle Database 11g PL/Scope feature.
ALL_ARGUMENTS lists the arguments of the procedures and functions that are accessible to the current user. USER_ARGUMENTS, as you might know or guess, offers information about those arguments defined in procedures and functions in the currently connected schema.
This view gives developers access to all sorts of interesting information, allowing them to create queries to perform checks on their code.
You can, for example, determine if any arguments of an "undesireable" type, such as CHAR or LONG. You can also find out if a subprogram is a procedure or a function.
You can also determine if a subprogram is a function or a procedure (though this is now also possible through PL/Scope and ALL_IDENTIFIERS). Here's an OracleLiveSQL script showing this usage.
As you likely know, an argument could be a scalar (number, date, string. etc.) or a composite (consisting of multiple "pieces", such as a record or object type). Up until Oracle Database 12c Release 2, the way that ALL_ARGUMENTS recursively expanded composites. In other words, that view contained one row for the argument itself and then one row for each attribute of an object type or field of a record. You can see this below.
I create a table, then a function that accepts a record as an in parameter.
Run this came code in 18c and above, and the count of rows is just 4. The following script shows a bit more detail.
CREATE TABLE plch_employees ( employee_id INTEGER , last_name VARCHAR2 (100) , first_name VARCHAR2 (100) , date_of_birth DATE , hire_date DATE , salary NUMBER ) / CREATE OR REPLACE FUNCTION plch_get_info ( employee_in IN plch_employees%ROWTYPE , info_type_in IN PLS_INTEGER , for_date_in IN DATE) RETURN BOOLEAN AUTHID DEFINER IS BEGIN RETURN NULL; END; / /* Verify choice correctness */ SELECT COUNT (*) arg_count FROM user_arguments WHERE object_name = 'PLCH_GET_INFO' / ARG_COUNT --------- 10
SQL> select OBJECT_NAME,ARGUMENT_NAME,POSITION 2 FROM user_arguments 3 WHERE object_name = 'PLCH_GET_INFO' 4 / OBJECT_NAME ARGUMENT_NAME POSITION -------------------- -------------------- ---------- PLCH_GET_INFO 0 PLCH_GET_INFO EMPLOYEE_IN 1 PLCH_GET_INFO INFO_TYPE_IN 2 PLCH_GET_INFO FOR_DATE_IN 3 4 rows selected. 12.2 SQL> select OBJECT_NAME,ARGUMENT_NAME,POSITION 2 FROM user_arguments 3 WHERE object_name = 'PLCH_GET_INFO' 4 / OBJECT_NAME ARGUMENT_NAME POSITION -------------------- -------------------- ---------- PLCH_GET_INFO 0 PLCH_GET_INFO EMPLOYEE_IN 1 PLCH_GET_INFO EMPLOYEE_ID 1 PLCH_GET_INFO LAST_NAME 2 PLCH_GET_INFO FIRST_NAME 3 PLCH_GET_INFO DATE_OF_BIRTH 4 PLCH_GET_INFO HIRE_DATE 5 PLCH_GET_INFO SALARY 6 PLCH_GET_INFO INFO_TYPE_IN 2 PLCH_GET_INFO FOR_DATE_IN 3
If you relied on that expanded data in ALL_ARGUMENTS in any of your QA scripts, you will now need to join with one of the following views: ALL_PLSQL_TYPES, ALL_PLSQL_TYPE_ATTRS, and ALL_PLSQL_COLL_TYPES.
I'll publish a post soon that explores how to do just that.