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. |
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
SQL string for column :
"TO_DATE(:hiredate,'YYYYMMDD')"
|
The "real life" scenario is much more complicated, because a number of cases must be dealt with:
- Do we allow for UPDATE/DELETE of rows in a table without primary key (or unique index)?
- What happens when a number of column values are missing in an input file for UPDATE? Do we need to update missing values to NULL or to leave these values unchanged?
- Are additional values (except primary key or unique index columns) supplied for DELETE? If the answer is positive then do we need to use these values for verification or just ignore them?
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
