Re: Sqlloader

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: 4 Apr 2004 18:04:24 -0700
Message-ID: <cf15dee4.0404041704.22fa3348@posting.google.com>

riky_rang_at_hotmail.com (Rich) wrote in message news:<724cbb19.0404040155.145115c9_at_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

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 Received on Sun Apr 04 2004 - 20:04:24 CDT