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  |
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 #308058 is a reply to message #308046] |
Thu, 20 March 2008 22:52  |
 |
Barbara Boehmer
Messages: 9094
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 #308167 is a reply to message #308153] |
Fri, 21 March 2008 12:54 |
|
Barbara Boehmer
Messages: 9094
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>
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 17 03:34:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
