Custom type as input parameter in callable statement (PL/SQL)

Certain times in PL/SQL function, the input type could be of TYPE  example: table type

example :
CREATE OR REPLACE TYPE "SAMPLE_TYPE_TAB" as table of number(17,0);

In oder to send the corresponding object in java, we need to construct the corresponding object which is of java.sql.Struct in java.

the sample pl/sql could be ( skeleton shown)

CREATE OR REPLACE PACKAGE  test_package IS

FUNCTION inputAsType(type_tab sample_type_tab,



creator VARCHAR2) RETURN NUMBER IS



num NUMBER;
 
here the input parameter type_tab is of type "SAMPLE_TYPE_TAB" defined above.
as per the defintion it has only one column of type number.
 
lets see how we can build the callable statement to invoke the above function.
 
1. build the connection
 
Class.forName(driver);



Connection con = DriverManager.getConnection(url + db, user, pass);
2. define the package and function name as string
String packagefunction = "{? = call test_package.inputAsType(?,?)}";

3. build the object for sample_tab_type equivalent in java.

         Object table[] = new Object[1];



                    Object rows[] = new Object[2];
                     //constructing 2 rows of data of type SAMPLE_TYPE_TAB
                      rows[0] = new BigDecimal("4006");


                       rows[1] = new BigDecimal("4007");
       
          table[0] = rows;
 
 4. call the createStruct method in connection object and pass the sqltype and object array constucted in previous step.
 
  Struct sampleTypeTab= con.createStruct("SAMPLE_TYPE_TAB", table); //need to use oracle 11.1.0.7 driver classes to support this feature
 
5.  call precall function by passing the package and function string constructed in step2.
 
    CallableStatement callableStatement = con.prepareCall(packagefunction);


6. set the input and register output variables.
       
callableStatement.setObject(2, sampleTypeTab, Types.STRUCT);


callableStatement.setString(3, "tester");


callableStatement.registerOutParameter(1, Types.NUMERIC);

7. call executequery on callable statement.
callableStatement.executeQuery();
8. get the output
Bigdecimal result = callableStatement.getBigDecimal(1);
 
Coming soon .. what if the output is of table type