How to call a stored procedure in BI Publisher Data model

cool2sathish

Summary

How to call a stored procedure in BI Publisher Data model

Content

Hi Everyone,

My BI publisher would like to perform in a way that users will input the required details and then it will go through the stored procedure and generate the outcome, which would then be use for BIP to generate the report in pdf format.

Could someone help me in calling a stored procedure in a dataset of a BI Publisher 11g? I have read few postings on the web but none of them worked for me.

I am calling in the following way:

SET serveroutput ON;

DECLARE

  P_BOARD_NAME    VARCHAR2(200);

  P_INCIDENT_NAME VARCHAR2(200);

  P_START_DATE DATE;

  P_END_DATE DATE;

  p_Cursor SYS_REFCURSOR;

 

  P_TOTAL_INCIDENTS         NUMBER;

  P_DISTRICT                VARCHAR2(200);

  P_ROUTES_OPEN             NUMBER;

  P_ROUTES_CLOSED_BLOCKED   NUMBER;

  P_ROUTES_PASSABLE         NUMBER;

  P_ROUTES_EXTENDED_CLOSURE NUMBER;

  --P_ROUTES_EXTENDEDSTAY    NUMBER;

  P_ROUTES_UNVERIFIED     NUMBER;

  P_ROUTES_WITH_NO_STATUS NUMBER;

BEGIN

  P_BOARD_NAME    := :param_1;

  P_INCIDENT_NAME := :param_2;

  P_START_DATE    := :param_3;

  P_END_DATE      := :param_4;

  GDOT_WEBEOC.RPT_EXECV_SUMRY_CURRENT_STATUS( P_BOARD_NAME, P_INCIDENT_NAME, P_START_DATE, P_END_DATE, P_CURSOR );

  -- Modify the code to output the variable

  LOOP

    FETCH P_CURSOR

    INTO P_BOARD_NAME,

      P_INCIDENT_NAME,

      P_TOTAL_INCIDENTS,

      P_DISTRICT,

      P_ROUTES_OPEN,

      P_ROUTES_CLOSED_BLOCKED,

      P_ROUTES_PASSABLE,

      P_ROUTES_EXTENDED_CLOSURE,

      /*P_ROUTES_EXTENDEDSTAY,*/

      P_ROUTES_UNVERIFIED,

      P_ROUTES_WITH_NO_STATUS;

    EXIT

  WHEN P_CURSOR%notfound;

    DBMS_OUTPUT.PUT_LINE(P_BOARD_NAME||','||P_INCIDENT_NAME||','||P_TOTAL_INCIDENTS||','||P_DISTRICT||','||P_ROUTES_OPEN||','||P_ROUTES_CLOSED_BLOCKED||','||P_ROUTES_PASSABLE||','||P_ROUTES_EXTENDED_CLOSURE||','||P_ROUTES_UNVERIFIED||','||P_ROUTES_WITH_NO_STATUS);

  END LOOP;

  CLOSE P_CURSOR;

END;

it worked in the SQL developer, but when I use this in the data set it's throwing an error.

Thanks in advance..

Youssef_B_82

Hi,

Have you tried creating a package to store your procedure and call package using the Event Trigger?

Also. Make sure all your variables (parameters) from the data model are declared in your package, even if not used.

Youssef

cool2sathish

Thanks for your reply Youssef!!!

below is the error I'm getting when I try to generate the xml.

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]Bind Variables ...

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]    1: pv_board:Hurricane

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]Bind Variables ...

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]    2: pv_incident:Hurricane

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]Bind Variables ...

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]    3: pv_start_date:01-01-1990

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]Bind Variables ...

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]    4: pv_end_date:10-09-2016

[171115_12320012][DATA PROCESSOR ID:309002466][SCH JOB INFO:]oracle.xdo.dataengine.datasource.plugin.DataAccessException: java.sql.SQLException: SQL string is not Query

Youssef_B_82

Hi,

what's the error you  are getting?

Regards

Youssef

Carlos Carvalho

Hello,

Just adding a little more info to @Youssef_B_82 reply:

Add the variables (parameters) in package spec and the procedure must return boolean type.

Regards,

Carlos

Carlos Carvalho

In data set you pass the parameters to package as in image above (where is 'set value' in blue) and click 'view' to run the query and get a sample.

Just be sure to have ALL parameters in package spec.

Carlos Carvalho

Hello,

As referred in previous reply, the procedures you declare in package to be called in event trigger must return boolean type.

Check this for more info: https://www.rittmanmead.com/blog/2011/12/event-triggers-in-bi-publisher-11g/

The parameters are passed in data set (you must declare them in package spec) if you provide the values when testing :

You can read the following for other types of sql in data sets: https://docs.oracle.com/middleware/1221/bip/BIPDM/create_data_sets.htm#BIPDM482

Regards,

Carlos

cool2sathish

Thank you very much for the detailed information!

Can you please give me the syntax/example how the parameters are passed in data set.

I'm actually creating the report using package first time.. all your inputs are valuable for me.

cool2sathish

Thank you for your reply!!!

I have created below the Package and have the steps to add the event trigger.

can you tell me what type of SQL have to execute and how to pass the parameter in the data set.

create or replace PACKAGE             PKG_RPT_EXECUTIVE_SUMMARY

AS

   PROCEDURE RPT_EXECV_SUMRY_CURRENTSTATUS (

  p_board_name  IN  varchar2,

  p_incident_name IN varchar2,

  p_start_date IN date,

  p_end_date IN date,

  p_cursor  OUT sys_refcursor);

end PKG_RPT_EXECUTIVE_SUMMARY;

cool2sathish

In Data set can I write a Procedural SQL like below to call the package spec parameters??

Please confirm...

DECLARE

  P_BOARD_NAME    VARCHAR2(200);

  P_INCIDENT_NAME VARCHAR2(200);

  P_START_DATE DATE;

  P_END_DATE DATE;

  p_Cursor SYS_REFCURSOR;

 

  P_TOTAL_INCIDENTS         NUMBER;

  P_DISTRICT                VARCHAR2(200);

  P_ROUTES_OPEN             NUMBER;

  P_ROUTES_CLOSED_BLOCKED   NUMBER;

  P_ROUTES_PASSABLE         NUMBER;

  P_ROUTES_EXTENDED_CLOSURE NUMBER;

  --P_ROUTES_EXTENDEDSTAY    NUMBER;

  P_ROUTES_UNVERIFIED     NUMBER;

  P_ROUTES_WITH_NO_STATUS NUMBER;

BEGIN

  P_BOARD_NAME    := :param_1;

  P_INCIDENT_NAME := :param_2;

  P_START_DATE    := :param_3;

  P_END_DATE      := :param_4;

  GDOT_WEBEOC.RPT_EXECV_SUMRY_CURRENT_STATUS( P_BOARD_NAME, P_INCIDENT_NAME, P_START_DATE, P_END_DATE, P_CURSOR );

  -- Modify the code to output the variable

  LOOP

    FETCH P_CURSOR

    INTO P_BOARD_NAME,

      P_INCIDENT_NAME,

      P_TOTAL_INCIDENTS,

      P_DISTRICT,

      P_ROUTES_OPEN,

      P_ROUTES_CLOSED_BLOCKED,

      P_ROUTES_PASSABLE,

      P_ROUTES_EXTENDED_CLOSURE,

      /*P_ROUTES_EXTENDEDSTAY,*/

      P_ROUTES_UNVERIFIED,

      P_ROUTES_WITH_NO_STATUS;

    EXIT

  WHEN P_CURSOR%notfound;

    DBMS_OUTPUT.PUT_LINE(P_BOARD_NAME||','||P_INCIDENT_NAME||','||P_TOTAL_INCIDENTS||','||P_DISTRICT||','||P_ROUTES_OPEN||','||P_ROUTES_CLOSED_BLOCKED||','||P_ROUTES_PASSABLE||','||P_ROUTES_EXTENDED_CLOSURE||','||P_ROUTES_UNVERIFIED||','||P_ROUTES_WITH_NO_STATUS);

  END LOOP;

  CLOSE P_CURSOR;

END;

Carlos Carvalho

One step a time.

It´s necessary to understand how things work, first.

Lets try a simple example:

select languagecode --someColumn; i used languagesfrom ng_languages; --yourTableNamecreate or replace PACKAGE packageTest AS     --declare a simple package with a function (no parameters for now)  TYPE refcursor IS REF CURSOR;  FUNCTION GET RETURN  refcursor;  --will return a cursor with all results  END;/  create or replace PACKAGE BODY packageTest AS -- package body with a function (no parameters for now)  FUNCTION GET  RETURN refcursor     --will return a cursor with all results  IS  l_cursor refcursor;  BEGIN           OPEN l_cursor FOR         select languagecode     --if you used another colum from select statement above change here too        from ng_languages;--if you used another table from select statement above change here too     RETURN l_cursor;   END GET;  END packageTest;

in data set add a 'SQL Query' and write the pacakage function method call:

SELECT packageTest.GET AS CURDATA FROM DUAL

to have something like:

and will be like :

and test it to see result:

Next step is to add a parameter. But complete this first and let me know if any problems occur.

Regards,

Carlos

P.S. - i am not using your example cause i am not a dba guy (i have some lights but i have help from my dba for building/improve querys), therefore i will use this to show you the mechanism. You´ll have to adapt it to your use case yourself