Identifying functions and procedures without arguments
I wanted to find a report on all procedures in my schema, that accepted zero arguments.
There are two views that are useful here:
Then, we can get functions:
Giving us a consolidated query for functions and procedures of:
Then we can do the packages like so:
There are two views that are useful here:
- USER_PROCEDURES
- USER_ARGUMENTS
With USER_PROCEDURES, if the row is referring to a subprogram in a packaged, object_name is the package name, and procedure_name is the name of the subprogram. With any other subprogram out of the context of a package, the object_name is the name of the subprogram and procedure_name returns NULL.
With user_argument, object_name becomes the name of the subprogram, with package_name being NULL when you are not dealing with a package's subprogram.
In the case of subprograms out of the package context, no rows are returned in the user_arguments view. That differs from a subprogram in a package - you get a row, but argument_name is set to NULL. You will never get a NULL argument if there is at least one argument.
In the case of functions, you will get an additional argument with argument_name set to NULL that relates to the return type.
So with that information in mind, we can identify procedures like:
select user_procedures.object_name from user_procedures left outer join user_Arguments proc_arguments on (user_procedures.object_name = proc_arguments.object_name ) where proc_arguments.object_id IS NULL and user_procedures.object_Type = 'PROCEDURE'
Then, we can get functions:
with func_arg_count as ( select func_args.*, count(*) over (partition by func_args.object_id) arg_count from user_procedures left outer join user_Arguments func_args on (user_procedures.object_name = func_args.object_name ) where user_procedures.procedure_name IS NULL and func_args.package_name is null and user_procedures.object_Type = 'FUNCTION' ) select object_name from func_arg_count where argument_name is null and data_Type is not null and arg_count = 1
Giving us a consolidated query for functions and procedures of:
with func_arg_count as ( select user_procedures.object_name , user_procedures.object_id , user_procedures.object_Type , func_args.argument_name , func_args.data_Type --func_args.* , count(case when func_args.object_id is not null then 1 end) over (partition by user_procedures.object_id) arg_count from user_procedures left outer join user_Arguments func_args on (user_procedures.object_name = func_args.object_name ) where user_procedures.procedure_name IS NULL and func_args.package_name is null ) select object_name from func_arg_count where ( ( object_Type = 'FUNCTION' and argument_name IS NULL and ARG_COUNT = 1) or ( object_Type = 'PROCEDURE' and arg_count = 0 ) )
Then we can do the packages like so:
with pkgs as ( select user_procedures.object_name , user_procedures.object_id , user_procedures.object_Type , user_procedures.procedure_name , func_args.in_out , func_args.argument_name , func_args.data_Type , func_args.subprogram_id , count(*) over (partition by user_procedures.object_id, func_args.subprogram_id) arg_count from user_procedures join user_Arguments func_args on (user_procedures.object_name = func_args.package_name and user_procedures.procedure_name = func_args.object_name) where user_procedures.object_Type = 'PACKAGE' ) select object_name pkg, procedure_name from pkgs where argument_name is NULL and arg_count = 1