CODEWITHSUNDEEP
oracle-databasestored-proceduresvieworacle12c
NaN
NaN

Reputation: 9094

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

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.

Upvotes: 7

Views: 13067

Answers (2)

etsa
etsa

Reputation: 5060

This can be used for views (I used USER_... metadata views, but you can use DBA_... depending of user grants):

SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE
  FROM USER_TAB_COLUMNS A
  INNER JOIN USER_VIEWS B ON A.TABLE_NAME = B.VIEW_NAME
/* WHERE A.TABLE_NAME = 'xxxx' */
 ORDER BY TABLE_NAME, COLUMN_ID;

Upvotes: 1

Miguel
Miguel

Reputation: 2079

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';

Upvotes: 17

Related Questions