I have created a procedure named "insert_financial_year" in oracle 10g to insert data into table "financial year".I want to insert data into table "financial year" from java.I have created a class "DatabaseConnection" that connects to database.I have also created object of "DatabaseConnection".I also have created another class called "ptechdatabean" that contains variables and methods.Some of this variables and methods are common to other classes and they are used there.Further I have created another class called "Financial_year_procedure" that calls the procedure "insert_financial_year" to insert data into my table "financial year" and also uses some(not all) methods and variables of class "ptechdatabean".There are a total of 13 columns in my table "financial year" out of which there is a single primary key.While creating my procedure "insert_financial_year" I have specified all 13 column names along with a flag("if it is I-INSERTION TAKES PLACE,if D-deletion takes place") and 2 variables namely "errbuff & errcode(OUT parameters)" to display messages.Connection has been created but i am not able to insert data into it.It is giving errors.I will send the code of classes and procedure.kindly guide me.
********************************************************************************
pocedure to insert data into table.
CREATE OR REPLACE PROCEDURE PR_INSERT_FINANCIAL_YEAR(P_COMPANY_ID IN NUMBER,
P_BRANCH_ID IN NUMBER,
P_F_YEAR IN NUMBER,
P_FROM_DATE IN DATE,
P_TO_DATE IN DATE,
P_STATUS IN CHAR,
P_ENTERED_BY NUMBER,
P_ENTERED_DATE IN DATE,
p_Updated_By NUMBER,
p_Update_Date In Date,
p_Ip_Address In Varchar2,
p_Mac_Address In Varchar2,
P_Year_close in char,
p_Flag In Char/*,
p_Errbuff Out Varchar2,
p_Errcode Out Varchar2*/)
IS
BEGIN
IF p_Flag = 'I' THEN
INSERT INTO FINANCIAL_YEAR
(COMPANY_ID,
BRANCH_ID,
F_YEAR,
FROM_DATE,
TO_DATE,
STATUS,
entered_by,
ENTERED_DATE,
Updated_By,
Update_Date,
Ip_Address,
Mac_Address,
year_close)
VALUES
(P_COMPANY_ID,
P_BRANCH_ID,
P_F_YEAR,
P_FROM_DATE,
P_TO_DATE,
P_STATUS,
P_ENTERED_BY,
P_ENTERED_DATE,
p_Updated_By,
p_Update_Date,
p_Ip_Address,
p_Mac_Address,
p_year_close);
p_Errbuff := 'Transaction Saved succssfully..';
p_Errcode := '001';
END IF;
commit;
END;
************************************************************************************************
//class using procedure to insert data into table.This lass is giving errors
package Ptech;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
public class Financial_Year_Procedure
{
public void Call_Financial_Year_Procedure(PtechDataBean pdb) throws ClassNotFoundException, SQLException
{
DatabaseConnection data_conn = new DatabaseConnection();
Connection Oracle_Connect = data_conn.Oracle_Connect();
CallableStatement cs = Oracle_Connect.prepareCall("{call PR_INSERT_FINANCIAL_YEAR(?,?,?,?,?,?,?,?,?,?,?,?,?,?}");
cs.setInt(1, pdb.getCompany_id());
cs.setInt(2, pdb.getBranch_id());
cs.setInt(3, pdb.getf_year());
cs.setString(4, pdb.getFrom_Date());
cs.setString(5, pdb.getTo_Date());
cs.setString(6, pdb.getStatus());
cs.setInt(7, pdb.getEntered_By());
cs.setString(8, pdb.getEntered_Date());
cs.setInt(9, pdb.getUpdated_By());
cs.setString(10, pdb.getUpdate_Date());
cs.setString(11, pdb.getIp_Address());
cs.setString(12, pdb.getMac_Address());
cs.setString(13, pdb.getYear_close());
cs.setString(14, pdb.getFlag());
cs.registerOutParameter(15, Types.VARCHAR);
cs.registerOutParameter(16, Types.VARCHAR);
cs.executeUpdate();
ArrayList al = new ArrayList();
{
al.add(cs.getString(15));
al.add(cs.getString(16));
}
return al;
}
public static void main(String args[]) throws ClassNotFoundException, SQLException {
Financial_Year_Procedure fy = new Financial_Year_Procedure();
PtechDataBean pdb = new PtechDataBean();
pdb.setCompany_id(13);
pdb.setBranch_id(14);
pdb.setf_year(2011);
pdb.setFrom_Date(" ");
pdb.setTO_Date(" ");
pdb.setStatus("E");
pdb.setEntered_By(1);
pdb.setEntered_Date(" ");
pdb.setUpdated_By(1);
pdb.setUpdate_Date(" ");
pdb.setIp_Address(" ");
pdb.setMac_Address(" ");
pdb.setYear_close("N");
pdb.setFlag("I");
pdb.setErrbuff(" ");
pdb.setErrcode(" ");
ArrayList al = fy.Call_Financial_Year_Procedure(pdb);
fy.Call_Financial_Year_Procedure(pdb);
System.out.println(al);
}
}
**************************************************************************