CODEWITHSUNDEEP
c#oracle-databasestored-procedures
regal
regal

Reputation: 161

How to get stored procedure parameter types and sizes in Oracle?

To call a stored procedure with output parameters from C#, I need to obtain the datatype and the size of the respective parameters. I am using the Oracle.DataAccess library. How can I get this information from stored procedure metadata on an Oracle database?

Someone gave the tables to query for SQL Server in this answer (How to determine size property for stored procedure output parameters in C# data access layer), however I'm looking to call stored procedures in Oracle.

Upvotes: 2

Views: 1781

Answers (2)

Jon Waterhouse
Jon Waterhouse

Reputation: 96

I'd suggest a little modification:

select  a.package_name,a.OBJECT_NAME,a.argument_name, a.data_type, a.sequence, a.in_out, a.data_length, a.data_precision, a.Data_scale, a.radix
from USER_ARGUMENTS a 
join user_OBJECTS O ON O.OBJECT_ID = A.OBJECT_ID
where a.OBJECT_NAME = 'your stored procname'
and o.OBJECT_TYPE in ('PACKAGE','PROCEDURE')
order by a.object_id,sequence;

If you have a procedure called Get_ID (for example), and you also have a procedure within a package called Get_ID, the object_name for both of them is the same, so you will get the arguments for both the "real" procedure and the procedure within the package. If you want both, then leave the two object types there, if you only want the "real" procedure, then delete 'PACKAGE'

Upvotes: 0

T.S.
T.S.

Reputation: 19394

Here is the query

select  a.OBJECT_NAME, data_type, sequence, in_out, data_length, data_precision, Data_scale, radix
from USER_PROCEDURES P inner join USER_ARGUMENTS a on P.OBJECT_NAME = a.OBJECT_NAME
where a.OBJECT_NAME = 'Stored Proc name'
order by sequence

Upvotes: 3

Related Questions