3

I am seeing a strange problem when loading my data with sqlldr. Here is my table schema:

CREATE TABLE TEST(
   "COL1" VARCHAR2 (255 BYTE),
   "COL2" VARCHAR2 (255 BYTE),
   "COL3" NUMBER,
   "COL4" VARCHAR2 (255 BYTE)

and here is just one row of data I am trying to ingest from the tab delimited file test.txt:

COL1    COL2    COL3    COL4
        10  17-cc

notice that the first two columns are empty (null). So my row is really:

\t\t10\t17-cc

my loader script:

load data
 infile 'test.txt'
 append into table TEST
 fields terminated by "\t" optionally enclosed by '"'
 TRAILING NULLCOLS  
(COL1,COL2,COL3,COL4)

This will be loaded into my table as:

COL1 COL2  COL3   COL4
10    17-CC (null) (null)

which is incorrect. it seems that the two leading tabs in the data row were ignored and COL3 position (10) was assigned to COL1. However, if I try to import the data as a comma separated file:

COL1,COL2,COL3,COL4
,,10,17-cc

it works as expected. Why does the tab delimited version fails here?

dmornad
  • 141
  • 1
  • 11

1 Answers1

-1

NOTE - Fixed my original wrong answer.

Your TAB is defined just fine. You need the NULLIF statement:

load data
 infile 'test.txt'
 append into table TEST
 fields terminated by "\t" optionally enclosed by '"'
 TRAILING NULLCOLS  
(COL1 NULLIF(COL1=BLANKS),
 COL2 NULLIF(COL2=BLANKS),
 COL3 NULLIF(COL3=BLANKS),
 COL4 NULLIF(COL4=BLANKS)
)
Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • Not sure what you are suggesting. Can you elaborate or maybe show how my row data should look like. This is an excel generated tab delimited file. Besides, if I change my row to : "55 66 10 17-CC" the tab delimited row gets ingested with no problem. It's the leading two tabs for empty columns COL1 and COL2 in my original data row that are causing the problem – dmornad Mar 21 '19 at 16:33
  • That is not the problem. If you look at my original post, you will see that my loader command already has what you are suggesting. I even replaced \t with x'09' as your suggestion, but same result. Again, the problem is with cells containing NULL values. The loader does not skip over those columns as it should. – dmornad Mar 21 '19 at 17:05
  • Sorry, my first answer was wrong. Please see the edit above. – Gary_W Mar 21 '19 at 17:05
  • NULLIF() statement does not fix it. I'm still getting the same result. Please note that the comma separated version of the same input file works without the NULLIF() statements, so the problem must be somewhere else. – dmornad Mar 21 '19 at 17:15
  • I wonder if you have to include double-quotes around the column names since you created the table with them? That means they are case-sensitive in Oracle. Best to avoid that. Please drop the table and recreate without double-quotes around the column names and try again. – Gary_W Mar 21 '19 at 17:18
  • Removed the double-quotes in the TABLE CREATE but the issue persists. – dmornad Mar 21 '19 at 17:24
  • Open your text file in an editor that will let you view the text in HEX (I use Gvim/tools/convert to HEX) and ensure the characters you are looking at are really what you think they are. I believe we have established the control file is ok. – Gary_W Mar 21 '19 at 17:42
  • 2
    I figured out how to get it to work. I had to remove the following control from my sqlldr command: optionally enclosed by '"' . Not sure why that caused the issue but after removing it logic behaves as expected. – dmornad Mar 21 '19 at 18:05