Oracle Analytics Cloud and Server

OBIEE Data Model --> Event trigger with date parameters failed

Received Response
134
Views
6
Comments

Summary

OBIEE Data Model --> Event trigger with date parameters failed

Content

Hi,

I am developing a report with Event trigger in OBIEE, in that i would like to pass the date parameters which are provided during report refresh to the function in default package.

How to pass the variables to this function, please help on this

image

right now i am getting the error as shown in below screen shot

image

***Moderator note (Timo): moved this post from to ​ for better alignment.***

Answers

  • Thomas Dodds
    Thomas Dodds ✭✭✭✭✭

    I have set this up many times ...

    1 - make sure you pass in ALL the parameters (regardless if you are using them in your procedure)

    2 - make sure your procedure is set up to accept ALL parameters (regardless if you are using them in your procedure)

    3 - make a package spec similar to:

    create or replace PACKAGE              CM_REPORT_PKG

    IS

      PROCEDURE doSomething(

          p_acct_id VARCHAR2);

      FUNCTION beforereporttrigger

        RETURN  BOOLEAN;

      p_acct_id VARCHAR2(15) ;

      p_strt_dt date;

      p_end_dt date;

    END CM_REPORT_PKG

    NOTE:  I will take all parameters, use only the account id and (doSomething procedure will) write the data to a table, then in the data model I select from the table and use the dates to filter ...

    4 - make package body similar to:

    create or replace PACKAGE BODY                 CM_REPORT_PKG

    IS

    PROCEDURE doSomething(

          p_acct_id IN VARCHAR2)

    END;

      FUNCTION beforereporttrigger

        RETURN BOOLEAN

      AS

      BEGIN

        doSomething(p_acct_id);

        RETURN TRUE;

      EXCEPTION

      WHEN OTHERS THEN

        -- DBMS_OUTPUT.PUT_LINE(SQLERRM);

        RETURN FALSE;

      END;

    END CM_REPORT_PKG;

  • 842395
    842395 ✭✭✭

    please use ':' colonmark before variable in publisher

  • Thank you very much.

    This is working for my requirement

    I have created a separate procedure in my package with global variables p_from_date date, p_to_date date

    procedure getdates(p_from_date date, p_to_date date) as

      begin

        execute immediate 'truncate table date_range';

        insert into date_range values (p_from_date, p_to_date);

        commit;

      end getdates;

    and in my event trigger function i am calling internally the below procedure for fetching the dates which we requested.

        getdates(P_FROM_DATE, P_TO_DATE);

  • Sketz
    Sketz ✭✭✭

    PKG_REPORT_AUTO_DETAILS.RM_WISE_SUMMARY_FOR_ALL_PRODS(P_FROM_DATE:DATE , P_TO_DATE:DATE)

    Why are you writing that :DATE behind the parameters?

    PKG_REPORT_AUTO_DETAILS.RM_WISE_SUMMARY_FOR_ALL_PRODS(:P_FROM_DATE, :P_TO_DATE)

    This would be the right call.

    The ':' works as a reference to an data model parameter, not as a data type qualifier.

    Otherwise you could create your function without any parameters. Since you need to declare all data model parameters as public variables in the package, the data model can handle the definition on its own.

    But this makes it harder to read for someone else, so I think, you should stay with the first variant

  • Hi Friends,

    Thanks for your suggestions and observations,

    My requirement is to pass the date parameters into that function , based on that dates i would like to retrieve the data from DB and present in my report.

    For that i have defined two global variables in my PLSQL package P_FROM_DATE and P_TO_DATE, and also the same variables are available under OBIEE data model as well.

    Now my challenge is, how to pass the variable values from data model to plsql function through event trigger function.

    when i am selecting the respective function from my default package it comes with the two date variables

    PKG_REPORT_AUTO_DETAILS.RM_WISE_SUMMARY_FOR_ALL_PRODS(P_FROM_DATE:DATE , P_TO_DATE:DATE)

    image

    And then when i am refreshing the sample data i am getting the below error

    image

    Please suggest on this.

    Many Thanks.

  • Sketz
    Sketz ✭✭✭

    Hello Thomas,

    You're doing this way too complicated.

    You don't have to pass all the parameters from your Data Model to the package function... they just have do be defined in the package.

    This makes the package interface much clearer

    Compare the Doc:

    If you define a default package then you must define all parameters as a global PL/SQL variable in the PL/SQL package. You can then explicitly pass parameters to your PL/SQL function trigger or all parameters are available as a global PL/SQL variable. https://docs.oracle.com/cd/E23943_01/bi.1111/e22258/add_event_trig.htm#BIPDM334

    I also wonder why you use a procedure called by the function when nothing more happes inside the function.

    Everything could be done inside the function and so you could get rid of the whole procedure