Tuesday, July 13, 2010

SQL*Loader Program Code

Your Ad Here


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;
Your Ad Here

0 comments:

Post a Comment