I have the following set of TYPE, PROCEDURE and Java code. I am able to call the stored procedure successfully but i have to append the objects one after the other. I want the process to be happening at one shot as i am dealing with over 50K+ records. Can anyone please let me know what changes needs to be done so that i can send the entire list at one. The code can be found below.
TYPES:
CREATE OR REPLACE TYPE CER_GL_ENTRY_TYPE AS OBJECT
(idKey NUMBER(10) );
CREATE or REPLACE TYPE CER_GL_ENTRY_TYPE_LIST AS TABLE OF CER_GL_ENTRY_TYPE;
PROCEDURE:
CREATE OR REPLACE PROCEDURE GL_PROCESS_BULK_ENTRIES (
p_array IN CER_GL_ENTRY_TYPE_LIST ,p_status OUT VARCHAR2)
AS
v_count NUMBER(5);
row_detail CER_GL_ENTRY_TYPE;
BEGIN
--p_arr_int := NEW array_int ();
--p_arr_int.EXTEND (10);
--len := p_array.COUNT;
v_count := 0;
FOR i IN 1 .. p_array.COUNT
LOOP
row_detail := p_array (i);
DBMS_OUTPUT.put_line('hello');
DBMS_OUTPUT.put_line (row_detail.idKey);
--p_arr_int (i) := v_count;
v_count := v_count + 1;
p_status := 'true';
END LOOP;
DBMS_OUTPUT.put_line (v_count);
DBMS_OUTPUT.put_line (p_status);
EXCEPTION
WHEN OTHERS
THEN
-- handle errors here...
DBMS_OUTPUT.put_line ('Error: ' || SUBSTR (1, 255, SQLERRM));
END;
/
Java Bean:
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
public class SampleListenerBean implements SQLData
{
private String sql_type="CER_GL_ENTRY_TYPE";
private int id;
public SampleListenerBean() {
}
public SampleListenerBean(String sqlType, int id) {
this.sql_type = sqlType;
this.id = id;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSQLTypeName() throws SQLException {
return sql_type;
}
public void readSQL(SQLInput stream, String typeName) throws SQLException {
sql_type = typeName;
id = stream.readInt();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeInt(id);
}
}
Main class:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class StProcExample {
public static void main(String args[]){
Connection con=null;
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
con = DriverManager.getConnection("jdbc:oracle:thin:@******:1521: TUW1", "*******", "*********");
String query = "{call GL_PROCESS_BULK_ENTRIES(?,?)}";
CallableStatement cs = con.prepareCall(query);
ArrayDescriptor des = ArrayDescriptor.createDescriptor("CER_GL_ENTRY_TYPE_LIST", con);
List<SampleListenerBean> sampleLst = new ArrayList<SampleListenerBean>();
SampleListenerBean bean = null;
for (int i = 0; i < 20; i++) {
bean = new SampleListenerBean("CER_GL_ENTRY_TYPE",i);
sampleLst.add(bean);
}
SampleListenerBean emp=new SampleListenerBean("TS.TEST_EMP_OBJ",234);
SampleListenerBean emp1=new SampleListenerBean("TS.TEST_EMP_OBJ",235);
Object[] employees= new Object[]{emp,emp1};
Object[] employees= new Object[]{sampleLst};
ARRAY a = new ARRAY(des, con, employees);
cs.setObject(1, (Object)a);
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String status = cs.getString(2);
System.out.print("The status is " + status);
if (cs != null) {
cs.close();
}
}
catch(SQLException e){
e.printStackTrace();
}
}
}
I want is the replacement for the following piece of code
SampleListenerBean emp=new SampleListenerBean("TS.TEST_EMP_OBJ",234);
SampleListenerBean emp1=new SampleListenerBean("TS.TEST_EMP_OBJ",235);
Object[] employees= new Object[]{emp,emp1};
ARRAY a = new ARRAY(des, con, employees);
cs.setObject(1, (Object)a);
Instead of setting each object separately, i want to directly use sampleLst instead of the Object array "employees". When i deal with 50K+ objects i cannot keep adding them to the object[]. i will run into heap problems as well. Can anyone please help me out here?
I would use a simpler table type:
CREATE OR REPLACE TYPE NUM_ARRAY AS TABLE OF NUMBER;
And then somewhat simplify the stored procedure:
CREATE OR REPLACE PROCEDURE GL_PROCESS_BULK_ENTRIES (
p_array IN NUM_ARRAY,
p_status OUT VARCHAR2)
IS
...
You then create an array of Integer
that will easily fit in memory:
Integer[] idArray = new Integer[50000];
// fill the array of integers here
for (int i = 0; i < idArray.length; i++)
idArray[i] = ....;
ARRAY a = new ARRAY(des, con, idArray);
cs.setObject(1, (Object)a);
There's no need to create any heavy-weight beans just to pass a list of IDs.