How To Set Default Value In Sql Loader Control File
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 get from DECIMAL to DATE in one pass, I'k trying to load it to a VARCHAR2(7), then that I tin TO_DATE information technology by view. Merely I am finding it difficult to NULLIF the field and instead get a zero. I accept tried the following in SQL*Loader and ultimately desire to use an external table :-
, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( seven ) "DECODE(LENGTH(:CUCCJ),1,Cypher,:CUCCJ)"
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( seven ) NULLIF CUCCJ = " "
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( vii ) NULLIF (203:206) = " "
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( vii ) 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 ( seven ) NULLIF CUCCJ = BLANKS
--, CUCCJ POSITION ( 203 : 206 ) DECIMAL ( 7 ) NULLIF (203:206) = BLANKS
Just 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 case, I get :-
select cuccj, '10'||cuccj||'x', length(cuccj), ascii(cuccj) from ext_cup01301;
CUCCJ 'X'||CUCC LENGTH(CUCCJ) ASCII(CUCCJ)
------- --------- ------------- ------------
0 x0x i 48
0 x0x one 48
0 x0x 1 48
0 x0x i 48
0 x0x ane 48
...
This is driving me MAD !!!
Regards
Adrian
July 17, 2003 - ten:38 am UTC
with an external table -- you would only create a view to decode anything you wanted, it is infinitely easier. I don't understand what issue yous percieve there?
Tom
I exercise have views to "expose" my external tables. In those views, I accept placed all the business rules that nosotros require to transform the information. What I don't want to have to do inside the view is to clean every column besides, else I'd accept 483 TRIM statements in the view against our largest XT and loads of boosted Instance/DECODEs.
SQL*Loader and XT admission parameters patently enable us to do some pretty high level cleaning, such as LDRTRIM and NULLIF/DEFAULTIF. (Incidentally, I have a potential problems being investigated past support with LRTRIM and LTRIM for XTs on nine.2.0.1). Anyway, I want to be able to separate the high level cleaning from the bodily data rules.
The overriding point is that I want to exist able to practice it using NULLIF and information technology doesn't seem to be working for me. I'm non averse to workarounds because nosotros need them all over the place, but I just desire someone to tell me that it either doesn't work equally expected in which case I'll happily use the workaround as you propose or does work but I've got the syntax wrong.
Many thanks
Adrian
July 18, 2003 - 8:44 am UTC
so? yous'll have them in the create table or the create view -- but y'all'll have them.
rather and so spend a while looking into it -- I personally would do every bit piddling every bit I could in the create table and every thing I can in the create view. One is slightly "easier" then the other.
Tom,
I have a state of affairs in loading data with sql*loder.
For a engagement cavalcade is Goose egg comes in data file I demand to replace with a value 31-Dec-2019.
I tried with decode as below, merely 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 tabular array "DWDBA"."TB_OST05880_STG_INVCE_DET", column RQSTN_DATE.
ORA-01861: literal does not match format string
Please assist me out here to sole the problem.
Thanks in advance.
- ARC
July 28, 2006 - eight:43 pm UTC
oh, this, scares, me.
why 31-dec-2019? what is wrong with using NULL. this is going to hurt you.
sounds like rqstn_date is not a valid appointment using your default engagement format. Await in your bad file and see what the value for rqstn_date is in that failed record.
Tom,
Thanks for the respond!
Every bit you said I verified the bad file and format is aforementioned "YYYYMMDD' what I given in cntrol file.
Is some where I did mistake?
If I give RQSTN_DATE Date "YYYYMMDD" with out whatsoever other functions for replacing the Nothing values it is working fine and if any Zip values in that cavalcade, are inserting as NULL's.
Regarding 31-Dec-2019, it is business requirement.
Delight advise.
Thank you
- ARC
July 29, 2006 - 4:33 pm UTC
oh, just you don't have a date in the decode, you lot accept a STRING by so.
RQSTN_DATE char
"decode(trim(:RQSTN_DATE),null,to_date('20191231','YYYYMMDD'),to_date(:RQSTN_DATE,'yyyymmdd'))",
you have implicit conversions happening in that location.
Thanks for posting the responses. Information technology helped me with my loader date problem.
A reader, Baronial 30, 2006 - 5:47 am UTC
Hi Tom,
How tin can I load a default value to Non null fields?
I accept a table called CRLIM and with post-obit columns.
C_DATE - Non NULL date (Default value is '01/01/1801')
C_FLAG - Non Goose egg number(1) (Default value is -1)
C_STAT - Not NULL varchar2(five) (Default value is 'Due north/K')
My sample data file looks like (Terminated by '|' enclosed past ")
"21/12/2002"|"9"|"UNCL"
""|"nine"|"VAL"
"15/11/2002"|""|"Exam"
"eleven/01/2002"|"7"|""
""|""|"VAL"
Cheers & Regards
Sheshadri
December 11, 2006 - ane:02 pm UTC
the defaults volition non work there because y'all Accept a value
if you are using sqlldr, you'll accept to put the default value into the control file (using nvl or whatever you want).
Hullo,
I used the following command file for loading the values using nvl. But yet 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/One thousand')"
)
Please guide me, how can I load these records.
Cheers & Regards
Sheshadri
December 12, 2006 - seven:08 am UTC
did you check you log file: Record 2: Rejected - Error on tabular array CRLIM, cavalcade C_DATE. ORA-01858: a non-numeric grapheme was found where a numeric was expected your control file doesn't accept "delimited by, enclosed by" for instance... LOAD DATA infile * TRUNCATE INTO TABLE crlim FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ( C_DATE appointment "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,'Due north/Chiliad')" ) begindata "21/12/2002"|"9"|"UNCL" ""|"9"|"VAL" "15/11/2002"|""|"TEST" "eleven/01/2002"|"7"|"" ""|""|"VAL" ops$tkyte%ORA10GR2> !sqlldr / examination SQL*Loader: Release ten.2.0.2.0 - Production on Tue December 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 nine UNCL 01/01/1801 ix VAL 15/eleven/2002 -i Test 11/01/2002 7 N/Yard 01/01/1801 -1 VAL
Why you do not effort 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 Zilch, '01/01/1801',:C_DATE)",
C_FLAG NULLIF C_FLAG=BLANKS "nvl(:C_FLAG,'-1')",
C_STAT NULLIF C_STAT=BLANKS "nvl(:C_STAT,'Northward/M')"
)
December 12, 2006 - vii:xv am UTC
non necessary, they forgot to tell sqlldr how to parse the lines of information - and that c_date is a date with a non-standard format (not that we should really rely on the default appointment mask)
I tried this aforementioned concept on something I am working on now and it did not work. I kept getting the ORA-01841 full year must exist between -4713 and 9999 and not exist 0 error. I could not discover a work around. There are valid circumstances when a NULL appointment is needed. I had to utilise FILLER which is bad work around because at present I accept to edit the CTL file and scrutinize the data for the appointment.
November 09, 2022 - vii:27 am UTC
Please give a full example of the problem you're having via a new question
How To Set Default Value In Sql Loader Control File,
Source: https://asktom.oracle.com/pls/apex/asktom.search?tag=sql-loader-and-date
Posted by: caudlebude1972.blogspot.com
0 Response to "How To Set Default Value In Sql Loader Control File"
Post a Comment