How to call PLSQL function from java

Question

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.

"recursive calls to the..." you are not using the word "recursive" correctly — ControlAltDel
– ControlAltDel, Commented Aug 16, 2016 at 18:14
ControlAltDelete done it. Thanks. @OldProgrammer I cannot change anything in the PLSQL Function. I've no access. — Kid101
– Kid101, Commented Aug 16, 2016 at 18:22
If the pl/sql function takes in a scalar parameter, then there is no way to "pass in a list" without modifying the function. You will need to prepare the call, and loop through the list, calling SetInt, and execute repeatedly. There is no alternative. — OldProgrammer
– OldProgrammer, Commented Aug 16, 2016 at 18:40
You cannot change the PL/SQL function, but maybe you can create a new one? This way you can move the for loop from the application to the DB, where it's much cheaper. — korolar
– korolar, Commented Aug 16, 2016 at 19:59

Matthew McPeak · Accepted Answer · 2016-08-16 21:32:58Z

2

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.

answered Aug 16, 2016 at 21:32

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kid101 Over a year ago

Thank you for the response. I'm a newbie to PLSQL but have learnt a lot in two days. Thanks.

Collectives™ on Stack Overflow

How to call PLSQL function from java

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related