Home » RDBMS Server » Server Utilities » inserting a default date, when input date is blank, via SQL LOADER (10g)
inserting a default date, when input date is blank, via SQL LOADER [message #308045] Thu, 20 March 2008 18:20 Go to next message
crw589
Messages: 4
Registered: March 2008
Junior Member
I'm trying to load a table from a flat file using sql loader. There are several dates on the flat file that are blank. The dates on the table are setup as NOT NULL. I'm attempting to use the NVL function to check for blanks on the incoming field, and want to plug in '9999-12-31' if the field is blank. The line is coded as... col_name_dt position(231:240) DATE "YYYY-MM-DD" "NVL(:COL_NAME_DT,'9999-12-31'))"
The line rejects with ORA-01841 (FULL) year must be between -4713 and +9999, and not be 0.
Any help would be much appreciated!
Re: inserting a default date, when input date is blank, via SQL LOADER [message #308046 is a reply to message #308045] Thu, 20 March 2008 18:27 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to establish valid/correct NLS_DATE_FORMAT,
or change date string to match what Oracle expects now.

SQL> select sysdate from dual;
Re: inserting a default date, when input date is blank, via SQL LOADER [message #308054 is a reply to message #308045] Thu, 20 March 2008 20:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
col_name_dt position(231:240) "nvl (to_date (:col_name_dt, 'yyyy-mm-dd'), to_date ('9999-12-31', 'yyyy-mm-dd'))"

Re: inserting a default date, when input date is blank, via SQL LOADER [message #308058 is a reply to message #308046] Thu, 20 March 2008 22:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I received your PM saying that you are still getting the same error message. Please post all follow-up questions in the forum thread, not via PM. If you are still getting the same message, then you must have some dates that are not in the yyyy-mm-dd format or do not have valid numbers, not just null values. Another option that you can use is to create a user-defined function and use that, but you should really find out what date values are wrong or in the wrong format and fix those. Here is an example of using a function:

create or replace function valid_date 
  (p_string in varchar2)
  return date
as
begin
  return nvl (to_date (p_string, 'yyyy-mm-dd'), to_date ('9999-12-31', 'yyyy-mm-dd'));
exception
  when others then return to_date ('9999-12-31', 'yyyy-mm-dd');
end valid_date;
/


Then is your control file:

col_name_dt position(231:240) "valid_date (:col_name_dt)"


[Updated on: Thu, 20 March 2008 22:53]

Report message to a moderator

Re: inserting a default date, when input date is blank, via SQL LOADER [message #308138 is a reply to message #308054] Fri, 21 March 2008 08:49 Go to previous messageGo to next message
crw589
Messages: 4
Registered: March 2008
Junior Member
I tried this again after removing the "DATE" that followed the start-end position of the particular column. Tried running the loader again. This time the reject reason is "missing right parens".
col_name_dt position(231:240) "nvl (to_date (:col_name_dt, 'yyyy-mm-dd'), to_date ('9999-12-31', 'yyyy-mm-dd'))"

Re: inserting a default date, when input date is blank, via SQL LOADER [message #308144 is a reply to message #308138] Fri, 21 March 2008 09:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You need to post the rest of your control file and some sample data. Your error regarding the parenthesis may be coming from somewhere else in the file. I have tested what I have posted and it works, so you are doing something different. Try copying and pasting exactly what I have posted, instead of typing.




[Updated on: Fri, 21 March 2008 09:46]

Report message to a moderator

Re: inserting a default date, when input date is blank, via SQL LOADER [message #308153 is a reply to message #308144] Fri, 21 March 2008 11:16 Go to previous messageGo to next message
crw589
Messages: 4
Registered: March 2008
Junior Member
Barb, thanks for the replies! here is a copy of the sqlloader that I'm using. The fields in question, I put ????? next to. Thanks for looking!
  • Attachment: qcqedwn.txt
    (Size: 8.04KB, Downloaded 235 times)
Re: inserting a default date, when input date is blank, via SQL LOADER [message #308161 is a reply to message #308153] Fri, 21 March 2008 12:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I need to see some sample data as well, just a few rows should do, or at least one row that produces the error. You SQL*Loader log file might also help.
Re: inserting a default date, when input date is blank, via SQL LOADER [message #308162 is a reply to message #308153] Fri, 21 March 2008 12:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
and your table structure, preferably in the form of a create table statement.
Re: inserting a default date, when input date is blank, via SQL LOADER [message #308167 is a reply to message #308153] Fri, 21 March 2008 12:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I extracted some pieces of what you provided and made a test case and it does not seem to handle the dates consistently. I think the best method would be to use a function. I have provided a brief demo below.

-- qcqedwn.dat:
2008-03-21            9999-12-31.
9999-12-31 2008-03-21.
9999-12-31 2008-03-21           .
2008-03-21     -  -   2008-03-21.
2008-03-21 garbage    2008-03-21.


-- test.ctl
OPTIONS (rows=100, direct=False)
load data
infile "qcqedwn.dat"
append
preserve blanks
INTO TABLE QCQEDWN
(
QCQEDWN_FIN_ICRD_DT POSITION(1:10)  "valid_date (:qcqedwn_fin_icrd_dt)",
QCQEDWN_SVCE_BGN_DT POSITION(12:21) "valid_date (:qcqedwn_SVCE_BGN_dt)",
QCQEDWN_SVCE_END_DT POSITION(23:32) "valid_date (:qcqedwn_SVCE_END_dt)"
)


-- function:
SCOTT@orcl_11g> create or replace function valid_date
  2    (p_string in varchar2)
  3    return date
  4  as
  5  begin
  6    return nvl (to_date (p_string, 'yyyy-mm-dd'), to_date ('9999-12-31', 'yyyy-mm-dd'));
  7  exception
  8    when others then return to_date ('9999-12-31', 'yyyy-mm-dd');
  9  end valid_date;
 10  /

Function created.

SCOTT@orcl_11g> show errors
No errors.


-- table:
SCOTT@orcl_11g> create table QCQEDWN
  2    (QCQEDWN_FIN_ICRD_DT  DATE,
  3  	QCQEDWN_SVCE_BGN_DT  DATE,
  4  	QCQEDWN_SVCE_END_DT  DATE)
  5  /

Table created.


-- data load:
SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl log=test.log


-- results:
SCOTT@orcl_11g> select * from QCQEDWN
  2  /

QCQEDWN_F QCQEDWN_S QCQEDWN_S
--------- --------- ---------
21-MAR-08 31-DEC-99 31-DEC-99
31-DEC-99 21-MAR-08 31-DEC-99
31-DEC-99 21-MAR-08 31-DEC-99
21-MAR-08 31-DEC-99 21-MAR-08
21-MAR-08 31-DEC-99 21-MAR-08

SCOTT@orcl_11g> 

Re: inserting a default date, when input date is blank, via SQL LOADER [message #308519 is a reply to message #308045] Mon, 24 March 2008 11:42 Go to previous messageGo to next message
crw589
Messages: 4
Registered: March 2008
Junior Member
Barb, I just wanted to thank you for all your suggestions! Our team has decided to make these date fields nullable on the table, so I am able now to load using the NULLIF blank.
Thanks again! I'm sure I'll be back here with more concerns.
Re: inserting a default date, when input date is blank, via SQL LOADER [message #308521 is a reply to message #308519] Mon, 24 March 2008 12:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was the best idea you could have.
Having a date in 9999 year was a very very bad idea.

Regards
Michel
Previous Topic: continue if ina sql loader
Next Topic: Import from dump
Goto Forum:
  


Current Time: Sun Dec 04 22:36:00 CST 2016

Total time taken to generate the page: 0.09073 seconds