Re: Messy SQLLDR question

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Sun, 24 May 1998 20:41:33 GMT
Message-ID: <356885f9.17049275_at_www.sigov.si>

On Sun, 24 May 1998 09:21:27 -0600, "Roger Loeb" <rloeb_at_martech.com> wrote:

>I need to load a table from a comma-delimited file using SQLLDR. However,
>the input file consists of data that must load into separate rows in the
>table, e.g.,
>
>ID,TYPE,val1,val2,val3,val4
>
>where ID is a numeric identifier, type is a sub-identifier, and the valx
>statements are numeric values.
>
>The table looks like
>
>create table SUMTABLE
>(
>ID NUMERIC,
>TYPE NUMERIC,
>VALUE NUMERIC);
>
>I understand that in SQLLDR this is going to take 4 separate statements of
>the form
>
>load data
>into table SUMTABLE Fields terminated by ','
>(ID,TYPE,VALUE)
>into table SUMTABLE Fields terminated by ','
>(ID POSITION (1),TYPE,VALUE POSITION (???))
>etc.
>
>What I can't figure out is how to tell SQLLDR to use the relative field,
>rather than a fixed column position, in the second "into table" statement.
>If I use POSITION (3), then it loads data starting at the third byte of the
>data record, rather than the third relative field.
>
>Any ideas??? I'm sure that I'm overlooking the obvious.

Several possibilities here.

1. Most obvious (but perhaps not the most space-and-time efficient) is to create the intermediate table with all 6 columns (ID, TYPE, val1, val2, val3, val4), load the data with simple SQL*Loader ctl file and finaly use a simple SQL statement or PL/SQL procedure to selectively load into your final SUMTABLE.
2. Much more efficient way (I belive you can use this method even with direct option of SQL*Loader) is to create three views:

CREATE VIEW v_sumtable2 AS
  SELECT id, type, value AS dummy1, value   FROM sumtable;

CREATE VIEW v_sumtable3 AS
  SELECT id, type, value AS dummy1, value AS dummy2, value   FROM sumtable;

CREATE VIEW v_sumtable4 AS
  SELECT id, type, value AS dummy1, value AS dummy2,

         value AS dummy3, value
  FROM sumtable;

Now you can insert into your SUMTABLE through this three views when you need to skip some of the input data fields. An example of your ctl file would be something like:

LOAD DATA INFILE ......
INTO TABLE sumtable FIELDS TERMINATED BY ',' (id, type, value)
INTO TABLE v_sumtable2 FIELDS TERMINATED BY ',' (id, type, dummy1, value)
INTO TABLE v_sumtable3 FIELDS TERMINATED BY ',' (id, type, dummy1, dummy2, value)
INTO TABLE v_sumtable4 FIELDS TERMINATED BY ',' (id, type, dummy1, dummy2, dummy3, value)

c) The third possibility is to create a PL/SQL package that allows you to skip the fields from the input file and use it as a "string function" in your ctl file. It was posted and explained a couple of times in comp.databases.oracle* newsgroups by Thomas Kyte, so go to the Deja News and look for "tkyte" as an author and "SQL Loader" or something simmilar as subject. His method is the most general and universal sullution for this kind of problems, the only drawback is it can't be used with direct path loading.

>Thanks,
>
>Rog

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Sun May 24 1998 - 22:41:33 CEST