6 Replies Latest reply: Oct 25, 2013 5:03 AM by DK2010 RSS

    SQL Loader Data Issue - Please help us

    user627525

      Hi ,

       

      I have data in XYZ.csv file. by using SQL Loader I am trying to load the data into staging table.

      so the problem here is, the last column in the data file having data value as single character(example column name is status_active, it should contain values as either Y or N).

      but program is inserting extra space. in ordered to avoid the extra spaces I have used trim option like this way  STATUS_ACTIVE     CHAR "LTRIM(RTRIM(:STATUS_ACTIVE))" in the script. and tried  STATUS_ACTIVE     CHAR "REPLACE(:STATUS_ACTIVE,' ' ,'')" , But still it is storing extra one space in the column.

       

      can some one please help to solve this issue.

       

      Many Thanks.

        • 1. Re: SQL Loader Data Issue - Please help us
          Herald ten Dam

          Hi,

           

          how is the definition of your column in the staging table? Is it maybe a CHAR(2), then you have always the extra space, if you declare as VARCHAR2(2) you lose the space.

           

          Herald ten Dam

          <Moderator Edit - deleted link signature - pl see FAQ on top right>

          • 2. Re: SQL Loader Data Issue - Please help us
            Srini Chavali-Oracle

            Pl post OS and database details, output of "describe <table>", sample of csv file, the complete sqlldr command and contents of the control file

             

            HTH
            Srini

            • 3. Re: SQL Loader Data Issue - Please help us
              user627525

              Hi Herald ten Dam,

               

              Thanks for quick reply.

              I have re-verified my staging table and i see that column is defined with  VARCHAR2(30).

               

              Many Thanks.

              • 4. Re: SQL Loader Data Issue - Please help us
                user627525

                Hi Srini,

                 

                data base details are

                Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

                PL/SQL Release 9.2.0.8.0 - Production

                "CORE 9.2.0.8.0 Production"

                TNS for Solaris: Version 9.2.0.8.0 - Production

                NLSRTL Version 9.2.0.8.0 - Production

                 

                desc table

                SOURCEVARCHAR2(20 BYTE)Yes1
                COMMERCE_IDVARCHAR2(8 BYTE)Yes2
                LAST_NAMEVARCHAR2(40 BYTE)Yes3
                FIRST_NAMEVARCHAR2(40 BYTE)Yes4
                FULL_NAMEVARCHAR2(92 BYTE)Yes5
                STATUS_ACTIVEVARCHAR2(30 BYTE)Yes6

                 

                sample data file

                USD|1234|Brad|Ching|Brad-Ching|Y

                BRL|1235|Nancy|Don|Nancy Don|N

                 

                ctl file

                 

                OPTIONS (ERRORS = 0)

                Load DATA

                APPEND

                INTO TABLE TEST_VENDORS_INFO

                FIELDS TERMINATED BY '|'

                TRAILING NULLCOLS

                (

                SOURCE                                  CHAR "LTRIM(RTRIM(:SOURCE))",

                COMMERCE_ID                        CHAR "LTRIM(RTRIM(:COMMERCE_ID))",                             

                LAST_NAME                             CHAR "LTRIM(RTRIM(:LAST_NAME))",                         

                FIRST_NAME                            CHAR "LTRIM(RTRIM(:FIRST_NAME))",                                

                FULL_NAME                             CHAR "LTRIM(RTRIM(:FULL_NAME))",

                STATUS_ACTIVE                       CHAR "LTRIM(RTRIM(:STATUS_ACTIVE))"         

                )

                 

                sqlldr command

                 

                sqlldr USERID=$vl_ora_user_pass  CONTROL=$OC_TOP/bin/TEST_VENDORS_INFO_LOAD.ctl   DATA=$vl_file

                 

                Many Thanks

                • 5. Re: SQL Loader Data Issue - Please help us
                  user627525

                  Hi Srini,


                   

                  Issue has been resolved. I have used replaceoption. now additional space or garbage is not there in the column value.

                  STATUS_ACTIVE  "REPLACE(REPLACE(REPLACE(:STATUS_ACTIVE ,CHR(9)),CHR(10)),CHR(13))"


                  please confirm above approach can be used for all the columns in the script. Thanks in advance.


                  Many Thanks.

                  • 6. Re: SQL Loader Data Issue - Please help us
                    DK2010

                    Hi,

                     

                    You can also try this

                    STATUS_ACTIVE CHAR(1) TERMINATED BY WHITESPACE

                     

                    HTH