Oracle Analytics and AI

Calling a stored procedure in BI Publisher Datamodel

Summary

Calling a stored procedure in BI Publisher Datamodel

Content

Hi All,

I want to call a Oracle Stored Procedure with output parameters in Oracle BI Publisher DataModel. Any idea on how to use the 'Procedure call/Non Standard SQL' for achieving this?

Find more posts tagged with

Reporting

Accepted answers

All comments

YGUTTIKONDA

https://docs.oracle.com/cd/E28280_01/bi.1111/e22258/create_data_sets.htm#BIPDM149

Dir_Pal

it is possible using Event Triggers. This feature is available from version 11.1.1.6. You can refer documentation here https://docs.oracle.com/cd/E23943_01/bi.1111/e22258/add_event_trig.htm#BIPDM331

Update: As per documentation only functions are allowed. "The BI Publisher data model supports before data and after data triggers that execute a PL/SQL function stored in a PL/SQL package in your Oracle Database. The return data type for a PL/SQL function inside the package must be a Boolean type and the function must explicitly return TRUE or FALSE."

Dir_Pal

IF u want to get the output u cant call the stored procedure with out parameter . U have to convert the procedure to function to get the output.

Rajesh123

Hi ,

You are in EBS correct ? if you want call your procedure then use like below.

call the procedure like below where ever you want...

Get_contact( contact_id, fname, lname, attn, mail_stop1 );

out variables,

first_name := fname;

last_name := lname;

ship_to_attn := attn;

mail_stop := mail_stop1;

you should let me know your requirement,

Surya Muralidharan-78192

I have written a wrapper function that will call the stored procedure and return true or false and this function I was able to call in the BI Publisher Triggers and use it in Datamodel. Thanks much for the immediate help.

venkat ram-143549

Hi ,

Could you please post your wrapper code for this req ?