CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
WBAR
WBAR

Reputation: 4984

SQL or PLSQL to fetch procedure parameters and types

Is there any way to fetch parameters used by stored procedure (can by sample of SQL of PL/SQL)?

From view USER_PROCEDURES I can check only flags, existence and permission to execute but nothing about input parameters and their types.

Upvotes: 0

Views: 138

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

[dba][all][user]_arguments data dictionary view allows you to view arguments of stored procedures:

/* test procedure */
create or replace procedure P1(
   p_par1 in number, 
   p_var2 in number, 
   p_cursor out sys_refcursor
)
as
begin
  null;
end;


/* list all formal parameters of P1 stored procedure */
select argument_name
     , t.position
     , t.data_type
  from user_arguments t
 where object_name = 'P1'

Result:

Argument_Name Position  Data_Type 
---------------------------------
P_CURSOR      3         REF CURSOR 
P_VAR2        2         NUMBER 
P_PAR1        1         NUMBER

Upvotes: 4

Related Questions