How to print information in the log file?

Use the below atonomous transaction to print messages in the LSH job log file. You need to get important job information like company id and job id by using getJobInfo(); before calling this procedure.

PROCEDURE print (
pi_cMessage IN VARCHAR2 -- message that needs to printed in the log file
)
IS PRAGMA AUTONOMOUS_TRANSACTION;
vc_message VARCHAR2(4000);
vc_returnStatus VARCHAR2(1);
vn_msg_count NUMBER;
vc_msg_data VARCHAR2(4000);
BEGIN

SELECT TO_CHAR(sysdate, 'DD-MON-RRRR hh24:mi:ssss')
INTO vc_message
FROM dual;

vc_message := vc_message || ' - ' || pi_cMessage;

CDR_PUB_EXE_SUBMISSION.addjoblogentry(P_API_VERSION => 1
,P_INIT_MSG_LIST => CDR_PUB_DEF_CONSTANTS.G_FALSE
,P_COMMIT => CDR_PUB_DEF_CONSTANTS.G_TRUE
,P_VALIDATION_LEVEL => CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
,X_RETURN_STATUS => vc_returnStatus
,X_MSG_COUNT => vn_msg_count
,X_MSG_DATA => vc_msg_data
,PI_NCOMPANYID => 'Give company id here'
,PI_NJOBID => 'Give job id here'
,PI_VLOGENTRY => vc_message
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END print;

How to get a Job Info in LSH?

The procedure, getJobInfo gets the important job information by giving a job id as input. All the important information is printed in below the api.

PROCEDURE getJobInfo (pi_nJobId IN NUMBER)
IS
vc_return_status VARCHAR2(1);
vn_msg_count NUMBER;
vc_msg_data VARCHAR2(4000);
vr_job_rec CDR_JOBS_V%ROWTYPE;
vr_sub_rec CDR_SUBMISSIONS_V%ROWTYPE;
BEGIN

cdr_pub_exe_runtime.getjobinfo(P_API_VERSION => 1
,P_INIT_MSG_LIST => CDR_PUB_DEF_CONSTANTS.G_FALSE
,P_COMMIT => CDR_PUB_DEF_CONSTANTS.G_FALSE
,P_VALIDATION_LEVEL => CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
,X_RETURN_STATUS => vc_return_status
,X_MSG_COUNT => vn_msg_count
,X_MSG_DATA => vc_msg_data
,PI_NJOBID => pi_nJobId
,PO_RSUBMISSION_V => vr_sub_rec
,PO_RJOB_V => vr_job_rec
);
IF vc_return_status <> 'S'
THEN
print('ERROR: error occured in cdr_pub_exe_runtime.getjobinfo'||vc_msg_data);
display_api_errors;
END IF;

print('vr_job_rec.company_id : '||vr_job_rec.company_id);
print('vr_job_rec.obj_id : '||vr_job_rec.obj_id);
print('vr_job_rec.obj_ver : '||vr_job_rec.obj_ver);
print('vr_job_rec.namespace_obj_id : '||vr_job_rec.namespace_obj_id);
print('vr_job_rec.namespace_obj_ver : '||vr_job_rec.namespace_obj_ver);
print('vr_job_rec.namespace_name : '||vr_job_rec.namespace_name);
print('vr_job_rec.prref_id : '||vr_job_rec.prref_id);
print('vr_job_rec.prref_ver : '||vr_job_rec.prref_ver);
print('vr_job_rec.master_prref_id : '||vr_job_rec.master_prref_id);
print('vr_job_rec.full_reload_flag_rc : '||vr_job_rec.full_reload_flag_rc);
print('vr_job_rec.blind_break_flag_rc : '||vr_job_rec.blind_break_flag_rc);
print('vr_sub_rec.execution_setup_obj_id: '||vr_sub_rec.execution_setup_obj_id);
print('vr_sub_rec.execution_setup_obj_ver: '||vr_sub_rec.execution_setup_obj_ver);
print('vr_sub_rec.wa_obj_id : '||vr_sub_rec.wa_obj_id);
print('vr_sub_rec.force_execution_flag_rc: '||vr_sub_rec.force_execution_flag_rc);
print('vr_sub_rec.execution_priority_rc : '||vr_sub_rec.execution_priority_rc);

EXCEPTION
WHEN OTHERS THEN
print('ERROR: '||SUBSTR(SQLERRM,1,200));
END getJobInfo;

How to get the currently executing Job Id?

Use the below PL/SQL block to get the currently executing job id.

DECLARE
v_job_id NUMBER;
BEGIN
v_job_id := CDR_PUB_EXE_RUNTIME.getCurrentlyExecutingJobId(1);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

LSH Programming Tutorial

 

In this tutorial we learn LSH Programming techniques. We start with the fundamentals of the LSH programming and move on to advanced programming techniques. This tutorial deals with programmatical way of creating objects in LSH, deriving metadata from LSH and modifying objects.

Intended Audience:
LSH Developers, Programmers and Support Engineers

Pre-Requisite:

Must have hands on experience in PL/SQL programming. Must have used PL/SQL collections, arrays and nested tables.

Contents:
1. How to get the currently executing job id
2. How to initialize job log
3. How to get currently executing user id
3. How to get currently executing user name