Project and Portfolio Management Practitioners Forum
cancel

Call Store Procedure

Highlighted
CHorta
Frequent Contributor.

Call Store Procedure

Hi,

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

 

Thank you.

 

3 REPLIES
Utkarsh_Mishra
Acclaimed Contributor.

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
Frequent Contributor.

Re: Call Store Procedure

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

Thank you.
Utkarsh_Mishra
Acclaimed Contributor.

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)