Hi all,

I have a stored procedure which works correctly.

PROCEDURE CHANGE_CANDIDATE_STATUS
is
begin
FOR REC IN
(SELECT a.artefactid, b.rsdid, round(sysdate - a.cand_status_date) as p_day
FROM candidate_statuses a, artefact_rsd b
where a.artefactid = b.artefactid
and b.cnstrtypeid <> 25 and a.cand_status = 1)
loop

if rec.p_day >= 5 then
insert into candapproval a (a.artefactid, a.rsdid, a.statusid, a.desdate, a.approver, a.id, a.comments)
values (rec.artefactid, rec.rsdid, 4, sysdate, 'system', candapproval_seq.NEXTVAL, null);

update candidate_statuses set cand_status = 4, cand_status_date = sysdate where artefactid = rec.artefactid;
else null;
end if;
end loop;

commit;
EXCEPTION
WHEN OTHERS THEN
WWV_FLOW.debug('[Change Candidate Status] ERROR');
end;


And I'd like to call it from Java code:


import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Date;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.jboss.varia.scheduler.Schedulable;

public class udb_timer implements Schedulable {
private String mName;
private int mValue;
private DataSource ds;
private Connection conn;
private final Logger log = Logger.getLogger(this.getClass());

public udb_timer(String pName, int pValue) {
mName = pName;
mValue = pValue;
}


public void perform(Date pTimeOfCall, long pRemainingRepetitions) {

try {
InitialContext ic = new InitialContext();

try {
ds = (DataSource) ic.lookup("java:/udbrnd");
conn = ds.getConnection();
log.info( "--->" + " connection opened");
}
catch (Exception ex) {
log.error( "connection error--->" + ex.toString());
}

// Main method call
CallableStatement stmt = conn.prepareCall("begin CHANGE_CANDIDATE_STATUS; end;");

stmt.execute();


conn.close();
}
catch (Exception ex) {
log.error( "------->" + ex.toString());
ex.printStackTrace();
}
log.info( "=====> Called at: " + pTimeOfCall + ", name: " + mName + ", value: " + mValue );
}



}


When I deploy it there is an error:

17:34:23,473 INFO [EARDeployer] Started J2EE application: file:/C:/Distr/jboss-4.2.2.GA/server/default/deploy/udb_timer.ear
17:34:24,645 INFO [udb_timer] ---> connection opened
17:34:24,754 ERROR [udb_timer] ------->java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CHANGE_CANDIDATE_STATUS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

17:34:24,801 ERROR [STDERR] java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CHANGE_CANDIDATE_STATUS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
17:34:24,848 ERROR [STDERR] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
17:34:24,848 ERROR [STDERR] at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
17:34:24,864 ERROR [STDERR] at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
17:34:24,879 ERROR [STDERR] at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1894)
17:34:24,879 ERROR [STDERR] at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:1094)
17:34:24,895 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.java:2132)
17:34:24,911 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.java:2015)
17:34:24,911 ERROR [STDERR] at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:2877)
17:34:24,926 ERROR [STDERR] at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePreparedStatement.java:608)
17:34:24,942 ERROR [STDERR] at oracle.jdbc.driver.OraclePreparedStatement.execute (OraclePreparedStatement.java:684)
17:34:24,942 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.execute(WrappedPreparedStatement.java:209)
17:34:24,958 ERROR [STDERR] at kz.kcell.udb_timer.perform(udb_timer.java:57)
17:34:24,973 ERROR [STDERR] at org.jboss.varia.scheduler.Scheduler$Listener.handl eNotification(Scheduler.java:1251)
17:34:24,973 ERROR [STDERR] at sun.reflect.GeneratedMethodAccessor4.invoke(Unknow n Source)
17:34:24,989 ERROR [STDERR] at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
17:34:25,004 ERROR [STDERR] at java.lang.reflect.Method.invoke(Method.java:597)
17:34:25,020 ERROR [STDERR] at org.jboss.mx.notification.NotificationListenerProx y.invoke(NotificationListenerProxy.java:153)
17:34:25,020 ERROR [STDERR] at $Proxy11.handleNotification(Unknown Source)
17:34:25,036 ERROR [STDERR] at javax.management.NotificationBroadcasterSupport.ha ndleNotification(NotificationBroadcasterSupport.ja va:257)
17:34:25,036 ERROR [STDERR] at javax.management.NotificationBroadcasterSupport$Se ndNotifJob****n(NotificationBroadcasterSupport.jav a:322)
17:34:25,051 ERROR [STDERR] at javax.management.NotificationBroadcasterSupport$1. execute(NotificationBroadcasterSupport.java:307)
17:34:25,067 ERROR [STDERR] at javax.management.NotificationBroadcasterSupport.se ndNotification(NotificationBroadcasterSupport.java :229)
17:34:25,083 ERROR [STDERR] at javax.management.timer.Timer.sendNotification(Time r.java:1234)
17:34:25,083 ERROR [STDERR] at javax.management.timer.Timer.notifyAlarmClock(Time r.java:1203)
17:34:25,098 ERROR [STDERR] at javax.management.timer.TimerAlarmClock****n(Timer. java:1286)
17:34:25,114 ERROR [STDERR] at java.util.TimerThread.mainLoop(Timer.java:512)
17:34:25,129 ERROR [STDERR] at java.util.TimerThread****n(Timer.java:462)
17:34:25,129 INFO [udb_timer] =====> Called at: Wed Aug 05 17:34:24 GMT+06:00 2009, name: Dana, value: 12345


Could you please help me to solve this problem.

Thank you in advance.

With regards,
Dayana