12 Replies Latest reply: Jul 7, 2013 6:00 PM by Srini Chavali-Oracle RSS

    Issue while using SQL Loader in concurrent programs

    473323
      Hi,
      I am using a concurrent program based on SQL Loader to load data into the tables from flat files. Columns are identified based on position.
      The first and last rows from the flat needs to be ignored.
      First row has 01 in the first two positions
      Last row has 99 in the first two positions
      The other records start with 10.

      My control file looks similar to this
      LOAD DATA
      TRUNCATE
      INTO TABLE XX_STAGE
      WHEN (01:02)= '10'
      (
      ....
      ....
      )

      The problem with this approach is the request ends in a warning which is not preferred. If I give SKIP to skip the first row, the last row still fails and discarded.
      It results in the warning.

      Is there a better way of doing this?

      Thanks
      Balaji
        • 1. Re: Issue while using SQL Loader in concurrent programs
          Srini Chavali-Oracle
          Pl post details of OS, database and EBS versions, along with the complete log of the concurrent program showing the warning.

          One option would be to strip the first and last record from the file before loading it.

          HTH
          Srini
          • 2. Re: Issue while using SQL Loader in concurrent programs
            473323
            Hi Srini,

            The EBS version is 11.5.10.2, DB version is 11G and its on unix.
            Few details from sql loader log program.

            Record 1: Discarded - failed all WHEN clauses.
            Record 39: Discarded - failed all WHEN clauses.

            Table XX_STAGE:
            37 Rows successfully loaded.
            0 Rows not loaded due to data errors.
            2 Rows not loaded because all WHEN clauses were failed.
            0 Rows not loaded because all fields were null.

            Control file structure
            LOAD DATA
            TRUNCATE
            INTO TABLE XX_STAGE
            WHEN (01:02)= '10'
            (
            .....
            )
            Thanks,
            Balaji
            • 3. Re: Issue while using SQL Loader in concurrent programs
              mv17
              Here are steps to resolve this issue.

              1. Create Unix prog file to run the SQL loader instead of running the SQL Loader directly.
              2. In Unix prog use below command to for the SQL loader
              sqlldr apps/devbau1896@devbau control=loader.ctl data=datafile.txt log=temp.log bad=temp.bad silent=DISCARDS
              3. Create the concurrent program for that Unix prog file.

              Now your concurrent program will finish w/o warning.

              Let me know if you need further help.
              • 4. Re: Issue while using SQL Loader in concurrent programs
                Srini Chavali-Oracle
                The issue with the approach stated above is that this conc pgm will always complete successfully, even if there are errors in loading records. More scripting will need to be done in order to determine if all rows in the flat file got uploaded successfully or not, so as to determine how the script should end (success, warning, failure).

                HTH
                Srini
                • 5. Re: Issue while using SQL Loader in concurrent programs
                  473323
                  Hi,

                  Thanks for your suggestion. I was actually trying to pass one more parameter to the SQL Loader concurrent program by passing silent=discards.
                  The first parameter (data=) gives the data file name.

                  Apparently what happens is both the values are going to data parameter and I am getting following error. The concurrent request ends in error.

                  LRM-00112: Multiple values not allowed for parameter 'data'

                  Is there a way to get rid of this.
                  Thanks,
                  Balaji
                  • 6. Re: Issue while using SQL Loader in concurrent programs
                    Srini Chavali-Oracle
                    The SILENT option only suppresses the error message, it does not eliminate the cause of the error - so I do not think it will help in this case. I think you have two options -

                    1. Eliminate the header and trailer lines from the input file, or
                    2. Write a shell script as above with additional code to trap how the conc pgm should end

                    HTH
                    Srini
                    • 7. Re: Issue while using SQL Loader in concurrent programs
                      mv17
                      Yeah, that's true you have to write additional scripting to make sure when it should give error, like when there are bad records in the file.
                      Let me know, if you need the example of such script.
                      • 8. Re: Issue while using SQL Loader in concurrent programs
                        473323
                        Yes please, can you give any such sample script?

                        Edited by: BalajiM on Nov 17, 2010 7:39 AM
                        • 9. Re: Issue while using SQL Loader in concurrent programs
                          mv17
                          Sorry mate for late reply, I was off sick.
                          Anyways....please find below script as an example, you have to modfiy it little bit for your requirement

                          # Create a runtime control file with parameters passed from Concurrent Program
                          echo ' '
                          echo "Generating control file"
                          sqlplus -SILENT $db_login_info <<END_OF_CONTROL_SQL
                          SET NEWP NONE
                          SET DOCUMENT OFF
                          SET HEADING OFF
                          SET FEEDBACK OFF
                          SET TERM OFF
                          SET VERIFY OFF
                          SET ECHO OFF
                          SET SHOW OFF
                          SET PAGESIZE 0
                          SET LINESIZE 256
                          SET TRIMSPOOL ON
                          DEFINE V_GROUP_ID NUMBER
                          SPOOL $control_file
                          SELECT 'OPTIONS (ERRORS=1000)
                          LOAD DATA CHARACTERSET WE8ISO8859P1 INFILE *
                          DISCARDMAX 1000000000
                          APPEND
                          INTO TABLE "GL_INTERFACE"
                          FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' TRAILING NULLCOLS
                          (set_of_books_id char "xxcle_common_utilities_pkg.get_set_of_books_id(:set_of_books_id)",
                          accounting_date date ''YYYYMMDD'',
                          currency_code char "ltrim(rtrim(:currency_code))",
                          actual_flag char "NVL(ltrim(rtrim(:actual_flag)), ''$actual_flag'')",
                          user_je_category_name char "ltrim(rtrim(:user_je_category_name))",
                          user_je_source_name char "ltrim(rtrim(:user_je_source_name))",
                          user_currency_conversion_type char "ltrim(rtrim(:user_currency_conversion_type))",
                          currency_conversion_date date ''YYYYMMDD'',
                          currency_conversion_rate char "ltrim(rtrim(:currency_conversion_rate))",
                          segment1 char "ltrim(rtrim(:segment1))",
                          segment2 char "ltrim(rtrim(:segment2))",
                          segment3 char "ltrim(rtrim(:segment3))",
                          segment4 char "ltrim(rtrim(:segment4))",
                          segment5 char "ltrim(rtrim(:segment5))",
                          segment6 char "ltrim(rtrim(:segment6))",
                          segment7 char "ltrim(rtrim(:segment7))",
                          entered_dr char "ltrim(rtrim(:entered_dr))",
                          entered_cr char "ltrim(rtrim(:entered_cr))",
                          reference1 char "ltrim(rtrim(:reference1))", -- Batch Name
                          reference2 char "ltrim(rtrim(:reference2))", -- Batch Description
                          reference4 char "ltrim(rtrim(:reference4))", -- Journal Entry Name
                          reference5 char "ltrim(rtrim(:reference5))", -- Journal Entry Description
                          reference10 char "unistr(ltrim(rtrim(:reference10)))", -- Jounral Entry Line Description
                          reference21 char "ltrim(rtrim(:reference21))", -- Line Detail Additional Information
                          reference24 char "ltrim(rtrim(:reference24))", -- Source Detail Additional Information
                          attribute1 char "ltrim(rtrim(:attribute1))",
                          attribute2 char "ltrim(rtrim(:attribute2))",
                          reference25 char "ltrim(rtrim(:reference25))",
                          reference26 char "ltrim(rtrim(:reference26))",
                          reference27 char "ltrim(rtrim(:reference27))",
                          reference28 char "ltrim(rtrim(:reference28))",
                          reference29 char "ltrim(rtrim(:reference29))",
                          reference30 char "ltrim(rtrim(:reference30))",
                          attribute3 char "ltrim(rtrim(:attribute3))",
                          attribute4 char "ltrim(rtrim(:attribute4))",
                          attribute5 char "ltrim(rtrim(:attribute5))",
                          attribute6 char "ltrim(rtrim(:attribute6))",
                          attribute7 char "ltrim(rtrim(:attribute7))",
                          attribute8 char "ltrim(rtrim(:attribute8))",
                          attribute9 char "ltrim(rtrim(:attribute9))",
                          attribute10 char "ltrim(rtrim(:attribute10))",
                          attribute11 char "ltrim(rtrim(:attribute11))",
                          attribute12 char "ltrim(rtrim(:attribute12))",
                          attribute13 char "ltrim(rtrim(:attribute13))",
                          attribute14 char "ltrim(rtrim(:attribute14))",
                          attribute15 char "ltrim(rtrim(:attribute15))",
                          attribute16 char "ltrim(rtrim(:attribute16))",
                          attribute17 char "ltrim(rtrim(:attribute17))",
                          attribute18 char "ltrim(rtrim(:attribute18))",
                          attribute19 char "ltrim(rtrim(:attribute19))",
                          attribute20 char "ltrim(rtrim(:attribute20))",
                          status constant ''NEW'',
                          created_by constant ''$user_id'', --char "fnd_global.user_id",
                          date_created SYSDATE,
                          group_id constant $request_id)'
                          FROM DUAL;

                          SET TERMOUT OFF

                          EXIT;
                          END_OF_CONTROL_SQL

                          # Call the SQL*Loader program to load the data into the database
                          sqlldr $db_login_info control=$APPLTMP/$request_id \
                          data=$file \
                          log=$APPLTMP/gl_interface_$timestamp.log \
                          bad=$APPLTMP/gl_interface_$timestamp.bad \
                          discard=$APPLTMP/gl_interface_$timestamp.dsc

                          # Print the output of the SQL*Loader log file
                          echo "****************** SQL*Loader Log File for $file **************"
                          cat $APPLTMP/gl_interface_$timestamp.log
                          rm $APPLTMP/gl_interface_$timestamp.log

                          # Move the inbound Files to the archive directory
                          echo "Moving $file file to $archive_dir"
                          mv "$datafile" "$archive_dir/"

                          # Check the return code to verify that the move was successful
                          ret=$?
                          if [ $ret -gt 0 ]
                          then
                          echo "Error Moving Inbound File $file To Archive Directory"
                          exit 2
                          fi

                          # If a bad file was created, then an error was encountered


                          if [ -s $APPLTMP/gl_interface_$timestamp.bad ]
                          then
                          echo ' '
                          echo '****************** SQL*Loader ERRORs - Bad Records **************'
                          echo ' '
                          cat $APPLTMP/gl_interface_$timestamp.bad
                          rm $APPLTMP/gl_interface_$timestamp.bad
                          exit 2
                          fi
                          • 10. Re: Issue while using SQL Loader in concurrent programs
                            473323
                            Thanks for the details..
                            Rgds
                            Balaji
                            • 11. Re: Issue while using SQL Loader in concurrent programs
                              1010582

                              Hi All,

                               

                              Even I had same issue with program completed with warning status,as few rows not loaded because all WHEN clauses were failed in data file.

                              My Control file is :

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

                              LOAD DATA

                              INFILE

                              INSERT INTO TABLE xxpo_header

                              WHEN (01) = 'H'

                              FIELDS TERMINATED BY  "|" 

                              TRAILING NULLCOLS

                              (.....);

                              .........................................

                               

                              My data file consists :

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

                              H|.............

                              L|...........

                              D|..........

                              H|.............

                              L|...........

                              D|..........

                              H|.............

                              L|...........

                              D|..........

                               

                              I need to load the data when the record starts with 'H'.

                               

                              Thanks.

                              • 12. Re: Issue while using SQL Loader in concurrent programs
                                Srini Chavali-Oracle

                                Pl post your issue in a new thread (with all appropriate details) rather than hijack an old thread.