5 Replies Latest reply: Oct 14, 2013 3:01 PM by Barbara Boehmer RSS

    sql loader -control files

    00125

      Hi all,

       

       

                        I have a file having data.I want to insert only of exp_data<=current data in table.my control file like this.

       

       

      LOAD DATA

      infile 'D:\sample.DAT'

        TRUNCATE

      INTO TABLE s_TABLE WHEN (1:6)<="(to_char(sysdate,'mmddyy'))"

      TRAILING NULLCOLS

      (

      eff_data position(1:6),

      name    position(7:20)

      )

       

                            While running this I got error like this

       

      SQL*Loader-350: Syntax error at line 48.
      Illegal combination of non-alphanumeric characters
      INTO TABLE s_TABLE WHEN (1:6)<="(to_char(sysdate,'

       

      If I use with out less than symbol I gets the data.Is any other way to achieve this scenario.If u need further data I ll provide you.

       

       

      Thank you.

        • 1. Re: sql loader -control files
          DK2010

          Hi,

           

          Can you Try this

          INTO TABLE s_TABLE WHEN (eff_data <= "(to_date(sysdate,'mmddyy'))")

          can you also provide the sample data 2 or 3 lines

           

          HTH

          • 2. Re: sql loader -control files
            00125

            Hi

             

                         Below is sample data.

             

            011413DBTEST

            011513DBDATAFORTEST

            102313DATA

             

            Table as:

             

            Create table s_TABLE

            (

            eff_data varchar2(6),

            name   varchar2(20)

            )

            • 3. Re: sql loader -control files
              00125

              Hi,

               

                         By trying as eff_data inplace position(1:6) still it gives same problem

              • 4. Re: sql loader -control files
                DK2010

                Hi,

                 

                In sql loader  comparison operator for either equal or not equal. Ref Doc:SQL*Loader Field List Reference

                You can go for the External Table and Filter Your record when insert from External table to your Permanent Table  :ORACLE-BASE - External Tables: Querying Data From Flat Files in Oracle

                 

                HTH

                • 5. Re: sql loader -control files
                  Barbara Boehmer

                  You can also do this using a trigger to reject the unwanted rows, as demonstrated below.

                   

                  SCOTT@orcl12c> HOST TYPE sample.dat

                  011413DBTEST

                  011513DBDATAFORTEST

                  102313DATA

                   

                  SCOTT@orcl12c> HOST TYPE test.ctl

                  LOAD DATA

                  infile 'sample.DAT'

                  TRUNCATE

                  INTO TABLE s_TABLE

                  TRAILING NULLCOLS

                  (eff_data position(1:6),

                  name    position(7:20))

                   

                  SCOTT@orcl12c> Create table s_TABLE

                    2    (eff_data varchar2(6),

                    3     name     varchar2(20))

                    4  /

                   

                  Table created.

                   

                  SCOTT@orcl12c> CREATE OR REPLACE TRIGGER s_table_eff_data_bir

                    2    BEFORE INSERT ON s_table

                    3    FOR EACH ROW

                    4    WHEN (TO_DATE (NEW.eff_data, 'mmddyy') > SYSDATE)

                    5  BEGIN

                    6    RAISE_APPLICATION_ERROR (-20001, 'Eff_data cannot be greater than sysdate!');

                    7  END s_table_eff_data_bir;

                    8  /

                   

                  Trigger created.

                   

                  SCOTT@orcl12c> SHOW ERRORS

                  No errors.

                  SCOTT@orcl12c> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

                   

                  SQL*Loader: Release 12.1.0.1.0 - Production on Mon Oct 14 12:57:48 2013

                   

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

                   

                  Path used:      Conventional

                  Commit point reached - logical record count 3

                   

                  Table S_TABLE:

                    2 Rows successfully loaded.

                   

                  Check the log file:

                    test.log

                  for more information about the load.

                   

                  SCOTT@orcl12c> SELECT * FROM s_table

                    2  /

                   

                  EFF_DA NAME

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

                  011413 DBTEST

                  011513 DBDATAFORTEST

                   

                  2 rows selected.