cancel
Showing results for 
Search instead for 
Did you mean: 

sql*loader question

Nicolas Dumeige
Esteemed Contributor

sql*loader question

Hi,

We're using sql*loader to load a file with 13 fileds delimited whit '|'.

We want to load all record that have the last field equal NULL, non caracter at all.
Problem is, there is no such keyword when using a WHEN CLAUSE.


LOAD DATA
REPLACE
INTO TABLE INTERFACE_FRONT_ECO_PREV
WHEN (1:7) != 'DEFFILE'
AND (1:7) != 'FTRFILE'
AND (1:6) != 'DEFLOT'
AND (1:6) != 'FTRLOT'
****** HERE ************
AND (13) = BLANKS
AND (13) = NULL
************************
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
IDENTIFIANT CHAR,
PERIOD DATE(8) "DDMMRRRR",
TYPE_PRESTATION CHAR,
DEVISE CHAR,
CODE_SEG CHAR,
CODE_STAT CHAR,
CODE_DEPARTEMENT CHAR,
SECT_GEO CHAR,
TYPE_CLIENT CHAR,
CANAL_RESA CHAR,
SOURCE_RESA CHAR,
VALEUR DECIMAL EXTERNAL,
SOURCE CONSTANT BOCOSAEXPBFRIJ09030625162920.TXT
)


Does anybody can provide some help,
thanks in advance,
All different, all Unix
3 REPLIES
Pete Randall
Outstanding Contributor

Re: sql*loader question

Yogeeraj_1
Honored Contributor

Re: sql*loader question

hi,

Are you using Oracle 8i or 9i?

In 9iR1 and up with external tables you have the ability to query a flat file using SQL. I find the flexibility of SQL over SQLLDR control files to be so useful that I would opt for an external table over sqlldr in most all cases. Use SQLLDR when SQL cannot be used to accomplish the job.

e.g.
create or replace directory data_dir as '/tmp/'

create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by '|' )
location ('emp.dat')
)
/

In Oracle8i and SQLLoader:

Consider the following example:

My control file:
=====================================
LOAD DATA
INFILE *
INTO TABLE dept
append
FIELDS TERMINATED BY '|'
(
deptno position(001:002),
dname position(004:013),
loc position(014:023) "nvl(:loc,'XX')",
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH |DALLAS
30|SALES |RESTON
40|OPERATIONS|
=====================================

Just load the data, the last column having null value will be substituted by 'XX'.

$ sqlldr userid=yd/yd@dev control=yd.ctl log=mlog


SQL> select * from dept;

DEPTNO DNAME LOC BUILDING
---------- ------------------------------ ------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES RESTON
40 OPERATIONS XX




After that, i can just do a delete where loc = 'XX'

delete dept where loc=''XX';


This is the easiest way (brute force).

Will need to investigate for other possibilities.

Hope this helps
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: sql*loader question

sorry! did not notice that this problem was already solved ;)

cheers
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)