Oracle 加载外部表，用varchar2修剪，不带null

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

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)   

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 

create or replace view vw as 
  select c1, nvl(c2, 'BLANK') c2, c3 from t;
  
select * from vw;

C1   C2      C3   
c1   BLANK   c3