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

How to copy objects from one environment to other in LSH

 In LSH Version 2.2, The Oracle LSH export and import utility allows you to
export all or part of a single Domain to a file and then read from the file to import the
included objects to an Oracle LSH instance—either the same one or a different
one—for example, to:

> Set up a new Oracle LSH instance within your organization.
> Make an Oracle LSH application that you developed available to others.
> Provide upgrades to previously imported objects.

The export and import utility includes three scripts:
> cdrruexport.sql exports objects to a file.
> cdrruimport.sql imports objects from a file.
> cdrruwainstall.sql installs imported objects.

The scripts are stored in the following location by default:
$CDR_TOP/patch/115/sql/script_name.sql

You must run these scripts from the command line on the computer where the Oracle
LSH database server is installed. There is no user interface to run these scripts.

Note: This utility imports and exports metadata. To export data out
of Oracle LSH, use Oracle LSH Data Marts. See "Defining Data Marts".

The following objects are not exported:

> All validation supporting information including all supporting outputs and
supporting documents.
> The classification hierarchy assigned to any object. On import, the default of the
target Oracle LSH instance's classification is used for that object type.
> The subtype of any object. On import the default subtype of the target Oracle
LSH instance is used for each object type.
> Checkin and checkout comments.
> Execution and Work Area installation data (jobs, outputs, data).
> All other object types: Adapter Domains, Adapter Areas, Workflows, Report Sets,
Data Marts.
> Other Oracle LSH metadata: Users, User Groups, Roles, Subtypes, Adapter
Security, Database Accounts, Remote Locations and Connections, Service
Locations, Hierarchies, Terms, and Subtypes.

New Bug in LSH ver 2.1.4.4

It looks like there is a bug in LSH 2.1.4.4 w.r.t installations.

Bug Description:
If you install a program instance, it is getting installed 2 times. In the installation history, you will be able to see 2 installations. This causing each installation time to be more than 30 minutes.
At the end of the installation, it is not showing the message like ‘The program installed successfully.’. But the LSH instance freezes and you will not be able to navigate further.
Currently, I observed this behavior w.r.t program instances only. This issue does not occur with table instances’ installation.

IMPACT:
This will have serious impact on the performance.