1

I need to input an array of ID (which is of NUMERIC type) and value (which is of VARCHAR2 type) as a parameter to a stored procedure. The Structure is given below

TYPE plan_dtl AS OBJECT(
p_id NUMERIC,
p_value VARCHAR2
);
TYPE plan_tbl AS TABLE OF plan_dtl;
PROCEDURE sp_update(p_name IN OUT VARCHAR2, p_array IN pk_manage.plan_tbl);

Can anyone tell me how to set an array of values in the position 2 using java ?

CallableStatement cs = conn.prepareCall("{call package_name.sp_update(?,?)}");  
cs.registerOutParameter(1,Types.CHAR);
cs.setString(1, "some name");  
cs.setArray(2, "how to pass it here ?")

sample inputs will be like

id(NUMERIC) value(VARCHAR2) 

[{88627,"02"},{88671,"01"},{88644,"05"}]

all these values should be passed as an array to the callable statement at position 2

Athul M R
  • 301
  • 2
  • 9
  • Possible duplicate of [How to pass PreparedStatement setArray for character array](https://stackoverflow.com/questions/13973212/how-to-pass-preparedstatement-setarray-for-character-array) – Jamey Jun 23 '17 at 12:54
  • Hi @Jamey I have went through this one already. but my case is different. its not an array of character. its an array of **id** and **values** where id is a numeric type and value is varchar2 type – Athul M R Jun 23 '17 at 13:00
  • Ah OK, so are you looking for a dictionary method then? I imagine you would have to pass two separate arrays, one for the keys and one for the values. It's a bit outside my expertise though, so hopefully someone else can help you. – Jamey Jun 23 '17 at 13:05
  • Searching about this since a while, have tried with HashMap as a arraylist too, but it doesn't work like that also. Since there is only one parameter in the procedure I think I wont be able to pass two separate arrays – Athul M R Jun 23 '17 at 13:11
  • This is for C#, but I believe the same principals should apply: [https://stackoverflow.com/questions/19957132/pass-dictionarystring-int-to-stored-procedure-t-sql](https://stackoverflow.com/questions/19957132/pass-dictionarystring-int-to-stored-procedure-t-sql). When you say there is only one parameter in the procedure, are you unable to modify the procedure? – Jamey Jun 23 '17 at 13:20
  • No, I don't have access for modifying the procedure. can you suggest a better way to create the procedure as well ? – Athul M R Jun 29 '17 at 07:42
  • If you don't have access to modify the procedure, do you have a reference to an existing implementation? If you can't modify the procedure, then I assume that's because it's already in use? I'm not sure what you mean by a better way to create the procedure, Are you suggesting creating a new stored procedure to fit your purpose? – Jamey Jun 29 '17 at 12:26
  • Yea for creating a new stored procedure to fit the purpose. Its a newly created procedure, which is created by another team. I don't have direct access for modifying the procedure, but I can suggest them to change. – Athul M R Jun 29 '17 at 13:55

0 Answers0