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:
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:
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
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
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
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
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
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
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.
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
Post a Comment