Hi, I'm a bit stuck, hopefully someone can help. I've searched far and wide, and this seems to be a 'feature' of the database going back nearly 20 years...
The problem: I am trying to create an external table on a tab delimited file, which may contain null values in some columns (i.e. a double-tab). Every "solution" I've found to this involves removing "optionally enclosed by.." from the access parameters. Sadly for me I wasn't using that at all, and it still fails.
Database:19.0.0.0.0
Test table:
CREATE TABLE extern_my_tsv
(
col1 VARCHAR2(255),
col2 VARCHAR2(255),
col3 VARCHAR2(255),
col4 VARCHAR2(255)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY "DB_IMPORT"
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
SKIP 1
FIELDS TERMINATED BY '\\t'
LRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
"DB\_IMPORT":'my\_tsv.tsv'
)
)
Test file, 5 rows, first row is the four column headings, EOL char is unix format (LF), 3rd row has a missing value for column 2 (two tabs).
It looks like this in notepad++ with whitespace enabled:
When selected from the external table, the 2nd row of data exhibits the usual problem of being shifted left so the 'null' appears in col4 rather than in col2. It seems like such a simple thing, hopefully there's something simple I can do to fix it?
Cheers!
P.S. the files come from a 3rd party so I have no control over the format of the original file.