| ORA-01861: literal does not match format string [message #116643] |
Thu, 21 April 2005 02:13  |
vinu_vkumar
Messages: 45
Registered: March 2005
|
Member |
|
|
Hi Experts,
I am trying to insert into a table which has a date type field.
my insert statement is like
insert into table values(sysdate);
Other than the date field i have some more fields also including varchar and number types.
This seems to be working fine in Indian time zone but not in Canada. Is it having that kind of connection.
The error is
java.sql.SQLException: ORA-01861: literal does not match format string
And does this happen with date types only or any other data type also.
Searched the net for the same but could not find a proper solution.
Kind of urgent
Thanks in advance.
Vinu.
|
|
|
|
|
|
| Re: ORA-01861: literal does not match format string [message #116786 is a reply to message #116678] |
Fri, 22 April 2005 01:13  |
vinu_vkumar
Messages: 45
Registered: March 2005
|
Member |
|
|
Dear Maaher,
This is exactly my dilema, the date is not coming from any client program but the client program just calls this procedure in oracle which has some varchar parameters(can this exception occur for these types also?) and to the DATE type field SYSDATE is inserted.
TABLE
CREATE TABLE GLB_TT_ACTIVITYLOG
(
NETWORK_ID NUMBER(6),
LOGIN_ID VARCHAR2(20),
ACTIVITY_ID NUMBER(6),
TRANS_ID NUMBER(10),
TRANS_DATE DATE,
SESSION_ID NUMBER(10),
SESSION_OF_DAY NUMBER(3),
INPUT_PARAMS VARCHAR2(500)
)
The input variables to the procedure are
1)pi_network_id IN GLB_TT_ACTIVITYLOG.NETWORK_ID%TYPE,
2)PI_LOGINID IN GLB_TT_ACTIVITYLOG.LOGIN_ID%TYPE,
3)pi_sessionid IN GLB_TT_ACTIVITYLOG.SESSION_ID%TYPE,
4)pi_activityid IN GLB_TT_ACTIVITYLOG.ACTIVITY_ID%TYPE,
5)pi_inputparams IN GLB_TT_ACTIVITYLOG.INPUT_PARAMS%TYPE,
The two local variables are
nb_transno NUMBER;
nb_sessionofday NUMBER;
The insert script in the procedure is
INSERT INTO GLB_TT_ACTIVITYLOG
(network_id, login_id, session_id, session_of_day, activity_id, trans_id, trans_date, input_params)
VALUES (pi_network_id, pi_loginid, pi_sessionid, nb_sessionofday, pi_activityid, nb_transno,
SYSDATE,pi_inputparams);
the other parameters other than the sysdate is coming from the java client program.
Another thing i have checked with my implementation engineeer in canada is that when u give the statement "select sysdate from dual" the format shown is 2005-Apr-21, where as here it shows
21-Apr-2005 for me here.
Just to re iterate, it is not giving any error in our development or testing or our implementation team locally, but for this different country. Dont know if I am leading to a non existant difference by pointing out this.
Hope this will help u in identifying something.
Expecting a reply soon...
Regards,
Vinu.
|
|
|
|
|
|
| Re: ORA-01861: literal does not match format string [message #116794 is a reply to message #116793] |
Fri, 22 April 2005 02:26 |
vinu_vkumar
Messages: 45
Registered: March 2005
|
Member |
|
|
But Barbara,
does that mean that this format will have a problem when an insert like the one in my code is given in different places where the format is set differently....
I just wanted to know why an insert script like the one given in the previous mail is not working at some other country(or different format) and is working here for me.
Regards,
Vinu
|
|
|
|
| Re: ORA-01861: literal does not match format string [message #116868 is a reply to message #116794] |
Fri, 22 April 2005 10:56 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The insert statement that you posted would not produce such an error when inserted into a table with the structure that you have posted. However, you have obviously not posted all of the code, so it is extremely difficult to guess at what the problem might be. The error that you have posted indicates that the error is coming through java, but you have not posted any java. It could be that your java code is inappropriately relying on an impicit conversion of the date format, which is dependent upon the nls_date_format, instead of doing an explicit conversion. Or, it could be that the problem has nothing to do with the date. You need to test pieces of your code until you determine where the error is occurring. If you run your sql code from SQL*Plus, then you will either get an error message with a line number and an asterisk that points to the place in that line where the problem occurs or you will get no error, which will tell you that the problem is in the java code and then you will have to determine where in your java code. I would tend to suspect that the java code is relying on an implicit conversion.
|
|
|
|
| Re: ORA-01861: literal does not match format string [message #117248 is a reply to message #116643] |
Tue, 26 April 2005 00:33 |
vinu_vkumar
Messages: 45
Registered: March 2005
|
Member |
|
|
Dear Barbara,
Your queries on nls_date_format had the answer to the problem I was facing. The client and server machines had different formats for this variable and hence the error. Now this variable is set to match each other in the DD-MON-RR format that was deviced to capture any year during the Y2K problem.
The records are being inserted properly now. Thanks again for sharing the expertise on the same.
Thanks to Maaher too.
Regards,
Vinu.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
