3 Replies Latest reply: Feb 13, 2013 12:40 PM by 990721 RSS

    sqlldr - Leading space issue...

    990721
      Hi,

      I have a text file that has a field (fourth field in the file and table) with leading spaces followed by some valid values. When I use sqlldr to load the file, it trims the leading spaces even though I do not have a trim function within the control file. I have tried both varchar and char data type definitions on the field, with no luck. My database is defined with NLS_CHARACTERSET = 'AL32UTF8'. Has anyone come across this issue and got it resolved? Thank you for your assistance.

      SA
        • 1. Re: sqlldr - Leading space issue...
          Srini Chavali-Oracle
          Pl post details of OS and database versions, along with the complete sqlldr command, the contents of the control file and sample rows from the data file . Are you wanting to retain the leading spaces in the field ?

          http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#i1007735

          HTH
          Srini
          • 2. Re: sqlldr - Leading space issue...
            990721
            Srini,

            Thank you for your quick response. I have extracted one problem row for test here. I would like Field3 to carry forward the leading space from the input file without trimming it. I have tried Field3 to be defined as varchar2/char, with no luck in either case.

            OS: HP-UX B.11.31 U ia64
            DB: Oracle 11.2.0.3.0 64 bit

            DDL:
            CREATE TABLE "SMRTSTR"."TEST1"
            (     "FIELD1" VARCHAR2(10 BYTE),
                 "FIELD2" VARCHAR2(3 BYTE),
                 "FIELD3" CHAR(22 BYTE),
                 "FIELD4" TIMESTAMP (9)
            )

            test1.txt:
            0000153483{}001{} 04-151TTAP {}Mar 26 2004 12:00:00:000AM{}#-#

            test1.ctl:
            OPTIONS (SKIP=0,ERRORS=10)
            LOAD DATA
            INFILE 'test1.txt' "str '#-#'"
            TRUNCATE INTO TABLE test1
            FIELDS TERMINATED BY '{}' optionally enclosed by '}{}'
            TRAILING NULLCOLS (
            field1 ,
            field2 ,
            field3 ,
            field4 timestamp(9) 'Mon DD YYYY HH12:MI:SS:ff3PM')
            • 3. Re: sqlldr - Leading space issue...
              990721
              Success. Adding "PRESERVE BLANKS" to the ctl file did the trick!!!

              SA