Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

problem with SYSDATE as entry in stored proc

user515689May 4 2007 — edited May 4 2007

Hello,

I've altered my stored procedure I've had running properly for the last few months.
I added a new DATE field to my database table, and the stored procedure said it created with no errors.

Here's the code for it:

CREATE OR REPLACE PROCEDURE updadmincoordproctwo
(procadmname   IN CHANGE_CONTROL_ADMIN.ADMNAME%TYPE,
procdate   IN CHANGE_CONTROL_ADMIN.CLOSEDATE%TYPE, 
procact   IN CHANGE_CONTROL_ADMIN.ACTIVE%TYPE, 
procstat1   IN CHANGE_CONTROL_ADMIN.STATUS1%TYPE, 
procstat2   IN CHANGE_CONTROL_ADMIN.STATUS2%TYPE, 
procadmcomm   IN CHANGE_CONTROL_ADMIN.ADM_NOAPROVE%TYPE,
procfilename   IN CHANGE_CONTROL_ADMIN.FILENAME%TYPE,
proctimestamp IN CHANGE_CONTROL_ADMIN.VPTIMSTAMP%TYPE,
cctrlid IN NUMBER) 
IS BEGIN 
-- UPDATE STATEMENT 
UPDATE CHANGE_CONTROL_ADMIN SET ADMNAME = procadmname, CLOSEDATE = procdate, ACTIVE = procact, STATUS1 = procstat1, STATUS2 = procstat2, ADM_NOAPROVE = procadmcomm,
FILENAME = procfilename, VPTIMSTAMP = proctimestamp
WHERE CHANGE_CTRL_ID = cctrlid; 
COMMIT;
END updadmincoordproctwo; 
/

In my Java servlet I have the following:

CallableStatement cstmt = connection.prepareCall("{call updadmincoordproctwo (?,?,?,?,?,?,?,?,?)}");
   cstmt.setString(1,admname);
   cstmt.setDate(2,cldt);
   cstmt.setInt(3,actbox);
   cstmt.setString(4,status1);
   cstmt.setString(5,status2);
   cstmt.setString(6,no_appr_comnt);
   cstmt.setString(7,templatefile);
   cstmt.setInt(8,ctid2);
   cstmt.setDate(9,sysdateret);
   cstmt.executeUpdate();

my other values, including a request parameter to retrieve a date or doing just fine; further, a print to the console shows 2007-05-04 for the SYSDATE being returned and my other Date object shows 2007-05-11 and enters fine.
However, I get an error showing the following:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UPDADMINCOORDPROCTWO'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UPDADMINCOORDPROCTWO'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Any idea why?

Any feedback is appreciated!

Thanks!

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 1 2007
Added on May 4 2007
4 comments
909 views