Sql ldr control file

Question

My loader script is failing due to ORA-01722 for field PATHID which is defined as integer external in the control file. Now i want to check if the column pathid of file is containing number value or not and then insert as null if it is not number. My database is oracle.

Answer 1

Here's an example; see if it helps.

Test table first, and a function which checks whether a parameter value is a number or not. It'll be used in the control file.

SQL> create table test (pathid number, name varchar2(20));

Table created.

SQL> create or replace function f_isnum (par_value in varchar2)
  2    return varchar2
  3  is
  4    -- if PAR_VALUE is a number, return Y. Otherwise, return N
  5  begin
  6    return case when regexp_like(par_value, '^\d+$') then 'Y' else 'N' end;
  7  end;
  8  /

Function created.

SQL>

Control file which also contains some sample data. Check the PATHID field which utilizes previously created function.

load data 
infile *
replace
into table test
fields terminated by "|" TRAILING NULLCOLS 
(
pathid integer external "case when f_isnum(:pathid) = 'Y' then :pathid else null end",
name)

begindata
123|Littlefoot
xxx|Invalid one
125|This is OK

Testing:

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

SQL*Loader: Release 11.2.0.2.0 - Production on Pon Svi 6 18:41:12 2019

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

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * From test;

    PATHID NAME
---------- --------------------
       123 Littlefoot
           Invalid one
       125 This is OK

SQL>