7 Replies Latest reply: Sep 19, 2013 8:41 AM by chris227 RSS

    Bug in sqlldr using trim in ctl ?

    user1058993

      Hi Guys,

      When I am trying to upload data using ctl file having trim in one of the fields, the length of that column in database is not being considered.

       

      The control file is

      OPTIONS (SKIP=1, ERRORS=99999999, DIRECT=TRUE )

      LOAD DATA

      CHARACTERSET WE8ISO8859P15

      APPEND

      INTO TABLE s_test_trim

      WHEN RECORD_TYPE='D'

      FIELDS TERMINATED BY '|'

      TRAILING NULLCOLS

      ( static_1 CONSTANT "13-Sep-2013" ,

      static_2 CONSTANT "AMERICAS" ,

      field_1 CHAR "TRIM(:field_1)" );

       

      The ddl for table is

      CREATE TABLE s_test_trim

      ( static_1 DATE NOT NULL ,

      static_2 VARCHAR2(10) NOT NULL ,

      field_1 VARCHAR2(10) )

      COMPRESS ;

       

      Now, when I am trying to load data of field length > 10 for field_1, it is allowing me to do so.

      I mean the field length for field_1 is 10, but when I am trying to insert data having value greater than 10 chars, it is inserting the data into the column.

      When I am trying to do the same, using insert statement, it is throwing expected error message.

      I am using 11g

      Please guide me.

        • 1. Re: Bug in sqlldr using trim in ctl ?
          chris227

          BTW there is a dedicated sqlldr forum on this site.

          However i wonder if the sqlldr inserts CONSTANT "" in not null columns.

          So i doubt in your example given.

          • 2. Re: Bug in sqlldr using trim in ctl ?
            AlbertoFaenza

            Hi,

             

            do you mind posting some lines of your data file?

            I don't quite understand which problem you have. Could you explain exactly this sentence:

            Now, when I am trying to load data of field length > 10 for field_1, it is allowing me to do so.

             

            and explain in detail which problem you are facing?

             

            TRIM is only removing leading and trailing blanks. What are you trying to do?

             

            Regards.

            Alberto

            • 3. Re: Bug in sqlldr using trim in ctl ?
              Barbara Boehmer

              The following is a simplified example.  Both rows are greater than 10 characters, with the second row only greater than 10 characters if you include the trailing spaces.  The first row is properly rejected.  Despite the fact that the table's column is varchar2(10), it does not reject the second row.  Apparently it trims the spaces first, then checks the length, not the other way around.

               

              SCOTT@orcl12c> HOST TYPE test.ctl

              LOAD DATA

              INFILE *

              INTO TABLE s_test_trim

              FIELDS TERMINATED BY '|'

              TRAILING NULLCOLS

              (field_1 CHAR "TRIM(:field_1)" )

              BEGINDATA:

              123456789012345|

              12345678       |

               

              SCOTT@orcl12c> CREATE TABLE s_test_trim

                2    (field_1 VARCHAR2(10))

                3  /

               

              Table created.

               

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

               

              SQL*Loader: Release 12.1.0.1.0 - Production on Wed Sep 18 00:48:34 2013

               

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

               

              Path used:      Conventional

              Commit point reached - logical record count 2

               

              Table S_TEST_TRIM:

                1 Row successfully loaded.

               

              Check the log file:

                test.log

              for more information about the load.

               

              SCOTT@orcl12c> SELECT * FROM s_test_trim

                2  /

               

              FIELD_1

              ----------

              12345678

               

              1 row selected.

              t

              • 4. Re: Bug in sqlldr using trim in ctl ?
                user1058993

                Hi Barbara,

                You did not use the parameter DIRECT=TRUE in ctl file

                When I am giving this parameter and using TRIM with the column in ctl, it is ignoring the length defined for the columns in the table and is inserting the data.

                But, is it the desired functionality?

                 

                test.ctl

                OPTIONS (SKIP=1,ERRORS=99999999, DIRECT=TRUE )

                LOAD DATA

                CHARACTERSET WE8ISO8859P15

                APPEND

                INTO TABLE s_test_trim

                FIELDS TERMINATED BY '|'

                TRAILING NULLCOLS (field_1 CHAR "TRIM(:field_1)")

                 

                test.dat

                123456789012345|

                12345678      |

                123456789012345|

                 

                CREATE TABLE s_test_trim (field_1 VARCHAR2(10) );

                 

                sqlldr scott/tiger data=test.dat control=test.ctl log=test.log

                • 5. Re: Bug in sqlldr using trim in ctl ?
                  Barbara Boehmer

                  Whether you use direct or conventional path is irrelevant as the results are the same either way.  It trims the spaces first, then checks the length, so the following row is the only row that is loaded.

                   

                  12345678 |

                   

                  When you trim the spaces, this is the expected behavior.  Also note that the same happens when you insert using SQL as demonstrated below.

                   

                   

                  SCOTT@orcl12c> SELECT * FROM s_test_trim

                    2  /

                   

                  no rows selected

                   

                  SCOTT@orcl12c> INSERT INTO s_test_trim VALUES (TRIM ('12345678       '))

                    2  /

                   

                  1 row created.

                   

                  SCOTT@orcl12c> SELECT * FROM s_test_trim

                    2  /

                   

                  FIELD_1

                  ----------

                  12345678

                   

                  1 row selected.

                  • 6. Re: Bug in sqlldr using trim in ctl ?
                    AlbertoFaenza

                    I confirm what Barbara is saying.

                     

                    I did the same test in the following DB version:

                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                    PL/SQL Release 11.2.0.1.0 - Production

                    CORE    11.2.0.1.0    Production

                    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

                    NLSRTL Version 11.2.0.1.0 - Production

                     

                    test.ctl

                    OPTIONS (SKIP=1,ERRORS=99999999, DIRECT=TRUE )

                    LOAD DATA

                    CHARACTERSET WE8ISO8859P15

                    APPEND

                    INTO TABLE s_test_trim

                    FIELDS TERMINATED BY '|'

                    TRAILING NULLCOLS (field_1 CHAR "TRIM(:field_1)")

                     

                    test.dat

                    123456789012345|

                    12345678      |

                    123456789012345|

                    Command line:

                    $ sqlldr userid=myusr/mypwd@localdb control=test.ctl data=test.dat

                     

                    SQL*Loader: Release 11.2.0.1.0 - Production on Thu Sep 19 11:47:23 2013

                     

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

                     

                     

                    Load completed - logical record count 2.

                     

                    And after that only one row got inserted:

                    SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 19 11:52:03 2013

                     

                    Copyright (c) 1982, 2010, Oracle.  All rights reserved.

                     

                     

                    Connected to:

                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                     

                     

                    FIELD_1

                    ----------

                    12345678

                     

                    SQL>

                     

                     

                    Regards.

                    Alberto

                    • 7. Re: Bug in sqlldr using trim in ctl ?
                      chris227

                      Post output of test.log.