How do I differentiate between Procedures and Functions in Oracle's Metadata?

Question

I want to list all the Stored Procedures which use overloading in a given schema. All the procedures are within packages. I can use the SQL below to nearly get there (anything with proc_count > 1).

select 
    object_name, procedure_name, count(procedure_name) as proc_count 
from 
    all_procedures                
where 
    owner = 'SCHEMA_NAME' 
group by
    object_name, procedure_name
order by proc_count desc   

However there seems to be no way to differentiate between a function named 'ask_version' and a procedure named 'ask_version' which I need to do in my case. The case being that our middleware has trouble calling procs where overloading is used. I need to do an impact analysis on how many places this occurs. We never call functions directly, hence the need to isolate them

Is there something that I'm missing?

Answer 1

all_arguments seems to help. For a function, there is an argument with position=0 (which is the return value), for procedures this argument does not exist.

SELECT object_name, procedure_name, t, COUNT(1) AS proc_count
FROM
(
  SELECT p.object_name, p.procedure_name,
         CASE WHEN a.object_id IS NULL THEN 'PROCEDURE' ELSE 'FUNCTION' END AS t
  FROM all_procedures p
  LEFT JOIN all_arguments a ON ( a.object_id = p.object_id
                             AND a.subprogram_id = p.subprogram_id AND a.position = 0 )
  WHERE p.owner = 'SCHEMA_NAME'
)
GROUP BY object_name, procedure_name, t
ORDER BY proc_count DESC;

Answer 2

This is a bit tricky since Oracle stores packages as discrete objects from standalone functions and procedures.

The only easy way you can tell this is by looking at the argument metadata in ALL_ARGUMENTS. Functions have an argument at position 0 to specify the return type, whereas procedures do not.

Also, it's easy to tell if a function or procedure has overloads by checking the OVERLOAD field.

select P.OBJECT_NAME
,      P.PROCEDURE_NAME
,      DECODE(min(a.POSITION), 0, 'Function', 'Procedure') FUNC_OR_PROC
,      DECODE(NVL(min(P.OVERLOAD), 0), 0, 'No', 'Yes') OVERLOADED
from   ALL_PROCEDURES P
,      ALL_ARGUMENTS a
where  P.OWNER = 'FLOWS_030000'
and    P.OBJECT_NAME = a.PACKAGE_NAME 
and    P.PROCEDURE_NAME = a.OBJECT_NAME
group  by P.OBJECT_NAME, P.PROCEDURE_NAME
order  by 1,2;