SQL*Loader Program Code

SQL*Loader Program Code

Below Parameters must be passed to this program while calling/submitting

p_job_name- Concurrent Program Short Name
p_directory- Server Path where data file resides
p_file_name- File name (.csv, .txt, etc)

FUNCTION submit_loader
(p_job_name IN VARCHAR2,
p_directory IN VARCHAR2,
p_file_name IN VARCHAR2)
RETURN BOOLEAN
IS
v_request_id NUMBER;
v_job_finished BOOLEAN := FALSE;
v_conc_request BOOLEAN := FALSE;
e_invalid_job_submission EXCEPTION;
e_job_error EXCEPTION;
e_job_failed EXCEPTION;
e_invalid_records EXCEPTION;
v_stage NUMBER (2) := 4;
v_phase VARCHAR2 (100);
v_request_status VARCHAR2 (100);
v_dev_phase VARCHAR2 (100);
v_dev_status VARCHAR2 (100);
v_message VARCHAR2 (100);
v_return BOOLEAN := FALSE;
BEGIN
fnd_file.put_line (fnd_file.LOG, 'P_JOB_NAME-' p_job_name);
v_request_id :=
fnd_request.submit_request
(application => 'XXCUST',
program => p_job_name,
sub_request => FALSE,
argument1 => p_directory '/' p_file_name);
/* Test to see if job submission has failed (0) */
IF v_request_id = 0 THEN
RAISE e_invalid_job_submission;
ELSIF v_request_id <> 0 -- successfully submitted
THEN
COMMIT;
/* Wait until concurrent job has completed */
WHILE NOT v_job_finished
LOOP
v_conc_request :=
fnd_concurrent.wait_for_request
(v_request_id,
30, -- Seconds between
31, -- Max Time to wait for
v_phase,
v_request_status,
v_dev_phase,
v_dev_status,
v_message);

v_job_finished := (v_conc_request = FALSE) OR (v_conc_request = TRUE AND v_dev_phase = 'COMPLETE');
END LOOP;

fnd_file.put_line (fnd_file.LOG, '*************************');
fnd_file.put_line (fnd_file.LOG, 'Dev Phase: ' v_dev_phase);
fnd_file.put_line (fnd_file.LOG, 'Dev Status: ' v_dev_status);
fnd_file.put_line (fnd_file.LOG, '*************************');

IF (NOT v_conc_request) OR (v_conc_request AND v_dev_phase = 'COMPLETE' AND v_dev_status = 'ERROR') -- v_dev_status <> 'NORMAL')
THEN
RAISE e_job_error;
END IF;
END IF;

v_return := TRUE;
RETURN v_return;
EXCEPTION
WHEN e_invalid_job_submission
THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, 'Error.');
fnd_file.put_line (fnd_file.LOG, 'The Concurrent Manager was unable to submit the SQL*Loader Concurrent job.');
fnd_file.put_line (fnd_file.LOG, '********************************');
fnd_file.put_line (fnd_file.LOG, ' ');
RETURN FALSE;

WHEN e_job_error
THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, 'Error.');
fnd_file.put_line (fnd_file.LOG, 'The SQL*Loader Concurrent job failed to complete successfully.');
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, ' ');
RETURN FALSE;

WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ' ');
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, 'Error.');
fnd_file.put_line (fnd_file.LOG, 'The SQL*Loader Concurrent job submission failed with the error : ');
fnd_file.put_line (fnd_file.LOG, SUBSTR (SQLERRM, 1, 300));
fnd_file.put_line (fnd_file.LOG, '*******************************');
fnd_file.put_line (fnd_file.LOG, ' ');
RETURN FALSE;
END submit_loader;

Tuesday, July 6, 2010

SQL Loader Overview

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. It supports various load formats, selective loading, and multi-table loads.

Below are the examples of loading data into Tables using different methods

Load Fixed length Data Records
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

Load Positional Data Records
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

Skip First 2 Header Records while Loading
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 2
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
NNNNNNNNNNNNNNN
UUUUUUUUUUUUUUU
11111AAAAAAAAAA
22222BBBBBBBBBB

Loading Data into Multiple Tables
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)

Skip Certain Columns while Loading DataLOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

Erroneous data after loading with SQL Loader

Our Database is created with below settingsCHARACTER SET "UTF8"
NATIONAL CHARACTER SET "WE8ISO8859P1"
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA

Include below code in control file to avoid junk data load using SQL loaderLOAD DATA
CHARACTERSET WE8ISO8859P1
APPEND INTO TABLE XXAP_GENERIC_INVOICES
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(operating_unit
,vendor_number
)

Using SQL Functions for Loader Columns
LOAD DATA
APPEND INTO TABLE
XXAR_INV_AT
FIELDS TERMINATED BY ""
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
DESCRIPTION "NVL(:DESCRIPTION,'Migrated from Legacy')",
TRX_DATE "TO_CHAR(TO_DATE(:TRX_DATE,'YYYYMMDD'),'DD-MON-YYYY')" ,
GL_DATE "TO_CHAR(TO_DATE(:GL_DATE,'YYYYMMDD'),'DD-MON-YYYY')" ,
TAX_RATE "REPLACE(:TAX_RATE,'%','')",
STAGING_TABLE_ID "XXAR_INV_AT_S1.NEXTVAL",
CREATION_DATE SYSDATE,
LAST_UPDATE_DATE SYSDATE
SOURCE "TRIM(REPLACE(:SOURCE,(SUBSTR(:SOURCE,LENGTH(:SOURCE),1)),''))"

Sqlloader Examples

Sqlloader Examples

SQLLDR - Shell script to load Multiple files in table

Based on request from one of our reader, here is the post to read file from a directory and then call sqlloader command to load data file into a table.
Assumptions:
1) There has to be some way/standard to recognize the file name. In my case its a .dat file starting withsample. If you don't know data file name then create a directory specific to the load and select all data files from that directory.
2) The file format has to be same so that same .ctl file can be used to read the file and load the table.
3) Once loaded the file is then archived.

Control file code

LOAD DATA
insert into table sv_temp
fields terminated by '|' optionally enclosed by '"'
(first_col
,second_col
)

Shell Script Code

cd $CUSTOM_TOP/data
for file in sample*.dat
do
sqlldr userid=$login control=$CUSTOM_TOP/bin/sv_test.ctl data=$CUSTOM_TOP/data/$file
mv $CUSTOM_TOP/data/$file $CUSTOM_TOP/archive/$file
done

3 files sample1.dat, sample2.dat, sample3.dat was copied in CUSTOM_TOP/data directory.
Sample Output is

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 8
Commit point reached - logical record count 9

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 5

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 7

SQL Loader (SQLLDR) in Oracle Applications

One of the executable type in Oracle applications is SQL*Loader. In this post we will discuss more about how to define, create and use this type of Execution Method.

Execution Method SQL*Loader is used to load data into database table using SQL*Loader. Below are the steps that needs to completed
1) Create Control File and copy it in the TOP/bin directory. E.g. if your custom application top directory is $XX_TOP, then the file should be copied in $XX_TOP/bin directory

OPTIONS (SKIP=1)
LOAD DATA infile '%1'
APPEND INTO TABLE sv_test_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
(
   item_number     "trim(:item_number)"
 , description     "trim(:description)"
 , attribute1      "trim(:attribute1)"
 , process_flag    Constant 'UNPROCESSED'
)

2) Create an executable with execution method as SQL*Loader and use the control file Name as Execution file Name. The Application should same as where the file is copied.

3) Create a concurrent program definition and use the executable created above.
It is optional to have any parameters to the program, but in this case the data file name should be same as control file name except the extension for the data file being .dat
If you notice in the control file definition above I have used %1 as the infile name. This is how parameters are referenced in control file.
So now we can create conc. program with a file name as a parameter(Full file name with path should be entered and this is case sensitive.)

4) Assign this program to the request group and it is ready to be used.

SQL Loader limit number of rows

Based on a query from one of our reader here is an example to illustrate how to limit number of rows to be loaded in SQL Loader. This can be done using option LOAD

OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
    item_number     "trim(:item_number)"
  , vendor_name "trim(:vendor_name)"
  , vendor_site_name "trim(:vendor_site_name)"
  , supplier_item   "trim(:supplier_item)"
  , process_flag  Constant 'UNPROCESSED'
)

In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.

These options can also be given with sqlldr command as follows

sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5  skip=1

Keywords: SQL*LOADER, ERRORS, SKIP, LOAD

Load multiple user datafiles into multiple tables using SQL*LOADER

On request from one of the reader, below are the steps to load multiple tables using multiple datafiles.
Problem Description
-------------------
You have multiple datafiles to be loaded using SQL*Loader. Each of the data
files contains records that must be loaded into one of several tables. The
following is an example that uses the INFILE clause for each datafile and a
WHEN clause for each table to do this.

Records in a datafile that are to be loaded into multiple table can be loaded
based on a check for a value that distinguishes each record. This check can be
on an entire column or on a specific position within the data file.

Solution Description
--------------------
Example:
Datafiles are generated each day that contain multiple employees and the
projects those employees worked on that day. Projects can be worked on from
many locations, and an employee can work on many projects each day. Each week
all the daily files are gathered and loaded into project tables based on
location.
-----------------------Table Create Statements---------------
CREATE TABLE DENVER_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE ORLANDO_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE MISC_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

-------------------Control File - MFILES.CTL------------------
LOAD DATA
INFILE '/u01/projs/denver.dat'
INFILE '/u01/projs/orlando.dat'
APPEND

INTO TABLE DENVER_PRJ
WHEN PROJNO = '101'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE ORLANDO_PRJ
WHEN PROJNO = '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE MISC_PRJ
WHEN PROJNO != '101' AND PROJNO != '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )
--------------------Datafiles: DENVER.DAT---------------------
1011234515
1015432140
1012345620
3032345610
--------------------Datafiles: ORLANDO.DAT--------------------
2021234515
2022345610
4041234510
--------------------------------------------------------------
SQL*Loader will read all the input files together. It will parse each record
and then based on the condition(s) in a WHEN clause will evaluate whether the
record can be loaded into that table. Care should be taken as to how WHEN
conditions are constructed because each record is evaluated against every WHEN
clause and loaded into all tables that match the condition.

Reference: Metalink Note: 1023792.6

Tips and Tricks with SQL Loader

Following are some of the tips and tricks that can be used with SQL Loader

1) Load text for a column which is having more than 4000 bytes.
Use following syntax

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)
)

2) How to use oracle functions with SQL Loader

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)   --- first 40000 characters
, column1  "trim(:column1)"       --- Trims and loads
, column2  "replace(:column2,'\n',chr(10))"  --- replace \n with new line
, column3  DATE "DD-MON-YYYY"   --- defining date style
, column4  "upper(:column4)"      --- changing to upper case
, column5  "lower(:column5)"      --- changing to lower case
, column6  constant "FIXED"      --- assigning a constant value
, column7  "sv_sequence.nextval"  --- defaulting a value from sequence
)

Thats it for today .. will update with more commands later.

Tips for SQLLOADER

Following are some of the tips and tricks that can be used with SQL Loader

1) Load text for a column which is having more than 4000 bytes.
Use following syntax

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)
)

2) How to use oracle functions with SQL Loader

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)   --- first 40000 characters
, column1  "trim(:column1)"       --- Trims and loads
, column2  "replace(:column2,'\n',chr(10))"  --- replace \n with new line
, column3  DATE "DD-MON-YYYY"   --- defining date style
, column4  "upper(:column4)"      --- changing to upper case
, column5  "lower(:column5)"      --- changing to lower case
, column6  constant "FIXED"      --- assigning a constant value
, column7  "sv_sequence.nextval"  --- defaulting a value from sequence
)