Home » RDBMS Server » Server Utilities » Extract date from a column in SQL Loader
Extract date from a column in SQL Loader [message #638200] Fri, 05 June 2015 16:44 Go to next message
jimitkr
Messages: 6
Registered: June 2015
Junior Member
Hi Friends,

I have a column STAT_SESSION_ID in my source file (.dat) which is 30 digits. The first 14 digits in this column are actually a character timestamp as seen below:
201505261048310090000468709941

I am trying to create a control file where one of the fields stores the date extracted from the above id:

The syntax of my date column is as follows:

STAT_SESS_DATE DATE "TO_DATE(SUBSTR(TO_CHAR(:STAT_SESSION_ID), 1, 14), 'YYYYMMDDHH24MISS')",

When i run my script to process incoming data i always get the error:
"ORA-01821: date format not recognized"

What am i doing wrong?

-- text of attached file:
UNRECOVERABLE
LOAD DATA
INFILE '/app_data1/stats/sql_loader_scripts/tmpFile'
APPEND
INTO TABLE stage.stat_match_track_test
fields terminated by "\t"
TRAILING NULLCOLS
(
STAT_SESSION_ID,
STAT_SEQ,
TRACK_ID,
TUI_ID,
ORDINAL,
STAT_SESS_DATE DATE "TO_DATE(SUBSTR(TO_CHAR(:STAT_SESSION_ID), 1, 14), 'YYYYMMDDHH24MISS')",
FEED_TUI_ID,
FEED_DATE,
EXTERNALID,
EXTERNALID_DATASOURCE,
MATCH_POSITION,
)


[mod-edit: text of attached file added to post]

[Updated on: Sat, 06 June 2015 19:49] by Moderator

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638201 is a reply to message #638200] Fri, 05 June 2015 17:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
Senior Member
Your control file has field FEED_DATE. Is corresponding table column data type DATE? IF so, then most likely it is FEED_DATE that's causing error. In any case, if FEED_DATE is DATE your control file should provide explicit date format and not rely on session default.

SY.

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638202 is a reply to message #638201] Fri, 05 June 2015 17:37 Go to previous messageGo to next message
jimitkr
Messages: 6
Registered: June 2015
Junior Member
The FEED_DATE is actually just a string and is also varchar in the database. I am pretty sure it is the STAT_SESSION_DATE i am trying to extract that is creating the issue. Here is a DESC of my target table:

Name Null Type
------------------------- -------- ------------------
STAT_SESSION_ID NOT NULL NUMBER(30)
STAT_SEQ NUMBER(30)
TRACK_ID NUMBER(10)
TUI_ID NUMBER
ORDINAL NUMBER
STAT_SESS_DATE NOT NULL DATE
FACT_PROC_STATUS NOT NULL VARCHAR2(1 CHAR)
FACT_PERFORM_PROC_STATUS NOT NULL VARCHAR2(1 CHAR)
FACT_ACTIVITY_PROC_STATUS NOT NULL VARCHAR2(1 CHAR)
FEED_TUI_ID NUMBER(14)
FEED_DATE VARCHAR2(50 CHAR)
STAT_DELAY_IN_SEC NUMBER(14)
EXTERNALID VARCHAR2(128 CHAR)
EXTERNALID_DATASOURCE VARCHAR2(128 CHAR)
MATCH_POSITION NUMBER(14)

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638204 is a reply to message #638202] Fri, 05 June 2015 18:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
SQL> DECLARE
  2  date_str VARCHAR2(50) := '201505261048310090000468709941';
  3  old_date date;
  4  BEGIN
  5  old_date := TO_DATE(SUBSTR(date_str, 1, 14), 'YYYYMMDDHH24MISS');
  6  dbms_output.put_line(to_char(old_date, 'DD/MON/YYYY HH24:MI:SS'));
  7  end;
  8  /
26/MAY/2015 10:48:31

PL/SQL procedure successfully completed.


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

[Updated on: Fri, 05 June 2015 18:46]

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638212 is a reply to message #638201] Sat, 06 June 2015 06:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then look in .BAD file and examine STAT_SESSION_ID value in failed row(s).

SY.

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638213 is a reply to message #638202] Sat, 06 June 2015 06:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
Senior Member
Also, CTL file you posted is either partial post or has error:

...
MATCH_POSITION,
)

SY.

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638218 is a reply to message #638202] Sat, 06 June 2015 08:01 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
jimitkr wrote on Fri, 05 June 2015 17:37
The FEED_DATE is actually just a string and is also varchar in the database.

That, in and of itself, is a SERIOUS design flaw. If you do not correct that, you will continue to pay for the mistake. If you do not correct it, I predict you will be back on the forums multiple times asking questions about how to deal with it, even after you get past this immediate problem.

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638225 is a reply to message #638200] Sat, 06 June 2015 19:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
It would help if you post one or more rows of sample data that cause the error.

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638266 is a reply to message #638204] Mon, 08 June 2015 13:26 Go to previous messageGo to next message
jimitkr
Messages: 6
Registered: June 2015
Junior Member
Yes i will post the full details shortly.

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638267 is a reply to message #638213] Mon, 08 June 2015 13:27 Go to previous messageGo to next message
jimitkr
Messages: 6
Registered: June 2015
Junior Member
I removed the comma but still face the same issue

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638268 is a reply to message #638225] Mon, 08 June 2015 13:36 Go to previous messageGo to next message
jimitkr
Messages: 6
Registered: June 2015
Junior Member
Hi Barbara / Friends,

Here you go.

I'm attaching my source data file with this post.

The script to pick up data from this source file and load to an oracle table is "load_stat_match_track.sh" below:

-------------------------------------------------------------------------------------------------------------------------------------
cd /app_data1/stats/loader
for file in STAT_MATCH_TRACK*
do
sqlldr stage/stage01@gndw control=/app_data1/stats/sql_loader_scripts/stat_match_track.ctl data=/app_data1/stats/loader/$file direct=true
done
------------------------------------------------------------------------------------------------------------------------------------- -------

My control file is as below:

---------------------------------------------------------------------------------------------------------
UNRECOVERABLE
LOAD DATA
APPEND
INTO TABLE stage.stat_match_track_test
fields terminated by "\t"
TRAILING NULLCOLS
(
STAT_SESSION_ID,
STAT_SEQ,
TRACK_ID,
TUI_ID,
ORDINAL,
STAT_SESS_DATE DATE "TO_DATE(SUBSTR(TO_CHAR(:STAT_SESSION_ID), 1, 14), 'YYYYMMDDHH24MISS')",
FEED_TUI_ID,
FEED_DATE,
EXTERNALID,
EXTERNALID_DATASOURCE,
MATCH_POSITION
)

--------------------------------------------------------------------------------------------------


I try to load my data with the command "./load_stat_match_track.sh"

This is the output of the above command:

--------------------------------------------------------------------------------------------------------------

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Jun 8 11:33:58 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ORA-01821: date format not recognized

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Jun 8 11:33:58 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ORA-01821: date format not recognized

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Jun 8 11:33:58 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ORA-01821: date format not recognized

---------------------------------------------------------------------------------------------------------

Also, there is no bad file getting generated in the folder where i run this script. I just get the above error and nothing else.


Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638299 is a reply to message #638268] Tue, 09 June 2015 05:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You have incorrectly combined two different syntaxes for declaring a date in your control file. Change the following line:

STAT_SESS_DATE DATE "TO_DATE(SUBSTR(TO_CHAR(:STAT_SESSION_ID), 1, 14), 'YYYYMMDDHH24MISS')",

to:

STAT_SESS_DATE "TO_DATE(SUBSTR(TO_CHAR(:STAT_SESSION_ID), 1, 14), 'YYYYMMDDHH24MISS')",

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638403 is a reply to message #638299] Wed, 10 June 2015 14:19 Go to previous messageGo to next message
jimitkr
Messages: 6
Registered: June 2015
Junior Member
Barbara Boehmer wrote on Tue, 09 June 2015 05:53
You have incorrectly combined two different syntaxes for declaring a date in your control file. Change the following line:

STAT_SESS_DATE DATE "TO_DATE(SUBSTR(TO_CHAR(:STAT_SESSION_ID), 1, 14), 'YYYYMMDDHH24MISS')",

to:

STAT_SESS_DATE "TO_DATE(SUBSTR(TO_CHAR(:STAT_SESSION_ID), 1, 14), 'YYYYMMDDHH24MISS')",



Removing the keyword "DATE" worked! Is a specific button to close this thread?

Report message to a moderator

Re: Extract date from a column in SQL Loader [message #638405 is a reply to message #638403] Wed, 10 June 2015 19:25 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Quote:
Removing the keyword "DATE" worked! Is a specific button to close this thread?


I am glad it worked. We don't have a specific method for marking a thread closed or answered. Just what you posted is sufficient.

Report message to a moderator

Previous Topic: Metadata of Entire DB.
Next Topic: Strange issue in SQL Loader
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 30 20:05:02 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.