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:

1. USER_PROCEDURES
2. 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