This discussion is archived
12 Replies Latest reply: Dec 6, 2012 4:12 AM by EdStevens RSS

SQL Error: ORA-01861: literal does not match format string

978268 Newbie
Currently Being Moderated
Hello,

I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,


create or replace function ssndate(p_date in varchar2 default '03-01-18',
p_time in varchar2)
return number
$if dbms_db_version.ver_le_10 $then
deterministic
$elsif dbms_db_version.ver_le_11 $then
result_cache
$end
as
begin
return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss')
- to_date('01-01-90','dd-mm-yy')) * (86400/2400));
end ssndate;
/

The function ssndate compiled successfully.
The next step I took was to create a view through the following query,

create or replace view WEBLOG_VIEWS
as
select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT",
C_IP,
CS_USER_AGENT,
(CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK
from WEBLOG;

This was successful as well. The problem is in the next step where I try to do data grouping.

create table FINAL_WEBLOG as
select SESSION_DT, C_IP, CS_USER_AGENT,
listagg(WEB_LINK, ' ')
within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS"
from WEBLOG_VIEWS
group by C_IP, CS_USER_AGENT, SESSION_DT
order by SESSION_DT

For this, I got the error,

Error starting at line 1 in command:
create table FINAL_LOG as
select SESSION_DT, C_IP, CS_USER_AGENT,
listagg(WEB_LINK, ' ')
within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS"
from WEBLOG_VIEWS
group by C_IP, CS_USER_AGENT, SESSION_DT
order by SESSION_DT
Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-01861: literal does not match format string
ORA-06512: at "DMUSER.SSNDATE", line 11
ORA-06512: at line 1
01861. 00000 - "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace).
If the "FX" modifier has been toggled on, the literal must
match exactly, with no extra whitespace.
*Action:   Correct the format string to match the literal.

I don't know where I'm going wrong with this.. the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively. If anyone has any clue about this I would be sincerely grateful for any help that I can get!! It's quite urgent..

The Oracle version is 11.2.0.1.0

Edited by: 975265 on Dec 5, 2012 5:31 PM
  • 1. Re: SQL Error: ORA-01861: literal does not match format string
    sb92075 Guru
    Currently Being Moderated
    the date and time are in no format. Example: 30118 and 0:00:09 respectively.
    what date is 30118?
  • 2. Re: SQL Error: ORA-01861: literal does not match format string
    978268 Newbie
    Currently Being Moderated
    I'm taking it as 03-01-18 (maybe 2018?)
  • 3. Re: SQL Error: ORA-01861: literal does not match format string
    sb92075 Guru
    Currently Being Moderated
    975265 wrote:
    I'm taking it as 03-01-18 (maybe 2018?)
    '10-11-12'
    Which is correct DATE below for string above?
    Oct. 11 2012
    Nov. 10 2012
    Nov. 12 2010
    Dec. 11 2010
    Oct. 12 2011
    Dec. 10 2011
    I'll give you 6 guesses, since the first 5 will be incorrect.
  • 4. Re: SQL Error: ORA-01861: literal does not match format string
    rp0428 Guru
    Currently Being Moderated
    >
    SQL Error: ORA-01861: literal does not match format string
    ORA-06512: at "DMUSER.SSNDATE", line 11
    ORA-06512: at line 1
    01861. 00000 - "literal does not match format string"
    *Cause: Literals in the input must be the same length as literals in
    the format string (with the exception of leading whitespace).
    If the "FX" modifier has been toggled on, the literal must
    match exactly, with no extra whitespace.
    *Action: Correct the format string to match the literal.

    I don't know where I'm going wrong with this.. the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively. If anyone has any clue about this I would be sincerely grateful for any help that I can get!! It's quite urgent
    >
    Why do you think the to_date function is fine when Oracle is telling you it isn't?

    Even the simples of tests would show you what the problem is.
    select ssndate('30118', '0:00:09') as "SESSION_DT" from dual
    
    ORA-01861: literal does not match format string
    ORA-06512: at "SCOTT.SSNDATE", line 6
    ORA-06512: at line 1
    Itsn't that amazing? I got exactly the same error you did simply by testing the function.
  • 5. Re: SQL Error: ORA-01861: literal does not match format string
    978268 Newbie
    Currently Being Moderated
    Um.. I was actually going for dd-mm-yy..
    But to answer your question.. it's the second one?
  • 6. Re: SQL Error: ORA-01861: literal does not match format string
    978268 Newbie
    Currently Being Moderated
    Oh wait, it's the first one..
  • 7. Re: SQL Error: ORA-01861: literal does not match format string
    978268 Newbie
    Currently Being Moderated
    rp0428 wrote:
    Why do you think the to_date function is fine when Oracle is telling you it isn't?

    Even the simples of tests would show you what the problem is.
    >
    select ssndate('30118', '0:00:09') as "SESSION_DT" from dual

    ORA-01861: literal does not match format string
    ORA-06512: at "SCOTT.SSNDATE", line 6
    ORA-06512: at line 1
    Itsn't that amazing? I got exactly the same error you did simply by testing the function.
    Point taken. ^ ^'
    But I'm just not sure about the solution.

    Edited by: 975265 on Dec 5, 2012 7:33 PM

    Edited by: 975265 on Dec 5, 2012 7:34 PM
  • 8. Re: SQL Error: ORA-01861: literal does not match format string
    rp0428 Guru
    Currently Being Moderated
    >
    I'm just not sure about the solution.
    >
    That is just one of the many prices you will pay for storing date/time data in VARCHAR2 columns. Anyone, at any time, can put junk in the column and you will never know.

    The 'solution' to the problem is to use the proper datatype for the data. If you are stuck with the current datatypes and data then you need to add some check constraints or a trigger to validate the data when it is inserted to keep the junk out of the table as best you can.
  • 9. Re: SQL Error: ORA-01861: literal does not match format string
    978268 Newbie
    Currently Being Moderated
    I've tried to use the datatype "DATE", and "TIMESTAMP", for dates and times while importing, but it always mentioned an error stating to put the correct data type, since this one is in the wrong format..
  • 10. Re: SQL Error: ORA-01861: literal does not match format string
    rp0428 Guru
    Currently Being Moderated
    >
    I've tried to use the datatype "DATE", and "TIMESTAMP", for dates and times while importing, but it always mentioned an error stating to put the correct data type, since this one is in the wrong format..
    >
    Oh, we quite understand the issue. You trited to 'solve' that problem by corrupting the datatype to accept the garbage you were giving it.

    All you did was HIDE the problem and postpone it until now you, AND EVERYONE OTHER DEVELOPER IN YOUR COMPANY, now has to deal with garbage.

    So instead of dealing with the garbage ONE TIME, when you insert the data, you have chosen to deal with garbage EVERY TIME you access the data.

    BAD, BAD, BAD choice.

    As part of an ETL process it is ok to load data like that into a staging table where you can more easily bring Oracle's power to bear to fix the data for insertion into the actual table. But to leave data like that for users or developers to have to deal with is a fundamental mistake.

    The data has to be fixed. You cannot excape that reality. Deal with it.
  • 11. Re: SQL Error: ORA-01861: literal does not match format string
    978268 Newbie
    Currently Being Moderated
    Ok.. Looks like I touched a nerve there. I apologize. I'm still a student, and this is the first time that I've tried something at this level. I'm still in the learning process, so I was hoping that someone could point me in the right direction in order to "fix" the data.
  • 12. Re: SQL Error: ORA-01861: literal does not match format string
    EdStevens Guru
    Currently Being Moderated
    975265 wrote:
    Ok.. Looks like I touched a nerve there. I apologize. I'm still a student, and this is the first time that I've tried something at this level. I'm still in the learning process, so I was hoping that someone could point me in the right direction in order to "fix" the data.
    Not so much touching a nerve as simply trying to implement a very very poor, but all too common, practice. Since you are a student (which we didn't know until this post) most people will cut you some slack. However, this little exchange should now be burned into your brain as you move forward. One of the very first rules of programming is to ALWAYS use the correct data types for your data. And along with that, never ever depend on implicit type conversions - always use the proper explicit conversion functions.

    And as a slight follow-on, when considering the appropriate data type, don't assume that just because we refer to a given element as a 'something number' that it is indeed a number. Telephone "numbers" are NOT numbers. U.S. Social Security "numbers" are NOT numbers. U.S. Postal Zip codes are NOT numbers. All are just character strings which, by convention, we limit to the same characters we use to represent numbers.

    And since this entire discussion came up around the representation of dates, you might want to take a look at http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

    Now, go forth and be a smarter programmer than your peers.

    Edited by: EdStevens on Dec 6, 2012 6:12 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points