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