Is there any way of listing down all of the parameters necessary to invoke a stored procedure (with an Oracle DB)?
For instance, I have a function called x that someone else has developed and now I have to call it but I don't know which parameters x expects and which of them are mandatory or not.
Depending on the privileges granted, you can query all_arguments or user_arguments or starting from 11g.r1 dba_arguments to list arguments and other arguments-related information of stored procedures:
Example:
SQL> create or replace procedure ProcX(
2 p_par1 in number,
3 p_par2 in varchar2 default 'default',
4 p_par3 out number
5 )
6 is
7 begin
8 null;
9 end;
10 /
Procedure created
select t.object_name
, t.argument_name
, t.position
, t.data_type
, t.defaulted
, t.in_out
from user_arguments t
where t.object_name = 'PROCX'
Object_Name Argument_Name Position Data_Type Defaulted In_Out
--------------------------------------------------------------
PROCX P_PAR3 3 NUMBER N OUT
PROCX P_PAR2 2 VARCHAR2 Y IN
PROCX P_PAR1 1 NUMBER N IN
Value Y in the Defaulted column indicates that the parameter p_par2 has a default value thus optional.
Even one can find subroutine arguments from %_ARGUMENTS-tables (as demonstrated by Nicholas) in many cases that's probably not enough (depending on the context/complexity of the domain). Instead you should have access to a documented subroutine signature either by documentation or by viewing the subroutine (documented) code in the database (the GUI tools like SQL Developer, PL/SQL Developer and Toad are great for that) or from version control system.
If your starting point really is:
I have a function called x that someone else has developed and now I have to call it but I don't know which parameters x expects and which of them are mandatory or not.
And there is no documentation and you have no access to the source code then I doubt that information like:
Object_Name Argument_Name Position Data_Type Defaulted In_Out
--------------------------------------------------------------
PROCX P_PAR3 3 NUMBER N OUT
PROCX P_PAR2 2 VARCHAR2 Y IN
PROCX P_PAR1 1 NUMBER N IN
doesn't help you much (even if arguments have more descriptive names) unless the function is "trivial".
For your sake I hope this is a one-time issue only - in all other scenarios your project is very likely going to fail.
The question is tagged plsqldeveloper.
One of the great features of such a great product is to right click the method after finding it in the object browser. There are options for all manner of instant interrogation without writing any queries.
