Oracle SQL Loader control file to ignore ellipsis

Question

I have an Oracle SQL Loader control file based on position in a text file. One particular field periodically gets an ellipsis '...' from the source, which causes a carriage return in the loading table. No matter how many times I request '...' to NOT be used by these users, there is eventually someone who forgets, or due to staff turnover, etc. Here is the current control file line for that field:

TRAN_DESC   POSITION(153 : 202)  Char,

Is there any command that can be added to this line in order to ignore special characters such as an ellipsis?

Answer 1

I'd think of REPLACE. Here's an example.

Sample table:

SQL> create table test (id number, tran_desc varchar2(10));

Table created.

Control file:

load data
infile *
into table test
(id        position(1:2), 
 tran_desc position(3:12) char "replace(:tran_desc, '...', '')"
)

begindata
10LittleFoot
11Big...foot

Loading session and result:

SQL> $sqlldr scott/tiger control=test2.ctl log=test2.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pon Tra 5 17:03:39 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2

SQL> select * from test;

        ID TRAN_DESC
---------- ----------
        10 LittleFoot
        11 Bigfoot

SQL>