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;

/