Thread: [JAVA CODE] stored proc to insert and then delete the data from another table /CODE

create or replace
PACKAGE BODY KEY_GPL_LCP_ARCHIVE AS

procedure LCP_ARCHIVE_PROC(CHECKID_VAR in number, STATUS_VAR out VARCHAR2) AS
STATUSID_VAR number;
ACTIONFID_VAR number;
PAYMNTFID_VAR number;
CORACTACCTFID_VAR number;
ACCTNAME_VAR varchar2(100);
ACCTNO_VAR varchar2(50);
AMOUNT_VAR number;
FEEAMT_VAR number;
PAYEE_VAR varchar2(32);
ADDRESS1_VAR varchar2(32);
ADDRESS2_VAR varchar2(32);
ADDRESS3_VAR varchar2(32);
CITY_VAR varchar2(50);
STATE_VAR varchar2(20);
ZIP_VAR varchar2(11);
DEPOSITDT_VAR timestamp(6);
CLOSEDDT_VAR timestamp(6);
COMMENTS_VAR varchar2(500);
ADDEDBY_VAR varchar2(50);
ADDEDDT_VAR timestamp(6);
PROCESS_VAR varchar2(3);
CONTROLNO_VAR varchar2(50);
MESSAGE_VAR varchar2(100);
NUMSHARES_VAR number;
ADMIN_VAR varchar2(50);
NEWACCT_VAR varchar2(50);
BRO_VAR varchar2(50);
FEES_VAR varchar2(1);
PMTPROC_VAR varchar2(15);
ARCHIVE_VAR varchar2(3);
ESCHSTATE_VAR varchar2(3);
ESCHMONTHS_VAR number;
SUCCACCNO_VAR varchar2(50);


begin
is_rec_inserted := FALSE;
STATUS_VAR :='OK';

select STATUSID
,ACTIONFID
,PAYMNTFID
,CORACTACCTFID
,ACCTNAME
,ACCTNO
,AMOUNT
,FEEAMT
,PAYEE
,ADDRESS1
,ADDRESS2
,ADDRESS3
,CITY
,STATE
,ZIP
,DEPOSITDT
,CLOSEDDT
,COMMENTS
,ADDEDBY
,ADDEDDT
,PROCESS
,CONTROLNO
,MESSAGE
,NUMSHARES
,ADMIN
,NEWACCT
,BRO
,FEES
,PMTPROC
,ARCHIVE
,ESCHSTATE
,ESCHMONTHS
,SUCCACCNO
into STATUSID_VAR
,ACTIONFID_VAR
,PAYMNTFID_VAR
,CORACTACCTFID_VAR
,ACCTNAME_VAR
,ACCTNO_VAR
,AMOUNT_VAR
,FEEAMT_VAR
,PAYEE_VAR
,ADDRESS1_VAR
,ADDRESS2_VAR
,ADDRESS3_VAR
,CITY_VAR
,STATE_VAR
,ZIP_VAR
,DEPOSITDT_VAR
,CLOSEDDT_VAR
,COMMENTS_VAR
,ADDEDBY_VAR
,ADDEDDT_VAR
,PROCESS_VAR
,CONTROLNO_VAR
,MESSAGE_VAR
,NUMSHARES_VAR
,ADMIN_VAR
,NEWACCT_VAR
,BRO_VAR
,FEES_VAR
,PMTPROC_VAR
,ARCHIVE_VAR
,ESCHSTATE_VAR
,ESCHMONTHS_VAR
,SUCCACCNO_VAR
from lc_checks
where CHECKID = CHECKID_VAR;


insert into lc_checks_arc values (CHECKID_VAR
,STATUSID_VAR
,ACTIONFID_VAR
,PAYMNTFID_VAR
,CORACTACCTFID_VAR
,ACCTNAME_VAR
,ACCTNO_VAR
,AMOUNT_VAR
,FEEAMT_VAR
,PAYEE_VAR
,ADDRESS1_VAR
,ADDRESS2_VAR
,ADDRESS3_VAR
,CITY_VAR
,STATE_VAR
,ZIP_VAR
,DEPOSITDT_VAR
,CLOSEDDT_VAR
,COMMENTS_VAR
,ADDEDBY_VAR
,ADDEDDT_VAR
,PROCESS_VAR
,CONTROLNO_VAR
,MESSAGE_VAR
,NUMSHARES_VAR
,ADMIN_VAR
,NEWACCT_VAR
,BRO_VAR
,FEES_VAR
,PMTPROC_VAR
,ARCHIVE_VAR
,ESCHSTATE_VAR
,ESCHMONTHS_VAR
,SUCCACCNO_VAR);

is_rec_inserted := TRUE;

delete from lc_checks where CHECKID = CHECKID_VAR;

exception
when OTHERS then
begin
if is_rec_inserted then
STATUS_VAR :='t';
else
STATUS_VAR :='f';
DBMS_OUTPUT.PUT_LINE('Caught raised exception NO_DATA_FOUND');
end if;
is_rec_inserted := FALSE;
end

END LCP_ARCHIVE_PROC;
END KEY_GPL_LCP_ARCHIVE;

------
Error(165,1): PLS-00103: Encountered the symbol "END" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier> The symbol ";" was substituted for "END" to continue.

----

public int updateArchive( String checkIdVal) throws ApplicationException {

System.out.println("the updateArchive method begins ************* ");
System.out.println("the value of checkid in the archive check is"+checkIdVal);


// GPLUserDataSource dataSource = null;
// Connection connection = null;
CallableStatement callableStatement = null;
String status = null;
int updatecount = 0;
try{
// TODO remove sysout and use logger to log these message.
// System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$ Scheduled History batch process started @ " + new Date());
dataSource = new GPLUserDataSource("gpl", "oracle");
conn = dataSource.getConnection();
// long batchId = new Date().getTime();
String query = "CALL " + GPLUtil.getDBOwner(dataSource.getApp(), dataSource.getDsname())
+ ".key_gpl_lcp_archive.lcp_archive_proc(?, ?)";
callableStatement = conn.prepareCall(query);
callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.setString(1, "" + checkIdVal);
System.out.println("the callable statement query is "+query);
System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$ Callig SQL checkIdVal : [" + checkIdVal +"]");
callableStatement.executeQuery();
status = callableStatement.getString(2);
System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$ SQL batch [" + checkIdVal + "] finished with status : " + status);
System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$ Finished Scheduled java batch : " + status);
updatecount++;

} catch (SQLException exception) {
//result = 0;
exception.printStackTrace();
throw new ApplicationException(exception.getLocalizedMessage (),exception);
} catch (ConnectionException exception) {
// result=0;
exception.printStackTrace();
throw new ApplicationException(exception.getLocalizedMessage (),exception);
} catch (NamingException exception) {
// result = 0;
exception.printStackTrace();
throw new ApplicationException(exception.getLocalizedMessage (),exception);
}finally{
try {
conn.close();
stmt.close();
} catch (SQLException e) {
// result = 0;
e.printStackTrace();
}
}

return updatecount;
}

Ahem,... do you have a question?

Hi
it is error from procedure .
After is_rec_inserted := FALSE;
there is a end statement replace it as end;(i.e., put ; after end statement)
it removes this error