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.

Please explain your problem statement by giving an example.. its not clear in current form. — Sanjeev
– Sanjeev, Commented Jun 15, 2016 at 7:31
@Sanjeev Would like to use LIst collection and pass List as argument and pass to callProc method and convert List collection to an array so that it can be passed to database procedure. — Jacob
– Jacob, Commented Jun 15, 2016 at 18:39

Najeeb Arif · Accepted Answer · 2016-06-21 11:52:02Z

1

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

edited Jun 21, 2016 at 11:52

answered Jun 18, 2016 at 19:39

Najeeb Arif

4123 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jacob Over a year ago

I will consider your suggestion for changing the PL/SQL procedure to make it more efficient as now the number of db calls is more. If I alter the procedure to have a collection and table type, how the Java method would be?

Najeeb Arif Over a year ago

I will further Edit the Post with proper code snippets

Jacob Over a year ago

Thanks for the insight. I can understand your approach to achieve using OBJECT. How can I use the POJO class and use List to pass all the values to Java procedure to pass values to database?

Najeeb Arif Over a year ago

I have added the code for calling th eDB procedure form the Java code. For thais we will create a Callable statement for that particular procedure and pass in all the required parameters to the prepared callable statement. It will automatically route everything to the DB proc.

Colin Shah · Accepted Answer · 2016-06-14 11:07:45Z

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(); } }

gene b. · Accepted Answer · 2023-09-18 19:32:36Z

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

Collectives™ on Stack Overflow

From Java Passing Values as Array to Database Stored Procedure

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related