Hi
I have a problem using external tables. When a field is not populated, it is being ignored and the value is being held in previous column. An example of the sort of SQL I am using is
CREATE TABLE "EXT_TABLE_TEST"
("COLUMN1" VARCHAR2(100 BYTE),
"COLUMN2" VARCHAR2(100 BYTE),
"COLUMN3" VARCHAR2(100 BYTE),
"COLUMN4" VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "DIR"
ACCESS PARAMETERS
( records delimited by newline
BADFILE 'Test.bad'
LOGFILE 'Test.log'
fields terminated by 0x'09'
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION
( "DIR":'Test.txt'
)
)
REJECT LIMIT UNLIMITED;If for example I have a file containing
a b c d
a c d
a b d
At the moment the data is displaying in the query as
a b c d
a c d
a b d
As you can see the data in the 2nd and 3rd rows are not showing the data in the correct columns.
If I change the source file as comma separated it seems to enforce the correct column ordering but tab delimited does not.
Any ideas whether there is a fix for this?
Cheers Ian
The problem comes from this:
OPTIONALLY ENCLOSED BY '"'
When you have this clause and fields without enclosures (double quotes), the database trims any leading whitespace. So when the source has two tabs next to eachother, the second becomes part of (ignored) leading whitespace for the next field.
To overcome this, exclude this clause:
CREATE TABLE "EXT_TABLE_TEST" (
"COLUMN1" VARCHAR2(100 BYTE),
"COLUMN2" VARCHAR2(100 BYTE),
"COLUMN3" VARCHAR2(100 BYTE),
"COLUMN4" VARCHAR2(100 BYTE)
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY tmp
ACCESS PARAMETERS
( records delimited by newline
BADFILE 'Test.bad'
LOGFILE 'Test.log'
fields terminated by 0x'09'
MISSING FIELD VALUES ARE NULL
)
LOCATION
( tmp:'test.txt' )
)
REJECT LIMIT UNLIMITED;
declare
f utl_file.file_type;
begin
f := utl_file.fopen ('TMP', 'test.txt', 'w');
utl_file.put_line(f, 'a' || chr ( 9 ) || 'b' || chr ( 9 ) || 'c' || chr ( 9 ) || 'd');
utl_file.put_line(f, 'a' || chr ( 9 ) || chr ( 9 ) || 'c' || chr ( 9 ) || 'd');
utl_file.put_line(f, 'a' || chr ( 9 ) || 'b' || chr ( 9 ) || chr ( 9 ) || 'd');
utl_file.fclose(f);
end;
/
select *
from EXT_TABLE_TEST;
COLUMN1 COLUMN2 COLUMN3 COLUMN4
a b c d
a <null> c d
a b <null> d