RE: SQL*Loader - Please Help!!!

Checkout the nullif keyword. This excerpt comes from Chapter 5 (SQL*Loader Control File reference) of the Oracle Utilities Manual (8.1.5).

Nancy

• Begin Excerpt ***** NULLIF Keyword Use the NULLIF keyword after the datatype and optional delimiter specification, followed by a condition. The condition has the same format as that specified for a WHEN clause. The column's value is set to null if the condition is true. Otherwise, the value remains unchanged.

NULLIF field_condition

The NULLIF clause may refer to the column that contains it, as in the following example:

COLUMN1 POSITION(11:17) CHAR NULLIF (COLUMN1 = "unknown")

This specification may be useful if you want certain data values to be replaced by nulls. The value for a column is first determined from the datafile. It is then set to null just before the insert takes place. Case 6: Loading Using the Direct Path Load Method provides examples of the NULLIF clause.

Note: The same effect can be achieved with the SQL string and the NVL function. See Applying SQL Operators to Fields.

• End Excerpt *****

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of WIlliam
> Thater
> Sent: Tuesday, December 19, 2000 9:40 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: SQL*Loader - Please Help!!!
>
>
> "Smith, Ron L." wrote:
> >
> > I am trying to run SQL*Loader on 8.1.6 NT. One column in the table is a
> > date format. In the SQL*Loader control file I defined the
> column as "DATE".
> > However, the input file is blank in the date column. The
> column can be null
> > according to the table structure. When I try to load the data I get an
> > error message that says there is an invalid value for month in the date
> > column. There is no format associated with the column and
> again, the date
> > column is blank.
> >
> > Ron Smith
> > Database Administration
> > rlsmith_at_kmg.com
>
> but a blank is not a null. if it's a blank the it's looking for it to
> fit the default date mask. i think there's a way to insert a null if
> the column i s blank, but i haven't used loader in a long time and the
> memory is subject to bit rot. sorry.
>
>
> --
> Bill Thater Sr. ORACLE DBA
> Telergy, Inc thaterw_at_telergy.net
> -------------------------------------------
> "Only two things are infinite, the universe and human stupidity,
> and I'm not sure about the former." -- Albert Einstein
Received on Tue Dec 19 2000 - 10:12:11 CST