Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.glorb.com!postnews1.google.com!not-for-mail
From: douglashawthorne@yahoo.com.au (Douglas Hawthorne)
Newsgroups: comp.databases.oracle.misc
Subject: Re: Sqlloader
Date: 4 Apr 2004 18:04:24 -0700
Organization: http://groups.google.com
Lines: 86
Message-ID: <cf15dee4.0404041704.22fa3348@posting.google.com>
References: <724cbb19.0404040155.145115c9@posting.google.com>
NNTP-Posting-Host: 138.130.85.169
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1081127064 1199 127.0.0.1 (5 Apr 2004 01:04:24 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 5 Apr 2004 01:04:24 +0000 (UTC)
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:137407

riky_rang@hotmail.com (Rich) wrote in message news:<724cbb19.0404040155.145115c9@posting.google.com>...
> Hi
> 
> I wish to load a table with sqlloader with data from a record that
> does not match the table record.  The target table has three fields
> but the pipe demlimited data file has 8 fields.  Is there a way to
> specify what field is inserted into the target field as with a fixed
> length sqlloader file?
> 
> To make things more complicated the third field in the target table
> needs to be populated with the sysdate.
> 
> Any ideas would be appriectaed, thanks
> 
> Rich.

Rich,

There are two things you would need to get a solution:
(1) Use the FILLER keyword to skip input fields;
(2) Use SQL expressions to generate the required data.

Daniel Morgan has some examples on SQL*Loader at
http://www.psoug.org/reference/sqlloader.html which you may wish to
examine.

Thomas Kyte has written a chapter on Data Loading in his book, "Expert
One-on-One Oracle" (A-Press:2003).  I got the ideas for a solution to
this problem from that chapter.

My test case went as follows:
(1) Creation of test table:
CREATE
   TABLE field_3
      (
      first_column     VARCHAR2( 40 ),
      second_column    DATE,
      third_column     VARCHAR2( 40 )
      )
;

(2) Creation of SQL*Loader control file (fld8.ctl):
LOAD DATA
   INFILE *
   INTO TABLE field_3
   FIELDS TERMINATED BY '|'
   (
      ignore_1       FILLER,
      ignore_2       FILLER,
      first_column   CHAR,
      second_column  "SYSDATE",
      ignore_3       FILLER,
      ignore_4       FILLER,
      third_column   CHAR,
      ignore_5       FILLER
   )
BEGINDATA
ignore this|and this|data col #1|ignore this|and this|and this|data
col #3|ignore this

For this test, I used inline data to be loaded into an empty table. 
In this example, I am loading the third and seventh fields from the
data record, and using an SQL Expression ("SYSDATE") to put the
current value of SYSDATE into the SECOND_COLUMN column.

Although you specified that you have a fixed format data file, I have
chosen the delimited specification because I could not be bothered
with counting up characters.

The FILLER keyword here tells SQL*Loader not to load that data into
the table.

(3) I use sqlldr with control=fld8.ctl

(4) And the result is:
SQL> exec tkyte.print_table( 'SELECT * FROM field_3' )
FIRST_COLUMN                  : data col #1
SECOND_COLUMN                 : 05-APR-04
THIRD_COLUMN                  : data col #3
-----------------

PL/SQL procedure successfully completed.

This was tested under 9.2.0.1 on WinXP Pro.

Douglas Hawthorne