OraFAQ Forum: Server Utilities » SQL*Loader convert Char to NULL

Home » RDBMS Server » Server Utilities » SQL*Loader convert Char to NULL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader convert Char to NULL [message #176985]

Mon, 12 June 2006 05:24

zodiac_hh
Messages: 10
Registered: June 2006

Junior Member

Hi,

I would like to load a dump file into my data base via SQL*Loader. One of the fields in the dump contains "~" as an replacement for NULL values. How do I convert these Strings/Chars which only consist of ~ to NULL values in the database? I think it would work with an SQL Expression in the field definition of the CTL-File, but I am not familiar with the SQL functions Oracle offers.

Report message to a moderator

Re: SQL*Loader convert Char to NULL [message #176986 is a reply to message #176985]

Mon, 12 June 2006 05:31

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Lookup NULLIF in the Utilities Guide:

col1 POSITION (6:8) CHAR INTEGER EXTERNAL NULLIF col1 = '~'

Report message to a moderator

Re: SQL*Loader convert Char to NULL [message #177006 is a reply to message #176986]

Mon, 12 June 2006 07:53

zodiac_hh
Messages: 10
Registered: June 2006

Junior Member

Thanks for the hint, but that doesn't work in this case.
One field (I am using fixed length records by the way) contains this as a NULL value:

'       ~'

(without quotation marks). So there's some leading white space and that seems to break the comparison with the '~'. The other way round (~ with trailing blanks) works just fine.

Here's a snippet of my CTL-File:

K_FEHLM	POSITION(289)	CHAR(13) NULLIF K_FEHLM = '~',

and that fails with ORA-01722: invalid number because the value doesn't get converted to NULL.

Do you have another hint for me?

Report message to a moderator

Re: SQL*Loader convert Char to NULL [message #177015 is a reply to message #176985]

Mon, 12 June 2006 09:10

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Try with other alternative ...

K_FEHLM	POSITION(289)	CHAR(13)  "DECODE(TRIM(:K_FEHLM),'~',NULL,TRIM(:K_FEHLM))"  ,

Rajuvan.

Report message to a moderator

Previous Topic:	exp 10.2 & imp 10.1
Next Topic:	Import problem in oracle 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 08:12:06 CST 2024