The Oracle (tm) Users' Co-Operative FAQ

Using SQL*Loader to process sequential files


Author's name: Michael Bialik

Author's Email: bialik@isdn.net.il

Date written: 30-Jan-2002

Oracle version(s): 8.1.7.0.0 EE

Prior to Oracle 8i we have to write Pro*C/Cobol program or to use UTL_FILE package to process sequential files, but now we can do it much simplier and faster with SQL*Loader and instead of triggers.

Back to index of questions


Oracle 8i EE supplies us with another possibility for using SQL*Loader utility: processing sequential files to update or delete data. The limitation is that direct option cannot be used, however in my opinion that method is much more convenient than writing Pro*C programs or using utl_file package to process such information.

In the following example we’ll use table emp in schema scott to demonstrate that method.

1. Define a view as select from table you intent to update.

CREATE VIEW EMP_V
   (OP_TYPE, EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) AS
   SELECT ‘ ‘ op_type, empno, ename, job, mgr, hiredate, sal, comm, deptno
   FROM EMP

The OP_TYPE column is a virtual column that we'll use to specify the type of DML operation (INSERT, UPDATE or DELETE).

2. Define an INSTEAD OF INSERT trigger to issue DML statements. (Such triggers may be defined only with Enterprise Edition of Oracle 8i.)

CREATE OR REPLACE emp_v_trg
   INSTEAD OF INSERT ON emp_v
   DECLARE
     ot VARCHAR2(1) := NULL;
   BEGIN
     ot := UPPER(:NEW.op_type);
     IF ot = 'D' THEN
       DELETE FROM EMP WHERE empno = :NEW.empno;
     ELSIF ot = 'U' THEN
       UPDATE EMP SET
         ename = :NEW.ename, job = :NEW.job,
         mgr = :NEW.mgr, hiredate = :NEW.hiredate,
         sal = :NEW.sal, comm = :NEW.comm,
         deptno = :NEW.deptno
       WHERE empno = :NEW.empno;
     ELSIF ot = 'I' THEN
       INSERT INTO EMP (empno,ename,job,mgr,hiredate,sal,comm,deptno)
       VALUES (:NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr,
         :NEW.hiredate, :NEW.sal, :NEW.comm, :NEW.deptno);
     ELSE
       -- Deal with incorrect operation type
       NULL;
     END IF;
   END emp_v_trg;

3. Prepare control file (together with data sample) for SQL*Loader.

LOAD DATA
INFILE *
APPEND
INTO TABLE emp_v (
  op_type  POSITION(01:01) CHAR,
  empno    POSITION(02:05) INTEGER EXTERNAL,
  ename    POSITION(06:15) CHAR,
  job         POSITION(16:24) CHAR,
  mgr       POSITION(25:28) INTEGER EXTERNAL,
  hiredate  POSITION(29:36) CHAR "TO_DATE(:hiredate,'YYYYMMDD')",
  sal         POSITION(37:44) DECIMAL EXTERNAL,
  comm    POSITION(45:52) DECIMAL EXTERNAL,
  deptno    POSITION(53:56) INTEGER EXTERNAL
)
BEGINDATA
I0001John SmithClerc 73692001123100001000000003000010
I0021Jane SmithClerc 00012000112000000200000000100010
D0020

Use SQL*Loader to process sequential file and to insert data into the view. The trigger will take care of the rest.

sqlldr scott/tiger@orc0 conrol=emp_v_load.ctl log=emp_v_load.log

After executing SQL*Loader job we must look at log file and verify successfull completion of the job.

SQL*Loader: Release 8.1.7.0.0 - Production on Mon Jan 28 11:28:36 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.

Control File: emp_v_load.ctl
Data File: emp_v_load.ctl
Bad File: emp_v_load.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table EMP_V, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ----- ----- ---------------------
OP_TYPE 1:1 1     CHARACTER
EMPNO 2:5 4     CHARACTER
ENAME 6:15 10     CHARACTER
JOB 16:24 9     CHARACTER
MGR 25:28 4     CHARACTER
HIREDATE 29:36 8     CHARACTER

    SQL string for column : "TO_DATE(:hiredate,'YYYYMMDD')"

SAL      37:44       8                         CHARACTER
COMM      45:52       8                         CHARACTER
DEPTNO                              53:56       4                         CHARACTER



Table EMP_V:
 3 Rows successfully loaded.
 0 Rows not loaded due to data errors.
 0 Rows not loaded because all WHEN clauses were failed.
 0 Rows not loaded because all fields were null.


Space allocated for bind array: 4864 bytes(64 rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped:       0
Total logical records read:           3
Total logical records rejected:      0
Total logical records discarded:    0

Run began on Mon Jan 28 11:28:36 2002
Run ended on Mon Jan 28 11:28:37 2002

Elapsed time was:    00:00:00.74
CPU time was:        00:00:00.14

The "real life" scenario is much more complicated, because a number of cases must be dealt with:

Oracle 9i introduces another possibility to process sequential files - external tables, but dealing with it is out of scope of that article.


Further reading:
  Oracle Metalink : Doc.Id 116237.1 - How to Use SQL*Loader to delete rows in table
  Oracle Metalink : Doc.Id 109144.1 - How to Update data with SQL*Loader


Back to top

Back to index of questions