How to get parameters properties from a stored procedure in Oracle?

Question

I am developing an admin panel where we can determine which stored procedures and views can be called in Oracle 12c schemas, from services in our micro-services platform.

The services must know which parameters a procedure needs, and which columns a view has available, in order to call them.

I the admin panel, the creator of the procedure or view may register it, by typing in these information, like object's name, each parameter's name, length and data type.

But it would be much more elegant if the user just types the name of the object and then a SQL SELECT would retrieve a procedure's parameters properties in a table, and the same with the view's column's properties, so the panel would register those configurations automatically.

Could anybody post a query on how to achieve this? I am very new to Oracle and I don't know how to query the objects metadata.

Answer 1

I believe your are looking for something like this.

SELECT *
  FROM SYS.DBA_PROCEDURES
  WHERE OBJECT_TYPE = 'PROCEDURE' 
  AND   OBJECT_NAME = 'xxxx' 

Once you have that, you can get the parameters from something like this.

SELECT *
  FROM SYS.ALL_ARGUMENTS  
  where object_name  = 'procedure_name';