Home » SQL & PL/SQL » SQL & PL/SQL » SQL Error: ORA-01861: literal does not match format string
SQL Error: ORA-01861: literal does not match format string [message #572082] Thu, 06 December 2012 00:08 Go to next message
Saya22
Messages: 14
Registered: December 2012
Location: London
Junior Member
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 following 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
Re: SQL Error: ORA-01861: literal does not match format string [message #572093 is a reply to message #572082] Thu, 06 December 2012 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Trying to reproduce what you said I got:
SQL> create or replace function ssndate(p_date in varchar2 default '03-01-18',
  2  p_time in varchar2)
  3  return number
  4  $if dbms_db_version.ver_le_10 $then
  5  deterministic
  6  $elsif dbms_db_version.ver_le_11 $then
  7  result_cache
  8  $end
  9  as
 10  begin
 11  return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss')
 12  - to_date('01-01-90','dd-mm-yy')) * (86400/2400));
 13  end ssndate;
 14  /

Function created.

SQL>
SQL> create or replace view WEBLOG_VIEWS
  2  as
  3  select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT",
  4  C_IP,
  5  CS_USER_AGENT,
  6  (CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK
  7  from WEBLOG;
from WEBLOG
     *
ERROR at line 7:
ORA-00942: table or view does not exist

Can you post the CREATE TABLE statement for this table.

Regards
Michel
Re: SQL Error: ORA-01861: literal does not match format string [message #572112 is a reply to message #572093] Thu, 06 December 2012 03:22 Go to previous messageGo to next message
Saya22
Messages: 14
Registered: December 2012
Location: London
Junior Member
Ok..

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;

Re: SQL Error: ORA-01861: literal does not match format string [message #572122 is a reply to message #572112] Thu, 06 December 2012 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not the view (we have it in your post), the table we have not.

Regards
Michel
Re: SQL Error: ORA-01861: literal does not match format string [message #572129 is a reply to message #572122] Thu, 06 December 2012 06:05 Go to previous messageGo to next message
Saya22
Messages: 14
Registered: December 2012
Location: London
Junior Member
Oh right.. I didn't have to create the table. All I did was import the data in the ODMiner, under Tables, and it automatically created one.

CREATE TABLE "DMUSER"."WEBLOG"
  (
    "LOG_DATE"      VARCHAR2(10 BYTE),
    "LOG_TIME"      VARCHAR2(7 BYTE),
    "C_IP"          CHAR(15 BYTE),
    "CS_USERNAME"   CHAR(1 BYTE),
    "SC_METHOD"     CHAR(4 BYTE),
    "CS_URI_STEM"   CHAR(56 BYTE),
    "CS_URI_QUERY"  CHAR(441 BYTE),
    "SC_STATUS"     NUMBER(*,0),
    "CS_HOST"       CHAR(19 BYTE),
    "CS_USER_AGENT" CHAR(157 BYTE),
    "CS_COOKIE"     CHAR(143 BYTE),
    "CS_REFERRER"   CHAR(517 BYTE)
  )
Re: SQL Error: ORA-01861: literal does not match format string [message #572130 is a reply to message #572129] Thu, 06 December 2012 06:15 Go to previous messageGo to next message
cookiemonster
Messages: 10903
Registered: September 2008
Location: Rainy Manchester
Senior Member
Only way I can see you getting this error is if a value in log_date isn't in the format 'dd-mm-yy' or a value in log_time isn't in the format 'hh24:mi:ss'
Re: SQL Error: ORA-01861: literal does not match format string [message #572141 is a reply to message #572129] Thu, 06 December 2012 08:26 Go to previous message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem for me:
SQL> create table FINAL_WEBLOG as
  2  select SESSION_DT, C_IP, CS_USER_AGENT,
  3  listagg(WEB_LINK, ' ')
  4  within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS"
  5  from WEBLOG_VIEWS
  6  group by C_IP, CS_USER_AGENT, SESSION_DT
  7  order by SESSION_DT
  8
SQL> /

Table created.

SQL> select * from v$version where rownum=1;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

But of course my table is empty.

Note: Do NOT give any schema, tablespace or storage parameters in your test case, we have not the same ones.

Regards
Michel

[Updated on: Thu, 06 December 2012 08:26]

Report message to a moderator

Previous Topic: Can we use the temp table in the function which uses ref cursor
Next Topic: Error message with no column name - does not match expected data length for column '[Xyz.Oracle].'''
Goto Forum:
  


Current Time: Wed Aug 20 17:39:57 CDT 2014

Total time taken to generate the page: 0.08865 seconds