Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 214 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
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?
Answers
-
-
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."
0 -
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.
0 -
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,
0 -
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.
0 -
Hi ,
Could you please post your wrapper code for this req ?
0