Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

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.



0
senico
Asked:
senico
  • 3
  • 2
1 Solution
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now