From Java Passing Values as Array to Database Stored Procedure

Question

I have the following stored procedure in database

CREATE OR REPLACE PROCEDURE my_proc (
   my_array         IN     my_array_type,
   my_var    IN OUT VARCHAR2)
   ....
   ....

In Java I have the following code snippet to invoke the above stored procedure

 public void callProc(String prodCode, 
                      String prodName, 
                      String prodDesc, 
                      ) {
            callableStatement = 
            this.getOADBTransaction().getJdbcConnection().prepareCall("{call my_proc (?,?)}");
    Object[] object = 
      new Object[] { prodCode, prodName, prodDesc};
StructDescriptor structDescriptor = 
  StructDescriptor.createDescriptor("my_array_type",this.getOADBTransaction().getJdbcConnection());
STRUCT struct = 
 new STRUCT(structDescriptor, this.getOADBTransaction().getJdbcConnection(), 
object);
STRUCT[] structArray = { struct };
ArrayDescriptor arrayDescriptor = 
ArrayDescriptor.createDescriptor("my_array",this.getOADBTransaction().getJdbcConnection());

ARRAY array = 
new ARRAY(arrayDescriptor, this.getOADBTransaction().getJdbcConnection(), 
structArray);
callableStatement.setArray(1, array);
callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.execute();
....

The above method is called from another class which is inside a for loop

for(....){

Serializable[] param = 
{ prodCode, prodName, prodDesc};
db.callProc(param )
}

What I would like achieve is instead of calling db.callProc inside the for loop, I would like to use a ListArray or some other collection object and pass the value to db.callProc method and in db.callProc method I would like to iterate and pass to database procedure as an array so that my stored procedure can handle the array and do the processing.

Answer 1

Unwrap the OracleConnection and then do createOracleArray.

If you have a custom Array type defined in Oracle as e.g. below:

create or replace TYPE   "REF_TYPE_ARRAY"  IS TABLE OF NUMBER

Then in Java's StoredProcedure implementation you can do the following, when you (1) unwrap the OracleConnection and then (2) do createOracleArray.

@Component public class MyStoredProcedure extends StoredProcedure {

public MyStoredProcedure(@Autowired DataSource dataSource) {
    super(dataSource, "MY_PKG.MY_STORED_PROC"); // Name

    declareParameter(new SqlParameter("itemIds", OracleTypes.ARRAY, "REF_TYPE_ARRAY"));
    // etc. other parameters

}

public void execute(ParamObject paramObject) throws Exception{
    Map<String, Object> inputParameters = new HashMap<>();

OracleConnection oracleConnection = getJdbcTemplate().getDataSource().getConnection().unwrap(OracleConnection.class);
Array array = oracleConnection.createOracleArray("REF_TYPE_ARRAY", referralTypeId);

inputParameters.put("itemIds", array); 
// etc. other params

Answer 2

First of all We will create a Pojo

public class ParamHolder{

   private String param1;

   private String param2;

   private String param3;

   //getters and setters.
}

On the DB side create an Object of the same type

CREATE OR REPLACE TYPE PARAM_HOLDER_OBJ 
AS OBJECT ( PARAM1 VARCHAR2(200), PARAM2 VARCHAR2(200), PARAM3 VARCHAR3(200));

Once you created the Object lets create a table of these objects

CREATE OR REPLACE TYPE PARAM_HOLDER_OBJ_TABLE
IS TABLE OF PARAM_HOLDER_OBJ

and our procedure may take input parajm like

custom(p_param_holder_tab IN TYPE PARAM_HOLDER_OBJ_TABLE)

lets for instance assume our proc looks something like this now what we need to do is to call this proc from our java code and pass in a Array of ParamHolder.

Code Snippet:
//variable declaration
//ParamHolder[] paramHolders = ..getTheParamHolderArray();
        try (Connection con = createConnWithDbDetails(getDBDetails());
             CallableStatement stmnt =
             con.prepareCall("{ call custom(?) }")) {
            //Create a arrayDescriptor
            ArrayDescriptor descriptor =
                ArrayDescriptor.createDescriptor("PARAM_HOLDER_OBJ_TABLE", con);
            Array array = new ARRAY(descriptor , con, paramHolders);
            stmnt.setArray(1, array );
          }Catch(Exception e){
              e.printStackTrace();
          }

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ EDITED-- 21st June 2016 For adding the calling method. 5 pm IST.

What you can do is you can create a method like this

public void callProc(ParamHolder[] paramHolders){

try (Connection con = createConnWithDbDetails(getDBDetails());
             CallableStatement stmnt =
             con.prepareCall("{ call custom(?,?) }")) {
            //Create a arrayDescriptor
            ArrayDescriptor descriptor =
                ArrayDescriptor.createDescriptor("PARAM_HOLDER_OBJ_TABLE", con);
            Array array = new ARRAY(descriptor , con, paramHolders);
            stmnt.setArray(1, array );
            stmnt.registerOutParameter(2,OracleType.VARCHAR,"p_out_var");           //Register any output variable if your procedure returns any thing.
            stmmnt.execute();                                              //this will take the Array of ParamHolder straight to the DB for processing.
            String result = stmnt.getString(2);                                  //Will fetch yuou the result form the DB to your local String.
          }Catch(Exception e){
              e.printStackTrace();
          }
}

which will take an array of ParamHolder class which will be passed directly to your db proc and you will have the results the accordingly. as the current proc defination does not specifies any out param but you can define and register of to catch that.

Lets Say you have a calling piece of code which will utilize it

public class ProcDaoImpl{

    public void executeProc(){

         ParamHolder[] paramArray = new ParamHolder[]{                                          //create an array of four elements
                                      new ParamHolder("param1","param2","param3"),
                                      new ParamHolder("param1","param2","param3"),
                                      new ParamHolder("param1","param2","param3"),            
                                      new ParamHolder("param1","param2","param3")                                                   //Each array element represents a set of InputParams
                                   }

     //call the DB procedure now..
     SomeClass.callProc(paramArray);                                                                     // pass in the created array to it.
    }

}

Hope it helps. :)

Thanks

Answer 3

this may help :

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor; 

public class TestSP{
    public static void arrayDataToSP()
    {
        try{
            Class.forName("oracle.jdbc.OracleDriver");

            Connection con = DriverManager.getConnection("jdbc:oracle:thin:url ","[user]","[password]");

            String alphabets[] = {"a", "b", "c","d","e","f","g"}; 

            //use ARRAY_TABLE as ArrayDescriptor 
            ArrayDescriptor des = ArrayDescriptor.createDescriptor("SchemaName.ARRAY_TABLE", con);

            ARRAY array_to_pass = new ARRAY(des,con,alphabets);

            CallableStatement cst = con.prepareCall("call SchemaName.my_proc(?,?)");

            // Passing an alphabets array to the procedure 
            cst.setArray(1, array_to_pass);
            st.registerOutParameter(2, Types.VARCHAR);
            cst.execute();

            // Retrive output of procedure execute
            ARRAY output = ((OracleCallableStatement)cst).getARRAY(2);

             BigDecimal[] outputArray = (BigDecimal[])(output.getArray());

            for(int i=0;i<outputArray.length;i++)
                System.out.println("element" + i + ":" + outputArray[i] + "\n");

        } catch(Exception e) {
            System.out.println(e);
        }
    }

public static void main(String args[]){ arrayDataToSP(); } }