How can i pass arraylist to stored procedure

[bob]

can anybody help me in giving sample code & guidance as how to pass a arraylist of java objects to a stored procedure to oracle database using spring


Thanks in advance

[Costin Leau]

Haven't worked much with stored procedure but since you passing parameters as a map, you can simply get the values from the array and place them inside the map used by the stored procedure.

[trisberg]

Take a look at the following post. It gives you a couple of options of accomplishing this. The sample code is based on call to the following procedure:

CREATE OR REPLACE TYPE numbers AS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE reserve_seats(in_performance_id in number,
            in_seats in numbers,
            in_price in number,
            in_reserved_until in date,
            out_new_booking_id out number)
is
begin
  -- Get a new pk for the booking table
  select booking_seq.nextval into out_new_booking_id from dual;

  -- Create a new booking
  insert into booking(id, date_made, price, reserved_until)
     values (out_new_booking_id, sysdate, in_price, in_reserved_until);

  for i in 1..in_seats.count loop
    insert into seat_status (Seat_id, Performance_id)
       values(in_seats(i), in_performance_id);
    update seat_status set Booking_id = out_new_booking_id
                where Seat_id = in_seats(i)
                and Performance_id = in_performance_id;
  end loop;
end;
/

[bob]

Thanks for ur reply.
But its throwing a "Invalid Pattern" exception.

i am creating a TABLE of Oracle object type Like this:

create or replace type Contact_OBJ_ARRAY as table
of Contact_OBJ;

where the Contact_OBJ is defined as a oracle type like:

CREATE OR REPLACE TYPE Contact_OBJ AS OBJECT (
id number ,
firstname varchar2(30),
lastName varchar2(30),
email varchar2(30)
)


I have a bean called Contact in java like:

public class Contact {

private long id;
private String firstName;
private String lastName;
private String email;

/**setter & getter for the fields*/
}

Then my oracle stored procedure looks like following with one in parameter :

CREATE OR REPLACE PROCEDURE
Contact_OBJ_ARRAY_PROC ( p_obj_array in Contact_OBJ_ARRAY ) AS
begin
//do some work
end;

And i have extended the AbstractsqlType class also:

public class MyObjectArray extends AbstractSqlTypeValue {

private ArrayList values;



public MyObjectArray(ArrayList values) {
this.values = values;

}

public Object createTypeValue(Connection con, int sqlType, String typeName)
throws SQLException {
OracleConnection oracle = (OracleConnection) ((org.apache.commons.dbcp.PoolableConnection) con)
.getDelegate();
HashMap map=new HashMap();
try{
map.put("CONTACT_OBJ",Class.forName("Contact"));
}
catch(Exception e){
e.printStackTrace();
}
oracle.setTypeMap(map);
oracle.sql.ArrayDescriptor desc = new oracle.sql.ArrayDescriptor(typeName, oracle);

return new oracle.sql.ARRAY(desc, oracle, (Contact[]) values
.toArray(new Contact[values.size()]));
}
}

I am calling the porocedure as:

//iNSTANTIATE THE STORED PROC CLASS

final StoredProcedure proc=new StoredProcedure(getJdbcTemplate(),"Contact_OBJ_ARR AY_PROC"){};

HashMap param=new HashMap();

//create a new arraylist

ArrayList list=new ArrayList();

//create Contact objects & populate them

Contact c=new Contact();
c.setName("xyz");

//finally add the contact object to the list

list.add(c);

//declare the sotred proc parameters

proc.declareParameter(new SqlParameter("param1",TYPES.ARRAY,"Contact_OBJ_ARR AY") );
param.put("param1",list);

proc.compile();

//finally execute the proc by passing the hashmap to the execute method

proc.execute(param);

//IN FACT I HAVE A OracleSpringStoredProcedureDAO.executeProcedure() METHOD WHICH DOES ALL THIS.

****************************************
EXCEPTION TRACE
***************************************
Exception in thread "main"
org.springframework.jdbc.UncategorizedSQLException :
CallableStatementCallback; uncategorized SQLException for SQL [{call CONTACT_OBJ_ARRAY_PROC(?)}]; SQL state [null];
error code [17074]; invalid name pattern: ATS.CONTACT_OBJ_ARRAY;
nested exception is java.sql.SQLException: invalid name pattern: ATS.CONTACT_OBJ_ARRAY


Regards
bob

Take a look at the following post. It gives you a couple of options of accomplishing this. The sample code is based on call to the following procedure:

CREATE OR REPLACE TYPE numbers AS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE reserve_seats(in_performance_id in number,
            in_seats in numbers,
            in_price in number,
            in_reserved_until in date,
            out_new_booking_id out number)
is
begin
  -- Get a new pk for the booking table
  select booking_seq.nextval into out_new_booking_id from dual;

  -- Create a new booking
  insert into booking(id, date_made, price, reserved_until)
     values (out_new_booking_id, sysdate, in_price, in_reserved_until);

  for i in 1..in_seats.count loop
    insert into seat_status (Seat_id, Performance_id)
       values(in_seats(i), in_performance_id);
    update seat_status set Booking_id = out_new_booking_id
                where Seat_id = in_seats(i)
                and Performance_id = in_performance_id;
  end loop;
end;
/

[trisberg]

I think you would have to create an Oracle STRUCT for each object in the input list. A straight ARRAY supports string and numeric values but object types defined in the database need to use a STRUCT. Try adding something like the following (check the Oracle docs for exact usage of STRUCT and ARRAY types):

StructDescriptor sdesc = StructDescriptor.createDescriptor ("Contact_OBJ", con);
List structList = new ArrayList();
for (Contact c : list) {
    Object[] attributes = {c.id;
        c.firstName
        c.lastName
        c.email};
    STRUCT s1 = new STRUCT(sdesc, con, attributes);
    structList.add(s1);
}

Then pass in the structList as your parameter value:

proc.declareParameter(new SqlParameter("param1", TYPES.ARRAY,"Contact_OBJ_ARRAY") );
param.put("param1", structList);

I think this might work

[bob]

Thanks a lot.
I will try that.

I think you would have to create an Oracle STRUCT for each object in the input list. A straight ARRAY supports string and numeric values but object types defined in the database need to use a STRUCT. Try adding something like the following (check the Oracle docs for exact usage of STRUCT and ARRAY types):

StructDescriptor sdesc = StructDescriptor.createDescriptor ("Contact_OBJ", con);
List structList = new ArrayList();
for (Contact c : list) {
    Object[] attributes = {c.id;
        c.firstName
        c.lastName
        c.email};
    STRUCT s1 = new STRUCT(sdesc, con, attributes);
    structList.add(s1);
}

Then pass in the structList as your parameter value:

proc.declareParameter(new SqlParameter("param1", TYPES.ARRAY,"Contact_OBJ_ARRAY") );
param.put("param1", structList);

I think this might work

[bob]

Hi
I have created a struct as cited by u. but still its giving the same error.Here is my final class which does all the stuff:

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

import org.apache.commons.dbcp.BasicDataSource;

import oracle.sql.StructDescriptor;
import oracle.sql.STRUCT;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.support.AbstractSqlT ypeValue;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.support.nativejdbc.Common sDbcpNativeJdbcExtractor;

public class ContactTest {
private void execute() throws Exception {
// some DI will be welcome here
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDr iver");
ds.setUrl("jdbc:oracle:thin:@c5899:1521B");
ds.setUsername("uas");
ds.setPassword("pas");
JdbcTemplate template = new JdbcTemplate(ds);
CommonsDbcpNativeJdbcExtractor nje = new CommonsDbcpNativeJdbcExtractor();
template.setNativeJdbcExtractor(nje);

SqlTypeValue param = new AbstractSqlTypeValue() {
protected Object createTypeValue(Connection con, int sqlType,
String typeName) throws SQLException {

StructDescriptor sdesc = StructDescriptor.createDescriptor(
"Contact_OBJ", con);
List structList = new ArrayList();

ArrayList conactObjlist = new ArrayList();
/** ***first OBj** */
Contact c = new Contact();
c.setId(6666);
c.setFirstName("hdx");
c.setLastName("k");
c.setEmail("a@ya.com");
conactObjlist.add(c);
/** **2nd obj** */
Contact c1 = new Contact();
c1.setId(1111);
c1.setFirstName("hari");
c1.setLastName("h");
c1.setEmail("b@ya.com");
conactObjlist.add(c1);

for (int i = 0; i < conactObjlist.size(); i++) {

Contact c2 = (Contact) conactObjlist.get(i);
Object[] attributes = { new Long(c2.getId()),
c2.getFirstName(), c2.getLastName(), c2.getEmail() };
STRUCT p1 = new STRUCT(sdesc, con, attributes);
structList.add(p1);
}
return structList;
}
};
Map params = new HashMap();
params.put("pDemo", param);
SumStoredProcedure sproc = new SumStoredProcedure(template,
"Contact_OBJ_ARRAY_PROC");
sproc.execute(params);
ds.close();
}

public static void main(String[] args) throws Exception {
new ContactTest().execute();
}

private class SumStoredProcedure extends StoredProcedure {

public SumStoredProcedure(JdbcTemplate jdbcTemplate, String name) {

setJdbcTemplate(jdbcTemplate);
setSql(name);
declareParameter(new SqlParameter("pDemo", Types.ARRAY,
"Contact_OBJ_ARRAY"));
compile();
}
}
}

I think you would have to create an Oracle STRUCT for each object in the input list. A straight ARRAY supports string and numeric values but object types defined in the database need to use a STRUCT. Try adding something like the following (check the Oracle docs for exact usage of STRUCT and ARRAY types):

StructDescriptor sdesc = StructDescriptor.createDescriptor ("Contact_OBJ", con);
List structList = new ArrayList();
for (Contact c : list) {
    Object[] attributes = {c.id;
        c.firstName
        c.lastName
        c.email};
    STRUCT s1 = new STRUCT(sdesc, con, attributes);
    structList.add(s1);
}

Then pass in the structList as your parameter value:

proc.declareParameter(new SqlParameter("param1", TYPES.ARRAY,"Contact_OBJ_ARRAY") );
param.put("param1", structList);

I think this might work

[bob]

Pls help....

I think you would have to create an Oracle STRUCT for each object in the input list. A straight ARRAY supports string and numeric values but object types defined in the database need to use a STRUCT. Try adding something like the following (check the Oracle docs for exact usage of STRUCT and ARRAY types):

StructDescriptor sdesc = StructDescriptor.createDescriptor ("Contact_OBJ", con);
List structList = new ArrayList();
for (Contact c : list) {
    Object[] attributes = {c.id;
        c.firstName
        c.lastName
        c.email};
    STRUCT s1 = new STRUCT(sdesc, con, attributes);
    structList.add(s1);
}

Then pass in the structList as your parameter value:

proc.declareParameter(new SqlParameter("param1", TYPES.ARRAY,"Contact_OBJ_ARRAY") );
param.put("param1", structList);

I think this might work

[trisberg]

If you can post a working example of how you would solve this using plain JDBC code, then I can help you translate this into Spring.

Also, if you use CODE tags around your code, then formatting is preserved

[bob]

If you can post a working example of how you would solve this using plain JDBC code, then I can help you translate this into Spring.

Also, if you use CODE tags around your code, then formatting is preserved

Hi
As per our discussions regarding passing arraylist of oblects to stored procedure ,i am posting a example of jdbc code which is passing arraylist
to stored procs .Please help me in converting the code in spring.I am getting the same exception(the one we discussed earlier) while i am trying with spring.
Here is my bean class that implements java.sql.SQLData interface .

import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class EmployeeObj implements SQLData
 {
  private String sql_type="TS.TEST_EMP_OBJ";
  public int empNo;
  public String empName;
  

  public EmployeeObj() 
  { 
  }
                                                            // line 14
public EmployeeObj (String sql_type, int empNo, String empName)
  {
    this.sql_type = sql_type;
    this.empNo = empNo;
    this.empName = empName;
    
   }                                                        // line 20

  ////// implements SQLData //////
 
  // define a get method to return the SQL type of the object  line 24
  public String getSQLTypeName() throws SQLException
  { 
    return sql_type; 
  }                                                         // line 28
 
  // define the required readSQL() method                      line 30
  public void readSQL(SQLInput stream, String typeName)
    throws SQLException
  {
    sql_type = typeName;
    empName = stream.readString();
    empNo = stream.readInt();
  }  
  // define the required writeSQL() method                     line 39
  public void writeSQL(SQLOutput stream)
    throws SQLException
  { 
	  stream.writeInt(empNo);
    stream.writeString(empName);
   
  }
}

Here is the class that calls the stored proc:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Dictionary;

import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestProc {
public static void main(String args[]){
	Connection con=null;
	try{
		 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
	con = DriverManager.getConnection("jdbc:oracle:thin:@c5899:1521:DB", "username", "password");
	String query = "{call TEST_EMP_OBJ_ARRAY_PROC (?)}";
	
	
	CallableStatement cs = con.prepareCall(query);
	ArrayDescriptor des = ArrayDescriptor.createDescriptor("TEST_EMP_OBJ_ARRAY", con);
	EmployeeObj emp=new EmployeeObj("TS.TEST_EMP_OBJ",234,"hello");
	EmployeeObj emp1=new EmployeeObj("TS.TEST_EMP_OBJ",235,"bye");
	Object[] employees= new Object[]{emp,emp1};
	ARRAY a = new ARRAY(des, con, employees);
	cs.setObject(1, (Object)a);
	cs.execute();
	if (cs != null) {
		cs.close();
     }
	}
	catch(SQLException e){
		e.printStackTrace();
	}
	
	
}}
where TS.TEST_EMP_OBJ is the oracle object type for java object employeeobj.
TEST_EMP_OBJ_ARRAY is the array of TEST_EMP_OBJ  in oracle