3 Replies Latest reply: Jul 14, 2014 12:43 PM by Barbara Boehmer RSS

    Load data in multiple table using sqlloader based on condition

    raj4tech

      I have the following data in a test file, i want to upload it in table using sql loader but before loading

      i want to validate the data and if does not match the condition then i would like to insert the data in another table.

       

       

      123,"2014-12-21",1.234

      234,"2014-11-28",1.256

       

       

      like 1st record is valid because data is less that 25th of the month so it should be inserted into a table

      taba(id number,dt date,num number)

       

       

      and second record is not a valid so it should be inserted into

      tabb(id number,dt date,num number)

       

       

      Is it possible using sqlloader? Please help me

        • 1. Re: Load data in multiple table using sqlloader based on condition
          Hoek

          Suggest you use an external table instead SQL*Loader ("the 20th century legacy data loading tool for old people"):

          https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5323624300346748382

          • 2. Re: Load data in multiple table using sqlloader based on condition
            RajenB

            Agree with Hoek

             

            But if you still want to work with sql loader, have a look at:

            http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_concepts.htm#i1006494

             

            • Case Study 5: Loading Data into Multiple Tables - Loads data into multiple tables in one run.
            • 3. Re: Load data in multiple table using sqlloader based on condition
              Barbara Boehmer

              Sql*Loader WHEN clauses can only use = or !=, not < or >.  If your data is on your server, not your client, or you can move it there, then you can use an external table as a staging table to access it.  If not, then you can use SQL*Loader to load the data into a staging table.  Then you can use an insert statement to distribute the data.  I have demonstrated both below.

               

               

              SCOTT@orcl12c> -- test data:

              SCOTT@orcl12c> host type c:\my_oracle_files\test.dat

              123,"2014-12-21",1.234

              234,"2014-11-28",1.256

               

              SCOTT@orcl12c> -- Use an external table as a staging table:

              SCOTT@orcl12c> create or replace directory my_dir as 'c:\my_oracle_files'

                2  /

               

              Directory created.

               

              SCOTT@orcl12c> create table staging(id number,dt date,num number)

                2  ORGANIZATION external

                3    (TYPE oracle_loader

                4     DEFAULT DIRECTORY my_dir

                5     ACCESS PARAMETERS

                6       (RECORDS DELIMITED BY NEWLINE

                7        LOGFILE 'test.log'

                8        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

                9        MISSING FIELD VALUES ARE NULL

              10        REJECT ROWS WITH ALL NULL FIELDS

              11          (id,

              12           dt DATE MASK 'yyyy-mm-dd',

              13           num))

              14     LOCATION ('test.dat'))

              15  REJECT LIMIT UNLIMITED

              16  /

               

              Table created.

               

              SCOTT@orcl12c> select * from staging

                2  /

               

                      ID DT                     NUM

              ---------- --------------- ----------

                     123 Sun 21-Dec-2014      1.234

                     234 Fri 28-Nov-2014      1.256

               

              2 rows selected.

               

              SCOTT@orcl12c> -- or use SQL*Loader to load data into a staging table:

              SCOTT@orcl12c> drop table staging

                2  /

               

              Table dropped.

               

              SCOTT@orcl12c> create table staging(id number,dt date,num number)

                2  /

               

              Table created.

               

              SCOTT@orcl12c> host type test.ctl

              load data

              into table staging

              fields terminated by ','

              optionally enclosed by '"'

              trailing nullcols

              ( id

              , dt date 'yyyy-mm-dd'

              , num )

               

              SCOTT@orcl12c> host sqlldr scott/tiger control=test.ctl data=test.dat log=test.log

               

              SQL*Loader: Release 12.1.0.1.0 - Production on Mon Jul 14 10:40:38 2014

               

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

               

              Path used:      Conventional

              Commit point reached - logical record count 2

               

              Table STAGING:

                2 Rows successfully loaded.

               

              Check the log file:

                test.log

              for more information about the load.

               

              SCOTT@orcl12c> select * from staging

                2  /

               

                      ID DT                     NUM

              ---------- --------------- ----------

                     123 Sun 21-Dec-2014      1.234

                     234 Fri 28-Nov-2014      1.256

               

              2 rows selected.

               

              SCOTT@orcl12c> -- target tables:

              SCOTT@orcl12c> create table taba(id number,dt date,num number)

                2  /

               

              Table created.

               

              SCOTT@orcl12c> create table tabb(id number,dt date,num number)

                2  /

               

              Table created.

               

              SCOTT@orcl12c> -- insert data from staging or external table into target tables:

              SCOTT@orcl12c> insert all

                2  when to_number (to_char (dt, 'dd')) < 25 then

                3    into taba (id, dt, num)

                4  when to_number (to_char (dt, 'dd')) >= 25 then

                5    into tabb (id, dt, num)

                6  select id, dt, num from staging

                7  /

               

              2 rows created.

               

              SCOTT@orcl12c> -- results;

              SCOTT@orcl12c> select * from taba

                2  /

               

                      ID DT                     NUM

              ---------- --------------- ----------

                     123 Sun 21-Dec-2014      1.234

               

              1 row selected.

               

              SCOTT@orcl12c> select * from tabb

                2  /

               

                      ID DT                     NUM

              ---------- --------------- ----------

                     234 Fri 28-Nov-2014      1.256

               

              1 row selected.