I have an EBCDIC file with a packed decimal that is supposed to contain a numeric representation of a date in yyyyddd format. As we can't go from DECIMAL to DATE in one pass, I'm trying to load it to a VARCHAR2(7), so that I can TO_DATE it by view. But I am finding it difficult to NULLIF the field and instead get a zero. I have tried the following in SQL*Loader and ultimately want to use an external table :-

, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) "DECODE(LENGTH(:CUCCJ),1,NULL,:CUCCJ)"
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) NULLIF CUCCJ = " "
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) NULLIF (203:206) = " "
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) NULLIF CUCCJ = '0'
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) NULLIF (203:206) = '0'
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) NULLIF (203:206) = '0000000'
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) NULLIF CUCCJ = BLANKS
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) NULLIF (203:206) = BLANKS

Only the DECODE works in SQL*Loader, but as I want to use an external table, I really need the NULLIF clause to work for me. In all cases other than the DECODE example, I get :-

select cuccj, 'x'||cuccj||'x', length(cuccj), ascii(cuccj) from ext_cup01301;

CUCCJ 'X'||CUCC LENGTH(CUCCJ) ASCII(CUCCJ)
------- --------- ------------- ------------
0 x0x 1 48
0 x0x 1 48
0 x0x 1 48
0 x0x 1 48
0 x0x 1 48
...

This is driving me MAD !!!

Regards
Adrian

Tom

I do have views to "expose" my external tables. In those views, I have placed all the business rules that we require to transform the data. What I don't want to have to do inside the view is to clean every column as well, else I'd have 483 TRIM statements in the view against our largest XT and loads of additional CASE/DECODEs.

SQL*Loader and XT access parameters obviously enable us to do some pretty high level cleaning, such as LDRTRIM and NULLIF/DEFAULTIF. (Incidentally, I have a potential bug being investigated by support with LRTRIM and LTRIM for XTs on 9.2.0.1). Anyway, I want to be able to separate the high level cleaning from the actual data rules.

The overriding point is that I want to be able to do it using NULLIF and it doesn't seem to be working for me. I'm not averse to workarounds because we need them all over the place, but I just want someone to tell me that it either doesn't work as expected in which case I'll happily use the workaround as you suggest or does work but I've got the syntax incorrect.

Many thanks
Adrian

Tom,
I have a situation in loading data with sql*loder.
For a date column is NULL comes in data file I need to replace with a value 31-Dec-2019.
I tried with decode as below, but its not working.
RQSTN_DATE DATE "YYYYMMDD" "decode(trim(:RQSTN_DATE),null,to_date('20191231','YYYYMMDD'),:RQSTN_DATE)",
Getting below error:
Record 47: Rejected - Error on table "DWDBA"."TB_OST05880_STG_INVCE_DET", column RQSTN_DATE.
ORA-01861: literal does not match format string

Please help me out here to sole the problem.

Thanks in advance.

- ARC

Tom,
Thanks for the reply!
As you said I verified the bad file and format is same "YYYYMMDD' what I given in cntrol file.
Is some where I did mistake?
If I give RQSTN_DATE DATE "YYYYMMDD" with out any other functions for replacing the NULL values it is working fine and if any NULL values in that column, are inserting as NULL's.
Regarding 31-Dec-2019, it is business requirement.
Please advise.

Thanks
- ARC

Thanks for posting the responses. It helped me with my loader date problem.

Thanks Tom. It helped.

Hi Tom,

How can I load a default value to Not null fields?
I have a table called CRLIM and with following columns.

C_DATE - NOT NULL date (Default value is '01/01/1801')
C_FLAG - NOT NULL number(1) (Default value is -1)
C_STAT - NOT NULL varchar2(5) (Default value is 'N/K')

My sample data file looks like (Terminated by '|' enclosed by ")

"21/12/2002"|"9"|"UNCL"
""|"9"|"VAL"
"15/11/2002"|""|"TEST"
"11/01/2002"|"7"|""
""|""|"VAL"

Thanks & Regards
Sheshadri

Hi,

I used the following control file for loading the values using nvl. But still the rows are getting discarded.

LOAD DATA
CHARACTERSET UTF8
INFILE 'A.txt'
BADFILE 'A.bad'
DISCARDFILE 'A.dis'
TRUNCATE INTO TABLE crlim
(
C_DATE NULLIF C_DATE=BLANKS "nvl(:C_DATE,'01/01/1801')",
C_FLAG NULLIF C_FLAG=BLANKS "nvl(:C_FLAG,'-1')",
C_STAT NULLIF C_STAT=BLANKS "nvl(:C_STAT,'N/K')"
)

Please guide me, how can I load these records.

Thanks & Regards
Sheshadri

did you check you log file:


Record 2: Rejected - Error on table CRLIM, column C_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected


your control file doesn't have "delimited by, enclosed by"

for example...


LOAD DATA
infile *
TRUNCATE INTO TABLE crlim
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
(
C_DATE date "dd/mm/yyyy" NULLIF C_DATE=BLANKS "nvl(:C_DATE,'01/01/1801')",
C_FLAG NULLIF C_FLAG=BLANKS "nvl(:C_FLAG,'-1')",
C_STAT NULLIF C_STAT=BLANKS "nvl(:C_STAT,'N/K')"
)
begindata
"21/12/2002"|"9"|"UNCL"
""|"9"|"VAL"
"15/11/2002"|""|"TEST"
"11/01/2002"|"7"|""
""|""|"VAL"




ops$tkyte%ORA10GR2> !sqlldr / test

SQL*Loader: Release 10.2.0.2.0 - Production on Tue Dec 12 07:07:43 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 5

ops$tkyte%ORA10GR2> select * from crlim;

C_DATE                             C_FLAG C_STAT
------------------------------ ---------- --------------------
21/12/2002                              9 UNCL
01/01/1801                              9 VAL
15/11/2002                             -1 TEST
11/01/2002                              7 N/K
01/01/1801                             -1 VAL

 

Why you do not try a decode

LOAD DATA
CHARACTERSET UTF8
INFILE 'A.txt'
BADFILE 'A.bad'
DISCARDFILE 'A.dis'
TRUNCATE INTO TABLE crlim
(
C_DATE NULLIF C_DATE=BLANKS "DECODE(:C_DATE,NOT NULL, '01/01/1801',:C_DATE)",
C_FLAG NULLIF C_FLAG=BLANKS "nvl(:C_FLAG,'-1')",
C_STAT NULLIF C_STAT=BLANKS "nvl(:C_STAT,'N/K')"
)

I tried this same concept on something I am working on now and it did not work. I kept getting the ORA-01841 full year must be between -4713 and 9999 and not be 0 error. I could not find a work around. There are valid circumstances when a NULL date is needed. I had to use FILLER which is bad work around because now I have to edit the CTL file and scrutinize the data for the date.

I want to add some code in ctrl file of SQL loader to get difference between start and end date and based on difference I want to create third coloumn...