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

metadata, oracle, oracle10g

Solution

`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;

Problem

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?

Original source