How to select everything from all_objects using different schemas at once - PL/SQL

Bittnutz 0
Viewed 35

Let's say that I have few schemas A, B, C, D (in reality they are like 10+). I'm trying to search for a specific data type in all of my schemas and I'm using the system table ALL_OBJECTS. The thing which I hope I can sidestep is executing the same query:

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE '%something%';

manually through all schemas.

Is there an easy way to execute the script above for all of the schemas at once? Thanks in advance!

oracle12c plsql
1 Answers

Try out this one:

SELECT PROC.OWNER          AS SCHEMA_NAME,
       PROC.OBJECT_NAME    AS PROCEDURE_NAME,
       ARGS.ARGUMENT_NAME,
       ARGS.IN_OUT,
       ARGS.DATA_TYPE,
       ARGS.DATA_LENGTH,
       ARGS.DATA_PRECISION,
       ARGS.DATA_SCALE,
       ARGS.DEFAULTED,
       ARGS.DEFAULT_VALUE
  FROM SYS.ALL_PROCEDURES PROC
       LEFT JOIN SYS.ALL_ARGUMENTS ARGS ON PROC.OBJECT_ID = ARGS.OBJECT_ID
 WHERE PROC.OBJECT_TYPE = 'PROCEDURE'
   AND PROC.OBJECT_NAME LIKE '%something%'
 ORDER BY SCHEMA_NAME, PROCEDURE_NAME, ARGS.POSITION;

This will give you all procedures in all schemas and list them by argument list with types.

Gnqz0
Related