I have gone through many posts on this exception,but did not get any clear idea.
I have developed application which fetches data from excelsheet and insert it into database.
To achieve this, I have used POI Apache concept. I have already fetched data from excelsheet (HSSFCell) object to Object[]. I have developed class which implements SQLdata and Serializable.
public class EmployeeObj implements SQLData,Serializable {
public long EMP_ID;
public String FIRST_NM;
public String MIDDLE_NM;
public String DOJ;
public String GENDER;
public String STATUS;
public long REPORTING_MGR;
public String LAST_NM;
public String EMAIL_ID;
public String STREAM_ID;
public String DESIGN_ID;
public String EMP_LOCATION;
public String GEO_CODE;
public String EMP_NO;
public long LEVEL_2_MGR;
public String TENTATIVE_LAST_DAY;
private String sql_type="EMP_DATA_OBJ";
static int count=0;
public EmployeeObj() {
super();
}
public EmployeeObj(String sql_type,Object[] empObj)
{
this.sql_type = sql_type;
count=count+1;
this.EMP_ID=Long.parseLong(empObj[0].toString());
this.FIRST_NM=empObj[1].toString();
this.MIDDLE_NM=empObj[2].toString();
this.DOJ=empObj[3].toString();
this.GENDER=empObj[4].toString();
this.STATUS=empObj[5].toString();
this.REPORTING_MGR=Long.parseLong(empObj[6].toString());
this.LAST_NM=empObj[7].toString();
this.EMAIL_ID=empObj[8].toString();
this.STREAM_ID=empObj[9].toString();
this.DESIGN_ID=empObj[10].toString();
this.EMP_LOCATION=empObj[11].toString();
this.GEO_CODE=empObj[12].toString();
this.EMP_NO=empObj[13].toString();
this.LEVEL_2_MGR=Long.parseLong(empObj[14].toString());
this.TENTATIVE_LAST_DAY=empObj[15].toString();
}
@Override
public String getSQLTypeName() throws SQLException {
// TODO Implement this method
return sql_type;
}
@Override
public void readSQL(SQLInput stream, String typeName) throws SQLException {
// TODO Implement this method
sql_type=typeName;
this.EMP_ID=stream.readLong();
this.FIRST_NM=stream.readString();
this.MIDDLE_NM=stream.readString();
this.DOJ=stream.readString();
this.GENDER=stream.readString();
this.STATUS=stream.readString();
this.REPORTING_MGR=stream.readLong();
this.LAST_NM=stream.readString();
this.EMAIL_ID=stream.readString();
this.STREAM_ID=stream.readString();
this.DESIGN_ID=stream.readString();
this.EMP_LOCATION=stream.readString();
this.GEO_CODE=stream.readString();
this.EMP_NO=stream.readString();
this.LEVEL_2_MGR=stream.readLong();
this.TENTATIVE_LAST_DAY=stream.readString();
}
@Override
public void writeSQL(SQLOutput stream) throws SQLException {
// TODO Implement this method
stream.writeLong(EMP_ID);
stream.writeString(FIRST_NM);
stream.writeString(MIDDLE_NM);
stream.writeString(DOJ);
stream.writeString(GENDER);
stream.writeString(STATUS);
stream.writeString(LAST_NM);
stream.writeString(EMAIL_ID);
stream.writeString(STREAM_ID);
stream.writeString(DESIGN_ID);
stream.writeString(EMP_LOCATION);
stream.writeString(GEO_CODE);
stream.writeString(EMP_NO);
stream.writeString(TENTATIVE_LAST_DAY);
stream.writeLong(REPORTING_MGR);
stream.writeLong(LEVEL_2_MGR);
}
}
I have created a callable statement object to call the pl/sql procedure from java.
CallableStatement pstmt =
getDBTransaction().createCallableStatement("begin ?:= employee_dml.Main(?,EMP_OBJ_DT_ARR(?),?) end;",
0);
EmployeeObj eob=null;
Object[] obj = EmployeeD.toArray(new Object[EmployeeD.size()]);
for(int i=0;i<EmployeeD.size();i++)
{
obj[i]=EmployeeD.get(i).toString();
}
eob=new EmployeeObj("EMP_DATA_OBJ",obj);
pstmt.setInt(1,row_id);
pstmt.setObject(2, (Object) eob);
pstmt.setString(3,user);
pstmt.registerOutParameter(1, Types.BIGINT);
try
{
pstmt.executeUpdate();
}
catch(SQLException e1)
{
e1.printStackTrace();
}
dbTransaction.commit();
The pl/sql procedure is :
create or replace package body employee_dml is
cursor C_EMP_EXISTS(L_EmpId LPM_EMPLOYEE_DATA.emp_id%type) is
select count(1)
from LPM_EMPLOYEE_DATA
where emp_id=L_EmpId;
cursor C_STREAM_EXISTS(L_StreamDesc LPM_STREAM.STREAM_DESC%type) is
select STREAM_CODE
from LPM_STREAM
where UPPER(STREAM_DESC)=trim(UPPER(L_StreamDesc));
cursor C_DESG_EXISTS(L_DesgDesc LPM_DESIGNATION.DESIGNATION_DESC%type) is
select DESIGNATION_CODE
from LPM_DESIGNATION
where UPPER(DESIGNATION_DESC)=trim(UPPER(L_DesgDesc));
L_stream varchar2(255);
L_desg varchar2(255);
FUNCTION MAIN(row_id IN number,
p_obj_array IN EMP_OBJ_DT_ARR,
L_user IN varchar2)
RETURN NUMBER IS
L_error_message varchar2(255);
L_error boolean := false;
L_count number := 0;
BEGIN
insert into ins_msg values('In Main new p_obj_array.count ');
commit;
for i in 1..p_obj_array.count loop
open C_EMP_EXISTS(p_obj_array(i).emp_id);
fetch C_EMP_EXISTS into L_count;
close C_EMP_EXISTS;
open C_STREAM_EXISTS(p_obj_array(i).STREAM_ID);
fetch C_STREAM_EXISTS into L_stream;
close C_STREAM_EXISTS;
open C_DESG_EXISTS(p_obj_array(i).DESIGN_ID);
fetch C_DESG_EXISTS into L_desg;
close C_DESG_EXISTS;
if L_count = 0 then
if INSERT_EMPDATA(p_obj_array,
L_error_message) = FALSE then
return 1;
end if;
return 0;
end if;
END LOOP;
EXCEPTION
when OTHERS then
L_error_message := SQL_LIB.CREATE_MSG('PACKAGE_ERROR',
SQLERRM,
null,
TO_CHAR(SQLCODE));
return 0;
END MAIN;
FUNCTION INSERT_EMPDATA(p_obj_array in EMP_OBJ_DT_ARR,
O_error_message OUT varchar2)
RETURN BOOLEAN IS
BEGIN
for i in 1..p_obj_array.count loop
insert into lpm_employee_data
(
EMP_ID,
FIRST_NM,
MIDDLE_NM,
DOJ,
GENDER,
STATUS,
REPORTING_MGR,
LAST_NM,
EMAIL_ID,
STREAM_ID,
DESIGN_ID,
EMP_LOCATION,
GEO_CODE,
EMP_NO,
LEVEL_2_MGR,
TENTATIVE_LAST_DAY,
CREATE_ID,
CREATE_DATE,
UPDATE_ID,
UPDATE_DATE)
values (p_obj_array(i).EMP_ID,
p_obj_array(i).FIRST_NM,
p_obj_array(i).MIDDLE_NM,
TO_CHAR(TO_DATE(p_obj_array(i).doj,'DD-MM-YYYY')),
p_obj_array(i).GENDER,
p_obj_array(i).STATUS,
p_obj_array(i).REPORTING_MGR,
p_obj_array(i).LAST_NM,
p_obj_array(i).EMAIL_ID,
L_stream,
L_desg,
p_obj_array(i).EMP_LOCATION,
p_obj_array(i).GEO_CODE,
p_obj_array(i).EMP_NO,
p_obj_array(i).LEVEL_2_MGR,
p_obj_array(i).TENTATIVE_LAST_DAY,
USER,
SYSDATE,
USER,
SYSDATE);
end loop;
return TRUE;
EXCEPTION
when OTHERS then
O_error_message := SQL_LIB.CREATE_MSG('PACKAGE_ERROR',
SQLERRM,
null,
TO_CHAR(SQLCODE));
return FALSE;
END INSERT_EMPDATA;
END employee_dml;
After running the code, I will get
java.sql.SQLException: Missing IN or OUT parameter at index:: 4
I have only pass 3 parameters and 1 result parameter. So I am not able to understand why It's asking for parameter at index 4. Please suggest me on this.
The output parameter of your function is at index 1. The input parameters start from index 2 until 4.
eob=new EmployeeObj("EMP_DATA_OBJ",obj);
pstmt.registerOutParameter(1, Types.BIGINT);
pstmt.setInt(2,row_id);
pstmt.setObject(3, (Object) eob)
pstmt.setString(4,user);