CODEWITHSUNDEEP
javaoracle-databasestored-procedures
abhijit nag
abhijit nag

Reputation: 381

Get stored procs OUT parameter name from java

Is it possible to get the OUT parameter name of Stored Procedure[Oracle] from java code. like

PROCEDURE Test(  
       ecm_property  OUT SYS_REFCURSOR,
       p_return_value  OUT NOCOPY  INT,
       p_error_message OUT NOCOPY  VARCHAR2) 
     AS  
     BEGIN..

From above, I would like to get the name "p_return_value" and "p_error_message". Could someone please give me some idea.

Upvotes: 0

Views: 155

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

I don't think there's a way to get it as metadata directly from Java, but you can query the data dictionary to get this information:

select argument_name,  data_type, in_out
from user_arguments
where package_name is null
and object_name = 'TEST'
order by subprogram_id, position;

ARGUMENT_NAME                  DATA_TYPE                      IN_OUT  
------------------------------ ------------------------------ ---------
ECM_PROPERTY                   REF CURSOR                     OUT       
P_RETURN_VALUE                 NUMBER                         OUT       
P_ERROR_MESSAGE                VARCHAR2                       OUT

If it's part f a package then give that as the package_name; if it isn't then it's safer to specify that is null in case the same name is used elsewhere. If the procedure is overloaded then you'll get several results with similar or identical names; I've ordered by subprogram_id so they are at least grouped together.

if it isn't in your schema then you can look in all_arguments instead, but specify the owner in the query as well.

Upvotes: 1

Related Questions