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