CODEWITHSUNDEEP
c#sqloracle-databasestored-proceduresoracle11g
Raam
Raam

Reputation: 11

How to get the list of parameters of the supplied Stored procedure in oracle 11g

I need a query in oracle 11g to get the list of all parameters, their data-type and their mode(IN or OUT) for a given Stored Procedure.

Upvotes: 0

Views: 878

Answers (3)

Hamidreza
Hamidreza

Reputation: 3128

You can take a look at ALL_ARGUMENTS or USER_ARGUMENTS Tables.

SELECT ARGUMENT_NAME,DATA_TYPE,IN_OUT 
FROM USER_ARGUMENTS WHERE OBJECT_NAME = UPPER('ProcedureName');

Upvotes: 4

Ravi.
Ravi.

Reputation: 674

try the following query

 select * from user_source where type = 'PROCEDURE'

This will give you all the stored procedures stored in your schema. Alternatively, you can also define type as Function or Packages to get required information.

Upvotes: 0

Madara Uchiha
Madara Uchiha

Reputation: 126

If you would like to get a view about the parameters of the procedure, just use

 SQL> desc <procedure name>;

If you would like to see the code for the procedure, then use (assuming you are logged in as the owner of the procedure)

 SQL> SELECT Text FROM User_Source WHERE Name ='PROCEDURENAME' ORDER BY Line;

Upvotes: 0

Related Questions