Oracle SQl Loader - External Table

This is my code:-

CREATE TABLE GLO_CUST_EXT
(SOURCE_ID CHAR(3),
 BUS_DT CHAR(8),
 TRAN_CD CHAR(1))
 CUST_CD NUMBER(10),
 MNEMONIC CHAR(10),
 SHORT_NM CHAR(255)
ORGANIZATION external
(TYPE oracle_loader
  DEFAULT DIRECTORY Source_dir
   ACCESS PARAMETERS
   (RECORDS DELIMITED BY NEWLINE Skip 1
   badfile bad_dir:'GLO_CUST%a_%p.bad'
   LOGFILE log_dir:'GLO_CUST%a_%p.log'
   FIELDS TERMINATED BY '|'
   (SOURCE_ID ,
    BUS_DT ,
    TRAN_Cd ,
    CUST_CD,
    MNEMONIC,
    SHORT_NM)
LOCATION ('GBCUS.txt'))
REJECT LIMIT 0;

Question:
Would like how to load the data by selected field?
Example:

I want to all these fields (SOURCE_ID , BUS_DT , TRAN_CD ,
 CUST_CD, SHORT_NM) except the MNEMONIC.



LVL 1
senicoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
select SOURCE_ID , BUS_DT , TRAN_CD , CUST_CD, SHORT_NM
FROM GLO_CUST_EXT
0
senicoAuthor Commented:
I think my question is how to load the data into external table?

By using the code below:-

CREATE TABLE GLO_CUST_EXT
(SOURCE_ID CHAR(3),
 BUS_DT CHAR(8),
 TRAN_CD CHAR(1))
 CUST_CD NUMBER(10),
 MNEMONIC CHAR(10),
 SHORT_NM CHAR(255)
ORGANIZATION external
(TYPE oracle_loader
  DEFAULT DIRECTORY Source_dir
   ACCESS PARAMETERS
   (RECORDS DELIMITED BY NEWLINE Skip 1
   badfile bad_dir:'GLO_CUST%a_%p.bad'
   LOGFILE log_dir:'GLO_CUST%a_%p.log'
   FIELDS TERMINATED BY '|'
   (SOURCE_ID ,
    BUS_DT ,
    TRAN_Cd ,
    CUST_CD,
    MNEMONIC,
    SHORT_NM)
LOCATION ('GBCUS.txt'))
REJECT LIMIT 0;
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I think my question is how to load the data into external table?
you cannot insert into an external table with the EXTERNAL table.

0
senicoAuthor Commented:
>I think my question is how to load the data into external table?
you cannot insert into an external table with the EXTERNAL table.<<< correct
<<<<Acctually, what i want to know is how to create external table and at the same time try to skip loading one column. See the example below:

CREATE TABLE GLO_CUST_EXT
(SOURCE_ID CHAR(3),
 BUS_DT CHAR(8),
 TRAN_CD CHAR(1))
 CUST_CD NUMBER(10),
 MNEMONIC CHAR(10),
 SHORT_NM CHAR(255)
ORGANIZATION external
(TYPE oracle_loader
  DEFAULT DIRECTORY Source_dir
   ACCESS PARAMETERS
   (RECORDS DELIMITED BY NEWLINE Skip 1
   badfile bad_dir:'GLO_CUST%a_%p.bad'
   LOGFILE log_dir:'GLO_CUST%a_%p.log'
   FIELDS TERMINATED BY '|'
   (SOURCE_ID ,
    BUS_DT ,
    TRAN_Cd ,
    CUST_CD,
    MNEMONIC,
    SHORT_NM)
LOCATION ('GBCUS.txt'))
REJECT LIMIT 0

I declare 6 columns   (SOURCE_ID , BUS_DT ,  TRAN_Cd , CUST_CD, MNEMONIC,    SHORT_NM), but i want to skip MENMONIC column from loading. The reason is because  size for MENMONIC column expanding until more than 4000 byte. Thank you.  
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please see this q how to unload data from a oracle table to a flat file:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:68212348056
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.