8 Replies Latest reply: Dec 19, 2012 1:34 PM by Srini VEERAVALLI RSS

    using variable in repository OBIEE

    marco
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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