Meaningless Drivel is fun!
The moose likes JDBC and Relational Databases and the fly likes SQLException: ORA-01861: literal does not match format string Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQLException: ORA-01861: literal does not match format string" Watch "SQLException: ORA-01861: literal does not match format string" New topic

SQLException: ORA-01861: literal does not match format string

Robert Wyns

Joined: Aug 08, 2003
Posts: 1
I have a little problem in my actual project :
I have this "literal does not match format string" exception coming again and again
Could someone say me exactly why I get it ? (and eventually how to solved it ?)
Here is the java method :
public int Save(int conId, String access, String idName, String comment, String group, String conState, String modifDate, String title, String nbPage, String launchDate, String delai, String noAgenda, boolean isAnnexe, String version, String procType, String Scuid) throws Exception
int vConId = -1;
System.out.println(access + " " + Scuid);
CallableStatement myCall = connect.prepareCall("{CALL CIS_VC_PACKAGE.P_VC_ADD(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
myCall.registerOutParameter(1, Types.INTEGER);
myCall.setString(2, new Integer(conId).toString());
myCall.setString(3, access);
myCall.setString(4, idName);
myCall.setString(5, comment);
myCall.setString(6, group);
myCall.setString(7, consultState);
myCall.setString(8, modifDate);
myCall.setString(9, title);
myCall.setString(10, nbPage);
myCall.setString(11, launchDate);
myCall.setString(12, delai);
myCall.setString(13, noAgenda);
myCall.setBoolean(14, isAnnexe);
myCall.setString(15, version);
myCall.setString(16, procType);
if (!myCall.execute()) {
// The store procedure return the id of the created consultation
vConId = myCall.getInt(1);
and here is the stored procedure it uses :

procedure P_VC_ADD(
a_vc_id out cis_version_con.vc_id%type, -- Version of Consultation identifier
a_con_id in integer, -- Consultation identifier-- Consultation identifier
a_tc_skey in varchar,
a_vc_idname in varchar,
a_vc_comment in varchar,
a_grp_id in varchar,
a_cs_skey in varchar,
a_vc_timestamp in varchar,
a_vc_title in varchar,
a_vc_nbpage in varchar,
a_vc_launchdate in varchar,
a_vc_delay in varchar,
a_vc_agenda in varchar,
a_vc_annexe in varchar,
a_vc_version in varchar,
a_pt_id in varchar
) is
select SEQUENCE_VC.NEXTVAL into a_vc_id from dual;
-- sd_id not inserted by default... must be inserted if tc_skey <> Base
insert into cis_version_con ( vc_id, con_id, tc_id, vc_idname, vc_comment, cs_id, grp_id, vc_timestamp,
vc_title, vc_nbpage, vc_launchdate, vc_delay, vc_agenda, vc_annexe, vc_version, pt_id)
values (
(select a.tc_id from cis_type_con a where a.TC_SKEY = a_tc_skey),
(select c.GRP_ID from cis_user a, sga_person b, cis_group c where a.PER_ID = b.PER_ID and c.GRP_ID = a.GRP_ID and b.PER_LOGIN = a_grp_id),
(select a.cs_id from cis_constate a where a.cs_skey = a_cs_skey),
-- to_date(a_vc_timestamp),
(select current_date from dual),
end P_VC_ADD;

Thanks for reading me
Lasse Koskela

Joined: Jan 23, 2002
Posts: 11962
The cause is one of your to_date(...) or to_number(...) functions. Try to figure out the exact strings you're putting inside the parenthesis and compare them to the expected format string. Btw, I like to use the more explicit TO_DATE('08-08-2003', 'mm-dd-yyyy') where I can clearly see the expected format string...

Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
I agree. Here's the link:
subject: SQLException: ORA-01861: literal does not match format string
It's not a secret anymore!