Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Ian.

Asked: July 13, 2020 - 5:09 pm UTC

Answered by: Chris Saxon - Last updated: July 14, 2020 - 3:09 pm UTC

Category: SQL - Version: 10g

Viewed 100+ times

You Asked

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



and we said...

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 

and you rated our response

  (1 rating)

Reviews

Thanks chris

July 14, 2020 - 3:43 pm UTC

Reviewer: Ian cleary from Uk

Thanks chris .. much appreciated.. I didn't realise the optionally enclosed by worked liked that..

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.