CODEWITHSUNDEEP
oracle-databasefunctiondatabase-schemaprocedures
gruszczy
gruszczy

Reputation: 42168

oracle: select information about a function/procedure

I would like to retrieve information about procedures/functions from Oracle database. I know, that I can use ALL_PROCEDURES and USER_OBJECTS tables, but they only inform me about names of existing procedures/functions. I would like to get return type and argument without parsing any source. Is that possible under Oracle? It's pretty easy under PostgreSQL and can be done under MySQL (argument under 5.5 AFAIK).

Upvotes: 1

Views: 1039

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

It sounds like you want the ALL_ARGUMENTS view (or USER_ARGUMENTS or DBA_ARGUMENTS)

SQL> create or replace function f1( p_in in varchar2,
  2                                 p_in_out in out number )
  3    return number
  4  is
  5  begin
  6    return 1;
  7  end;
  8  /

Function created.

SQL> ed
Wrote file afiedt.buf

  1  select argument_name, position, in_out, data_type
  2    from all_arguments
  3   where object_name = 'F1'
  4*    and package_name is null
SQL> /

ARGUMENT_N   POSITION IN_OUT    DATA_TYPE
---------- ---------- --------- ----------
P_IN_OUT            2 IN/OUT    NUMBER
P_IN                1 IN        VARCHAR2
                    0 OUT       NUMBER

Upvotes: 4

Related Questions