Hi all,
i have this CTL FILE:
CREATE TABLE EXT_T_SI_EG_RAI_170630E1166A
(
WAEHRUNG CHAR(3)
,GESCHAEFT_ID VARCHAR2(48)
,GESCHAEFTSKATEGORIE CHAR(1)
,UNTERGESCHAEFT_ID VARCHAR2(24)
,SL_MELDERECHT CHAR(4)
,SL_ANSATZ CHAR(3)
,SL_LAUF CHAR(3)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DBDIR_TOE_170630E1166A
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
SKIP 1
TERRITORY "AMERICA"
CHARACTERSET WE8ISO8859P1
LOGFILE 't_si_eg_rai.log'
BADFILE 't_si_eg_rai.bad'
FIELDS
REJECT ROWS WITH ALL NULL FIELDS
REJECT ROWS WITH ALL NULL FIELDS
(
WAEHRUNG POSITION(2:4) CHAR LRTRIM
,GESCHAEFT_ID POSITION(5:52) CHAR LRTRIM
,GESCHAEFTSKATEGORIE POSITION(53:53) CHAR LRTRIM
,UNTERGESCHAEFT_ID POSITION(54:77) CHAR LRTRIM NULLIF UNTERGESCHAEFT_ID = BLANKS
,SL_MELDERECHT POSITION(78:81) CHAR LRTRIM
,SL_ANSATZ POSITION(82:84) CHAR LRTRIM
,SL_LAUF POSITION(85:87) CHAR LRTRIM)
)
)
LOCATION (xxxxxxxx)
)
REJECT LIMIT UNLIMITED
when i load the record always the field UNTERGESCHAEFT_ID is NULL
select * from TUKB100.T_si_EG_RAI PARTITION (TP_Si_EG_RAI_170630E1166A)
EUR E1166 30-GIU-17 170630E1166A 000020000168704200001ACC F (null)
EUR E1166 30-GIU-17 170630E1166A 0006297CG00000000061785 N (null)
EUR E1166 30-GIU-17 170630E1166A 0006297CG00000000159960 N (null)
EUR E1166 30-GIU-17 170630E1166A 0006297CG00000000232694 N (null)
Do you have any idea for substitute NULL with blanks?
Thank you so much for help
You want to convert nulls in the source file to "BLANKS"?
NVL/coalesce when you query it will do the trick:
declare
test_file utl_file.file_type;
begin
test_file := utl_file.fopen('TMP', 'test.txt', 'w');
utl_file.put_line(test_file, 'c1,,c3');
utl_file.fclose(test_file);
end;
/
create table t (
c1 varchar2(10),
c2 varchar2(10),
c3 varchar2(10)
) organization external (
default directory tmp
access parameters (
records delimited by newline
fields terminated by ',' (
c1, c2 , c3
)
)
location ('test.txt')
) reject limit unlimited;
select c1, nvl(c2, 'BLANK') c2, c3
from t;
C1 C2 C3
c1 BLANK c3 If you want to save some typing every time you query it, do the conversion in a view and query that:
create or replace view vw as
select c1, nvl(c2, 'BLANK') c2, c3 from t;
select * from vw;
C1 C2 C3
c1 BLANK c3