CODEWITHSUNDEEP
javaoracle-databaseplsqlcallable-statement
Kid101
Kid101

Reputation: 1470

How to call PLSQL function from java

I have a PLSQL FUNCTION. PKG_GAS_KT.GET_DEPTINFO('{UserID}') This function returns the name of the Department the user is in.

Now, I've a list of UserID in java. How can I pass all the UserId's and get the department of the respective user. Is there any way to do it without putting it inside a for loop and making unwanted calls to the database. Also:

    CallableStatement  callSt = con.prepareCall("{?= call PKG_GAS_KT.GET_DEPTINFO(?)}");`// Is this correct way to call my PLSQL function?
    callSt.setInt(1,101);
    callSt.registerOutParameter(2, Types.STRING);
    callSt.execute();
    Double output = callSt.getString(2);

Any guidance is appreciated. I cannot change anything in PLSQL function.

Upvotes: 1

Views: 1215

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Better to change your PL/SQL function. However, if you really need a kludge, you can try this:

Issue the following statement from Java as a JDBC query:

select ids.column_value id, 
       pkg_gas_gt.get_deptinfo(ids.column_value) deptinfo 
from   TABLE(SYSTEM.NUMBER_TBL_TYPE( ? ) ) ids;

... where ? is your parameter and is the JDBC array of user ids you want to pass in.

The result set of the query will be each ID along with the result of the function. The function needs to be callable from SQL for this to work. Most are, though.

Upvotes: 2

Related Questions