How to specify IN parameter that as Oracle data type of TABLE type?
java, oracle, oracle10g
Solution
As your type has a constructor defined, I am not sure if this will work exactly, but I have posted samples on how to do this over on my blog. First, how to pass a record type into Oracle from Java:
http://betteratoracle.com/posts/31-passing-record-types-between-oracle-and-java
And then extended it to pass arrays of records into Oracle from Java:
http://betteratoracle.com/posts/32-passing-arrays-of-record-types-between-oracle-and-java
Problem
Based on this previous question on stackoverflow: Fetch Oracle table type from stored procedure using JDBC The answer gave us a sample of OUT parameter using Oracle getArray and java.sql.Datum. But what if I want to specify IN parameter that has data type of Oracle TABLE type? ``` CREATE OR REPLACE TYPE XXINV.XX_PROD_SRCH_RSLT_REC_TYPE IS OBJECT( item_no VARCHAR2(30), inventory_item_id NUMBER, organization_id NUMBER, item_description VARCHAR2(240), item_long_description VARCHAR2(240), cat_description VARCHAR2(240), category_set_name VARCHAR2(240), nla_flag VARCHAR2(1), CONSTRUCTOR FUNCTION XX_PROD_SRCH_RSLT_REC_TYPE RETURN SELF AS RESULT , MEMBER PROCEDURE log_prod_srch_rslt_rec_values (SELF IN XX_PROD_SRCH_RSLT_REC_TYPE) ) CREATE OR REPLACE TYPE xxinv.XX_PROD_SRCH_RSLT_TAB_TYPE AS TABLE OF XX_PROD_SRCH_RSLT_REC_TYPE; ``` And my SP is this: ``` XX_PART_RESEARCH_PKG .GET_PARTS (p_called_from IN VARCHAR2, p_item_id IN NUMBER, p_category_id IN NUMBER, p_mnfg_part_id IN NUMBER, p_item_desc IN VARCHAR2, p_include_NLA_items IN VARCHAR2, p_catl_group_id IN NUMBER, p_catl_attributes IN XX_PROD_ATTR_TAB_TYPE, x_srch_rslt IN XX_PROD_SRCH_RSLT_TAB_TYPE, x_return_status OUT VARCHAR2, x_returb_msg OUT VARCHAR2 ) ``` where as the XX_PROD_ATTR_TAB_TYPE is a table as IN parameter. How to specify this in Java? I'm using Oracle 10g release 2.