0 Replies - 5080 Views - Last Post: 05 December 2012 - 06:03 PM

#1 Saya_26  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 40
  • Joined: 07-November 11

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

Posted 05 December 2012 - 06:03 PM

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

Is This A Good Question/Topic? 0
  • +

Page 1 of 1