Re: SQL*Loader Question

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/18
Message-ID: <34ea9083.1659123@www.sigov.si>#1/1

On Wed, 18 Feb 1998 01:28:36 GMT, kmacs_at_gandalf.kmacs.com (Keith E. Moore) wrote:

>On Tue, 17 Feb 1998 23:51:07 GMT, Alan Cannon <acannon_at_webzone.net> wrote:
>>Does anyone know how to (or if it's possible at all) use SQL*Loader to load
>>a delimited ASCII file into a table without loading all the fields from the
>>input file. It seems like this should be possible to me.
>>
>>
>
>We just had a similar problem and were unable to find an option for this,
>we ended up using an awk script as a filter:
>
> ....[SNIPPED SCRIPT].....
>
>If someone does know how to have sqlldr skip a field, I would like
>to know.
>
>Hope this helps
>--
>-- Keith Moore
> President
> KMA Computer Solutions, Inc.

About half a year ago we have thread about the same topic in this newsgroup. My proposed sollution (wich have some limitations) was to use a view on a target table:

[quote from one of my previous posts]
You must create a view, based on your target table. This view must have one additional column, selected additionaly from the same table.

Example:
SQL> desc emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                                    NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9) 

SQL> CREATE VIEW view_emp AS
  2 (SELECT empno, ename, job, ename AS dummy FROM emp);

View created.

SQL> DESC view_emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                                    NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 DUMMY                                    VARCHAR2(10)

The following control file will insert records into the table emp through the view, leaving out the second (=unwanted) field from the text data.

  LOAD DATA INFILE *
  INSERT INTO TABLE view_emp
  FIELDS TERMINATED BY ','

        (empno, dummy, ename, job)
  BEGINDATA        
  1,skip me1,SMITH,CLERK

  2,skip me 2,SCOTT,MANAGER
  3,me too!,TEETS,PRESIDENT

The result after loading with loader:

SQL> SELECT * FROM EMP;
    EMPNO ENAME JOB
--------- ---------- ---------

        1 SMITH      CLERK
        2 SCOTT      MANAGER
        3 TEETS      PRESIDENT

3 rows selected.
[/quote from one of my previous posts]

But Thomas Kyte has proposed a more ellegant and universal sollution, which uses PL/SQL packaged function in SQL*Loader's controll file as "SQL strings". The only (possible) drawback with this sollution could be that it can't be used with direct load option. Email me if you can't find his sollution on the news archives.

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 Wed Feb 18 1998 - 00:00:00 CST