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"
)