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

Appsupport

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

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

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

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

please use ':' colonmark before variable in publisher

Appsupport

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

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

Appsupport

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)

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

Please suggest on this.

Many Thanks.

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