CHorta Absent Member.
Absent Member.
479 views

Call Store Procedure

Hi,

How can I call a stored procedure with scheduling from ppm?

 

Thank you.

 

0 Likes
3 Replies
Established Member.. Utkarsh_Mishra
Established Member..

Re: Call Store Procedure

You can schedule it using PPM report.

 

Create SQL script file which call the Procedure... (see below is example)

And then call this SQL script using PPM report command. (see example below)


SQL Script.. (example take from HP out of the box script)

 

SET SERVEROUTPUT ON;
SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
DEFINE username='&1';
DEFINE file_name='&2';
DEFINE sub_path='&3';
DEFINE application='&4';
BEGIN
KVER_UTILS.CI_CHECK('&username','&file_name','&sub_path','&application');
END;
/
EXIT;

 

Report Command

 

if JSP Report -

 

ksc_run_plsql_procedure <PROCEDURE_NAME>
v_user_id.INTEGER.IN=[RP.CREATED_BY]
v_sub_id.INTEGER.IN=[RP.REPORT_SUBMISSION_ID]
o_message_type.INTEGER.OUT
o_message_name.VARCHAR.OUT
o_message.VARCHAR.OUT
ksc_end_plsql_parameters

ksc_run_jsp_report /web/knta/rpt/demo.jsp
REPORT_ID=[RP.REPORT_SUBMISSION_ID]
USER_ID=[RP.CREATED_BY]
OUT_BASEFILE=[RP.FILENAME]
ksc_end_report_parameters

 

OR (if using script)

 

ksc_local_exec [AS.ORACLE_HOME]/bin/[AS.SQLPLUS] [AS.DB_USERNAME]/[AS.DB_PASSWORD]@[AS.DB_CONNECTION_STRING] @./scripts/kdshrpt_data_source_detail '[AS.REPORT_DIR]' '[RP.FILENAME]' '[P.SUB_PATH]' '[P.APPLCIATION]'

 

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
CHorta Absent Member.
Absent Member.

Re: Call Store Procedure

Hi Utkarsh,
To meet this requirement (call SP from PPM) is needed license to PPM Reports?

Thank you.
0 Likes
Established Member.. Utkarsh_Mishra
Established Member..

Re: Call Store Procedure

Yes, you should have any PPM license  to run the report.

 

You can define the license type required in the Report by setting value in "Requires License" field.

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.