This discussion is archived
8 Replies Latest reply: Dec 19, 2012 11:34 AM by Srini VEERAVALLI RSS

using variable in repository OBIEE

marco Newbie
Currently Being Moderated
Hi all,

This is my initialization string of physical table (select typ) in repository (hardcoded) :
SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export(2,2012)) 
I have two repository variables: aquarter and ayear. How do I set variables instead of hardcoded values?

Tried
SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export('valueof(aquarter)','valueof(ayear)')‚Äč) 
and it gives no error, but also this query doesn't return any data.


Upd: yes, default values for repository variables aquarter and ayear are set (but still no result)
  • 1. Re: using variable in repository OBIEE
    950217 Newbie
    Currently Being Moderated
    Hi,

    Please go through the link: http://obieetutorialguide.blogspot.in/2012/02/using-variables-in-obiee.html

    Hope, it helps.

    Regards
    Raj Kumar
  • 2. Re: using variable in repository OBIEE
    marco Newbie
    Currently Being Moderated
    I can see query result in repository, but Analysis gives me error:
    Odbc driver returned an error (SQLExecDirectW). Error Details Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 1722, message: ORA-01722: invalid number at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000) SQL Issued: SELECT 0 s_0, "VOLKSBANK"."cash_limits"."ACCNAME" s_1 FROM "VOLKSBANK" FETCH FIRST 2000001 ROWS ONLY
    Help me please, I'm stuck.
  • 3. Re: using variable in repository OBIEE
    marco Newbie
    Currently Being Moderated
    It seems to me that parameter is not passed from Analysis into repository. I think so because when I try to pass characters instead of numbers to my pipelined function I got the same error:
    select * from table(ORACLEBI.PKG_CASH_LIMITS.FN_B2EXPORT('d','t')) * 
    Error at line 1 ORA-01722: invalid number
  • 4. Re: using variable in repository OBIEE
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Are you trying to pass variables in Physical layer? I dont think it'll work.. correct me if I'm wrong.
    I think the only place to use variable in Physical layer is Connection Pool->Connection Scripts tab.
  • 5. Re: using variable in repository OBIEE
    marco Newbie
    Currently Being Moderated
    1. I've created non-system session variable:
    http://s10.postimage.org/4tm0zzfvt/variable_191212_0001a.png

    2. I use initialization block:
    http://s11.postimage.org/i5kg4nlv7/variable_191212_0002a.png

    3. I've set my variable in pipelined function:
    http://s8.postimage.org/qqlmp1dp1/variable_191212_0003a.png

    4. Got error during run "View data" in Physical section of Oracle BI Administration Tool:
    http://s7.postimage.org/yr4tc5m9n/variable_191212_0004a.png

    What is wrong?
  • 6. Re: using variable in repository OBIEE
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I dont think we can refer repository variables as you are using... The only place to refer variables is
    Connection Pool->Properties->Connection Pool scripts

    Pls correct me if I'm wrong.

    I'm not sure about your step/image at 2.
  • 7. Re: using variable in repository OBIEE
    marco Newbie
    Currently Being Moderated
    Well, the question is resolved for this moment. Honestly I don't like it, but it works. I've created view with nested pipelined function:
    create or replace view oraclebi.vcash_limits as 
    with quarts as 
    (
    select 1 as q from dual union all
    select 2 as q from dual union all
    select 3 as q from dual union all
    select 4 as q from dual 
    ), 
    years as 
    (
    select extract(year from sysdate) r  
    from dual
    union all 
    select extract(year from sysdate) - 1 r  
    from dual 
    union all
    select extract(year from sysdate) - 2 r  
    from dual  
    ),
    prompts as (
    select quarts.q, years.r from quarts, years 
    ),
    tsql as (
    SELECT t.q, t.r, d.* FROM prompts t, 
    TABLE(ORACLEBI.PKG_CASH_LIMITS.FN_B2EXPORT(t.q,t.r)) d/*(+)*/
    )
    select * from tsql t
    and then filtered query result using OBIEE Analysis prompts on columns.

    Thanks to all.
  • 8. Re: using variable in repository OBIEE
    Srini VEERAVALLI Guru
    Currently Being Moderated
    If you look at one your post related same (more or less)
    table function as physical table in Oracle BI EE

    with heading table function as physical table in Oracle BI EE

    we were guiding you to go with database views as you've done for this post.

    Edited by: Srini VEERAVALLI on Dec 19, 2012 1:34 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points