This discussion is archived
12 Replies Latest reply: Jul 7, 2013 4:00 PM by Srini Chavali-Oracle RSS

Issue while using SQL Loader in concurrent programs

473323 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for the details..
    Rgds
    Balaji
  • 11. Re: Issue while using SQL Loader in concurrent programs
    1010582 Newbie
    Currently Being Moderated

    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 Oracle ACE Director
    Currently Being Moderated

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points