Database Administration (MOSC)

MOSC Banner

How to handle NULLs in external tables

edited May 12, 2009 1:20AM in Database Administration (MOSC) 4 commentsAnswered
Hi all,
I have the following tab-delimited data in my comps.txt file:
1	31	248161	"Company1"
	
2 31 248221 "Company2"
2 31 248301 "Company3"
248323 "Company4"
248403 "Company5"
1 "12" 248406 Company6

The following external table was created against the file:
CREATE TABLE EXT_COMPS
(
  C1  VARCHAR2(100 BYTE),
  C2  VARCHAR2(100 BYTE),
  C3  VARCHAR2(100 BYTE),
  C4  VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY MYDIR
     ACCESS PARAMETERS 
       ( records delimited by newline
     badfile MYDIR:'COMPS%a_%p.bad'
     logfile MYDIR:'COMPS%a_%p.log'
     fields terminated by 0x'09'
     OPTIONALLY ENCLOSED BY '"'
     missing field values are null
 ( C1 CHAR,
   C2 CHAR ,
   C3 CHAR ,
   C4 CHAR) )
     LOCATION (MYDIR:'comps.txt')  )
REJECT LIMIT UNLIMITED
	

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center