Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    3

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

    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,

    Code:
    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,

    Code:
    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.
    Code:
    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 following error,

    Code:
    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. 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..

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Saya22 View Post
    I don't know where I'm going wrong with this. 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..
    Hello,

    Firstly, it is not urgent. Otherwise you would not waste your time on forum(s) and called for support/consultant/anybody responsible/skilled enough.

    Secondly, if "the date and time are in no format", why are you using concrete ones ('dd-mm-yy' for date, 'hh24:mi:ss' for time) in SSNDATE function. Which date does '30118' represent? Why? Of course it causes the error you showed as it may be interpreted ambiguously (e.g. January, 3rd or 30th, 2018 or November, 30th, 2008). Here, as separator dashes ('-') are missing, Oracle expects at least same number of digits (6) as in format mask.
    Code:
    SQL> with web_link as ( select '30118' log_date, '0:00:09' log_time from dual )
      2  select trunc((to_date(log_date||' '||log_time, 'dd-mm-yy hh24:mi:ss')
      3              - to_date('01-01-90','dd-mm-yy')) * (86400/2400))
      4  from web_link;
    select trunc((to_date(log_date||' '||log_time, 'dd-mm-yy hh24:mi:ss')
                                        *
    ERROR at line 2:
    ORA-01861: literal does not match format string
    Maybe you should consider to fix date and time in "possessed" data to (any) uniform format.

    Lastly, I do not understand, why you subtract January, 1st, 2090 (supposing that your system date is set to this century) in SSNDATE function. Anyway, as you did not describe its logic, it may be fine.

    [Edit: in 30th, zero was missing]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •