sqlldr : How to upload records where all columns are null

From: toto <parag9i_at_gmail.com>
Date: 27 Mar 2006 21:25:02 -0800
Message-ID: <1143523502.624434.245390@u72g2000cwu.googlegroups.com>

Hi,

I am having following table

09:48:42 SQL> desc t

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 A                                                  CHAR(1)
 B                                                  NUMBER

I am having following contorlfile t.ctl to upload records

LOAD DATA
INFILE *
INTO TABLE T
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(

a nullif a="(null)"
,b nullif b="(null)"
)
BEGINDATA
A,2
(null),(null)
(null),(null)
(null),9

B,(null)
(null),10
(null),(null)

C,1
(null),(null)
(null),(null)
(null),(null)

When I try to do upload using sqlldr sqlldr xxxx/yyyy control=t.ctl log=t.log I am getting following error

Table T, loaded from every logical record. Insert option in effect for this table: REPLACE TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
A                                   FIRST     *   ,       CHARACTER
    NULL if A = 0X286e756c6c29(character '(null)')
B                                    NEXT     *   ,       CHARACTER

    NULL if B = 0X286e756c6c29(character '(null)')

Record 2: Discarded - all columns null.
Record 3: Discarded - all columns null.
Record 7: Discarded - all columns null.
Record 9: Discarded - all columns null.

Record 10: Discarded - all columns null. Record 11: Discarded - all columns null.

Table T:
  5 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.

I want to all insert all records where all fields are null. How can I do this ?

thanks & regards
pjp Received on Mon Mar 27 2006 - 23:25:02 CST