1

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.

Kid101
  • 1,440
  • 11
  • 25
  • "recursive calls to the..." you are not using the word "recursive" correctly – ControlAltDel Aug 16 '16 at 18:14
  • Possible duplicate of [pass array to oracle procedure](http://stackoverflow.com/questions/5198856/pass-array-to-oracle-procedure) – OldProgrammer Aug 16 '16 at 18:15
  • ControlAltDelete done it. Thanks. @OldProgrammer I cannot change anything in the PLSQL Function. I've no access. – Kid101 Aug 16 '16 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 Aug 16 '16 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 Aug 16 '16 at 19:59
  • It's not a duplicate. He's looking for a way to pass an array when the PL/SQL function doesn't accept one. – Matthew McPeak Aug 16 '16 at 21:33
  • 1. You can create new function which accepts table type parameter (e.g. `CREATE TYPE T_NUMBER_TABLE IS TABLE OF NUMBER(18)`) and pass it using ArrayDescriptor - [Example](http://viralpatel.net/blogs/java-passing-array-to-oracle-stored-procedure/). 2. You can build anonymous PL/SQL block in Java calling your function inside it as many times as needed and retreiving the same amount of out parameters. IMPORTANT: I don't recommend the second solution, because it is very probable to generate bugs, is way harder to maintain and creates different statement every time (may affect the performance). – AndrewMcCoist Aug 24 '16 at 12:13
  • @AndrewMcCoist I went with the first approach. – Kid101 Aug 24 '16 at 16:36

1 Answers1

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.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • Thank you for the response. I'm a newbie to PLSQL but have learnt a lot in two days. Thanks. – Kid101 Aug 18 '16 at 16:38