title: Oracle External Table Columns based on header row or star(*)
body: <p>I have a text file with around 100 columns terminated by <code>"|"</code>. And i need to get few of the columns from this file into by External Table. So the solution i have is either specify all columns under <code>ACCESS PARAMETERS</code> section in the same order as the file. and define required columns in the Create table definition. Or define all columns in the same order in the create table itself. </p>
  
  <p>Can i avoid defining all the columns in the query? Is it possible to get the columns based on the first row name itself - Provided i have the column names as the first row.</p>
  
  <p>Or is it atleast possible to get all columns like a (<code>select *</code> ) without mentioning each column?
  Below is the code i use</p>
  
  <pre><code>drop table lz_purchase_data;
  CREATE TABLE lz_purchase_data  
  (REC_ID CHAR(50))
             ORGANIZATION EXTERNAL
              ( TYPE ORACLE_LOADER
                DEFAULT DIRECTORY "FILEZONE"
                ACCESS PARAMETERS
                ( RECORDS DELIMITED BY NEWLINE Skip 1
                  FIELDS  TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
                          LRTRIM MISSING FIELD VALUES ARE NULL
                ) LOCATION( 'PURCHASE_DATA.txt' ))
             REJECT LIMIT UNLIMITED
          PARALLEL 2 ;
  
          select * from LZ_PURCHASE_DATA;
  </code></pre>