| Handling Nulls in Sql Loader [message #129284] |
Sat, 23 July 2005 01:59  |
abc123
Messages: 24
Registered: March 2005
Location: sa
|
Junior Member |
|
|
Hi
I need to detect blank values in my datafile to be uploaded through Sql Loader.
Data will come in format delimited by '|' and optionally enclosed by ''
Both numeric and varchar2 fields may contain blank fields in the upload file only difference being varchar2 value will have |''| while mumeric will be simply | |.
I have one solution to this as follows:
In case a field is defined as varchar in table and expected to contain only numeric values then,
column1 NULLIF=BLANKS col=(to_char(to_number(:column1))
Will it handle blank data both char and number type and replace it with NULL in the table.
|
|
|
|
| Re: Handling Nulls in Sql Loader [message #129491 is a reply to message #129284] |
Mon, 25 July 2005 08:09  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'd have to write up a test to verify, but according to the docs, if you use a sqlldr datatype of char it will default to null. Do you alrady have an example you could post?
| Quote: |
Numeric EXTERNAL
The numeric EXTERNAL datatypes are the numeric datatypes (INTEGER, FLOAT, DECIMAL, and ZONED) specified as EXTERNAL, with optional length and delimiter specifications. The length is in bytes unless character-length semantics are used for the datafile. In that case, the length is in characters. See Character-Length Semantics.
These datatypes are the human-readable, character form of numeric data. The same rules that apply to CHAR data with regard to length, position, and delimiters apply to numeric EXTERNAL data. See CHAR for a complete description of these rules.
The syntax for the numeric EXTERNAL datatypes is shown as part of datatype_spec.
Note:
The data is a number in character form, not binary representation. Therefore, these datatypes are identical to CHAR and are treated identically, except for the use of DEFAULTIF. If you want the default to be null, use CHAR; if you want it to be zero, use EXTERNAL. See Using the WHEN, NULLIF, and DEFAULTIF Clauses.
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
