Can I filter rows by date in Oracle SQL Loader?

Question

Everyday I receive a big interface file and I'm loading all the rows, but I'm only using the most recent information, (much less rows). Can I filter by the .ctl file so as to insert only the newest rows?

This is my ctl file:

LOAD DATA
INSERT  INTO  TABLE SCHEMA.TBL_INTERFACE
(
"ID"    POSITION(001:008), --varchar2(08),
"FIRSTNAME"    POSITION(009:028), --varchar2(20),
"LASTNAME"    POSITION(029:048), --varchar2(20),
"DATE"    POSITION(049:058), --varchar2(10) FORMAT YYYYMMDD 20211029
)

This is a sample of the interface I load:

12345678JUAN CARLOS0        PEREZ0               20211029
23456789JUAN CARLOS1        PEREZ1               20201029
34567890JUAN CARLOS2        PEREZ2               20181029
45678901JUAN CARLOS3        PEREZ3               20171029

Answer 1

How about external tables feature? Its benefit is that you can write queries against it (and simply filter rows you're interested in). Drawback? You have to have access to database server; no problem - from my point of view, though; it's just that you can't run everything locally.

Here's a walkthrough:

As that feature requires access to directory (Oracle object that points to a filesystem directory), we'll have to create it first, connected as SYS:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

Connect as scott (use which will be working on that problem):

SQL> connect scott/tiger
Connected.

Target table (I refuse to use reserved word - DATE - for column name. That's a bad practice, I suggest you avoid it):

SQL> desc tbl_interface
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 FIRSTNAME                                          VARCHAR2(20)
 LASTNAME                                           VARCHAR2(20)
 DATUM                                              DATE

Data is stored in C:\TEMP\TEXT.TXT file; note that I'm running Oracle on my laptop which - therefore - acts as if it was a database server. If your database server isn't on your PC, you'll have to talk to DBA.

12345678JUAN CARLOS0        PEREZ0               20211029
23456789JUAN CARLOS1        PEREZ1               20201029
34567890JUAN CARLOS2        PEREZ2               20181029
45678901JUAN CARLOS3        PEREZ3               20171029

Just setting date format (you don't have to do that):

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

Let's, finally, create external table:

SQL> create table ext_table
  2    (id         number,
  3     firstname  varchar2(20),
  4     lastname   varchar2(20),
  5     datum      date
  6    )
  7  organization external
  8    (type oracle_loader
  9     default directory ext_dir
 10     access parameters
 11       (records delimited by newline
 12        fields (id        position(01:08) char(8),
 13                firstname position(09:28) char(20),
 14                lastname  position(29:48) char(20),
 15                datum     date mask "yyyymmdd"
 16               )
 17       )
 18     location('text.txt')
 19    )
 20     reject limit unlimited;

Table created.

Is there anything there?

SQL> select * From ext_table;

        ID FIRSTNAME            LASTNAME             DATUM
---------- -------------------- -------------------- ----------
  12345678 JUAN CARLOS0         PEREZ0               29.10.2021
  23456789 JUAN CARLOS1         PEREZ1               29.10.2020
  34567890 JUAN CARLOS2         PEREZ2               29.10.2018
  45678901 JUAN CARLOS3         PEREZ3               29.10.2017

SQL>

Yes, there is - all rows from text.txt.

As we're using SQL, no problem in applying any filter to data, such as the one you wanted - to get the most recent data (today's):

SQL> select * From ext_table
  2  where datum = trunc(sysdate);

        ID FIRSTNAME            LASTNAME             DATUM
---------- -------------------- -------------------- ----------
  12345678 JUAN CARLOS0         PEREZ0               29.10.2021

Obviously, now it is a simple matter of inserting desired values into the target table:

SQL> insert into tbl_interface (id, firstname, lastname, datum)
  2  select id, firstname, lastname, datum
  3  from ext_table
  4  where datum = trunc(sysdate);

1 row created.

SQL> select * from tbl_interface;

        ID FIRSTNAME            LASTNAME             DATUM
---------- -------------------- -------------------- ----------
  12345678 JUAN CARLOS0         PEREZ0               29.10.2021

SQL>