Re: sqlloader Question

sarek_at_cais.com (Scott T. Johnson) wrote:
>
> You cannot extract certain columns from the data file. All columns must be
> loaded somewhere. Best thing to do is either load a temp table then drop it
> or load a temp table then create the actual tables with the column you would
> like.
>
>
> Scott

You can extract certain columns from any data file and load one or more tables from one input record. For example (from ulcase5.ctl found in $ORACLE_HOME/rdbms/demo). Only the specifed column positions ever get loaded.

LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dis'
REPLACE   INTO TABLE EMP

  (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
   ENAME    POSITION(6:15)  CHAR,
   DEPTNO   POSITION(17:18) CHAR,
   MGR      POSITION(20:23) INTEGER EXTERNAL)
 

INTO TABLE PROJ
-- PROJ has two columns, both not null: EMPNO and PROJNO WHEN PROJNO != ' '
  (EMPNO POSITION(1:4) INTEGER EXTERNAL,    PROJNO POSITION(25:27) INTEGER EXTERNAL) -- 1st proj  

INTO TABLE PROJ
WHEN PROJNO != ' '
  (EMPNO POSITION(1:4) INTEGER EXTERNAL,    PROJNO POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj  

This ctl file loads some of the columns into EMP (not all) and some of the columns into PROJ. In fact, it shows how to take an in-record dimension (PROJECTS in this example) and create multiple input records from ONE record.

This example is also found in the Server Utilities Guide

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

>
>
> In article <3pcvbl$mo4_at_cnn.MOTOWN.GE.COM> ttroccol_at_motown.ge.com (Ted M.
> Troccola X4721) writes:>From: ttroccol_at_motown.ge.com (Ted M. Troccola X4721)
> >Subject: sqlloader Question
> >Date: 17 May 1995 13:57:09 GMT
>
>
> >I am using sqlloader to get data from a data file.
> >The data file has like 10 columns in it.
> >I am loading four tables which use some, but NEVER all, of the
> >columns in the datafile.
 

> >So, I have to extract only these certain columns for certain tables.
 

> >Can this be done?
 

> >Ex : A general idea...
 

> > Load TABLE(emp, salary, age)
> > with (column_1, column_3, column_7)
> > from 'MY_10_COLUMN_DATA_FILE'
 

> >Please email any responses
 

> >Thanks in advance,
> >Ted
>
Received on Fri May 19 1995 - 00:00:00 CEST