Wednesday, June 6, 2012

SQL LOADER

SQLLOADER is a great utility for loading data into Oracle Tables. But working with SQLLOADER can have its own set of challenges, some of the common requirements and there solutions are listed below.

1. Populating record number in SQL LOADER:
    This can be really helpful if you need to populate the sequence of records as they appear in the data file.
    SQL Loader doesn't neccessarly insert data the way it comes in datafile, using RECNUM can help.
    Record_ID RECNUM

2. Populating who columns in SQL LOADER
    ,CREATION_DATE        "TRUNC(SYSDATE)"     
    ,CREATED_BY           "UID"     
    ,LAST_UPDATE_DATE     "TRUNC(SYSDATE)"     
    ,LAST_UPDATED_BY      "UID"     

3. Populating request id and Filename (Input Parameters) in SQL LOADER
    SQLloader doesn't populate the request id, File name or other input parameters directly through
    control file. Even using the below statement doesn't work

    REQUEST_ID               "fnd_global.conc_request_id"

    So write a Wrapper Program and trigger SQLOADER through the wrapper and then excute a Update
    statement to update the required fields. Below is sample code
   
    -- Call SQL*Loader program that load the data from csv file
    l_conc_request_id := fnd_request.submit_request
                                               ('XXX',
                            'XXSQLLDR',
                            '',
                            '',
                            FALSE,
                            l_chr_dir_path||'/IN/'||in_file_name);

    fnd_file.put_line( fnd_file.log,'Request id: '||l_conc_request_id );

    IF l_conc_request_id != 0
    THEN

        COMMIT;

        l_call_status :=  fnd_concurrent.wait_for_request
                                ( l_conc_request_id,
                                  3,        -- interval
                                  0,        -- max_wait
                                  l_phase,
                                  l_status,
                                  l_dev_phase,
                                  l_dev_status,
                                  l_message
                                 );

    END IF;

    --Check Program completion status
    IF  (l_dev_phase = 'COMPLETE'
        AND l_dev_status = 'NORMAL')
    THEN
        fnd_file.put_line( fnd_file. 'Program Completed Successfully');

        --Update the Staging table
        UPDATE xx_stg_table
           SET filename = in_file_name,
               request_id= l_conc_request_id
        WHERE  status = 'NEW'
        AND    request_id IS NULL;

        COMMIT;

    ELSE
        fnd_file.put_line( fnd_file.log, 'Program did not Completed Successfully: '||l_dev_status);       

    END IF;


4. Populate a sequence value in SQL LOADER
     LOAD DATA
       INFILE *
       INTO TABLE load_db_seq_positional
       (seq_number "db_seq.nextval"
        data1 POSITION(1:5),
        data2 POSITION(6:15),
       )

5. Using functions in SQL LOADER

    LOAD DATA
    INFILE 'xyz.dat'
    BADFILE 'xyz.bad'
    LOG xyz.log
    INSERT INTO TABLE empmast
    (emp_no POSITION(1:6) INTEGER,
    emp_name POSITION(7:31) CHAR "initcap(:emp_name)")

6. Assigning constants in SQL LOADER

    LOAD DATA
    INFILE 'xyz.dat'
    BADFILE 'xyz.bad'
    LOG xyz.log
    INSERT INTO TABLE empmast
    (emp_no POSITION(1:6) INTEGER,
    emp_name POSITION(7:31) CHAR,
    alive CONSTANT "Y")
   
7. Fixed position file in SQL LOADER

    LOAD DATA
    INFILE filename
    REPLACE
    INTO TABLE temp_data
    (
     field1    POSITION (1:4)     INTEGER EXTERNAL,
     field2    POSITION (5:6)     INTEGER EXTERNAL,
     field3    POSITION (7:12)    INTEGER EXTERNAL,
     field4    POSITION (13:42)   CHAR,
     field5    POSITION (43:72)   CHAR,
     field6    POSITION (73:73)   INTEGER EXTERNAL,
     field7    POSITION (74:74)   INTEGER EXTERNAL,
     field8    POSITION (75:75)   INTEGER EXTERNAL,
     field9    POSITION (76:86)   INTEGER EXTERNAL
    )

8. Comma delimited file in SQL LOADER

    LOAD DATA
    INFILE *
    INTO TABLE stg_table
    REPLACE
    FIELDS TERMINATED BY ','
    (
     image_id   INTEGER(5),
     file_name  CHAR(30),
     field1,
     field2
        )
9. Command SQL LOADER
  sqlldr userid=ora_id/ora_passwd control=control_file_name.ctl data= data.csv

10. Populating master and detail record using SQL LOADER:
      Let say you have a data file with mutliple groups of data for Master and details record as below
    
     CONTACT REC1
     FIRST NAME LAST NAME1
     FIRST NAME LAST NAME2
     CONTACT REC2
     FIRST NAME LAST NAME21
     FIRSTNAME  LAST NAME22

    SQL Loader never process the data the sequence it comes in data file, but there is a workaround
    all you need to do is use SQL Loader command line parameter ROWS =1, this will make SQLLoader commit for each record and process records in the data file in the same sequence.

10.  Skip Loading of Columns SQLLOADER
FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
  field2 FILLER,
  field3
)
BOUNDFILLER
LOAD DATA
INFILE *
TRUNCATE INTO TABLE sometable
FIELDS TERMINATED BY ","  trailing nullcols
(
 c1,
 field2 BOUNDFILLER,
 field3 BOUNDFILLER,
 field4 BOUNDFILLER,
 field5 BOUNDFILLER,
 c2     ":field2 || :field3",
 c3     ":field4 + :field5"
)

8 comments:

sandeep saxena said...

First of all thanks for the useful info. It's very useful for my research.
SAS Training in Chennai
SAS Training Center in Chennai
SAS Training in Porur
clinical sas training in chennai
Placement Training in Chennai
Training institutes in Chennai with placement
soft skills training institutes in chennai

Sadhana Rathore said...

I really admired with your post, do share more updates.
Tally Training in Chennai
Tally course
Tally institute in Chennai
AngularJS course in Chennai
ccna Training in Chennai
PHP course in Chennai
Salesforce course in Chennai
Web Designing Training in Chennai
Tally Course in Velachery

Devi.Angularjs said...

Great Article. Thank you for sharing! Really an awesome post for every one.
Project Centers in Chennai

JavaScript Training in Chennai

Final Year Project Domains for IT

JavaScript Training in Chennai

Ravi said...


You write this post very carefully I think, which is easily understandable to me. Not only this, but another post is also good. As a newbie, this info is really helpful for me. Thanks to you.
Tally ERP 9 Training
tally classes
Tally Training institute in Chennai
Tally course in Chennai

Ravi said...


This content of information has
helped me a lot. It is very well explained and easy to understand.
seo training classes
seo training course
seo training institute in chennai
seo training institutes
seo courses in chennai
seo institutes in chennai
seo classes in chennai
seo training center in chennai

w3webschool said...

Thanks for sharing such a most informative blog.... Waiting for the new updates.
Digital Marketing Course In Kolkata
Web Design Course In Kolkata
SEO Course In Kolkata

Smith said...

Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

Radley Co Tad said...

IEEE Cloud computing DOamin is a general term for anything that involves delivering hosted services over the Internet. cloud computing projects The cloud projects for cse is a metaphor for a global network of remote servers which operates as a single ecosystem, commonly associated with the Internet. IEEE FInal Year Networking Projects for CSE Domains Networking Projects cloud computing is the delivery of computing projects services—including servers, storage, databases, networking projects, software, analytics, and intelligence


JavaScript Training in Chennai


JavaScript Training in Chennai