This content has been marked as final. Show 8 replies
Please go through the link: http://obieetutorialguide.blogspot.in/2012/02/using-variables-in-obiee.html
Hope, it helps.
I can see query result in repository, but Analysis gives me error:
Help me please, I'm stuck.
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
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
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.
1. I've created non-system session variable:
2. I use initialization block:
3. I've set my variable in pipelined function:
4. Got error during run "View data" in Physical section of Oracle BI Administration Tool:
What is wrong?
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.
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:
and then filtered query result using OBIEE Analysis prompts on columns.
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
Thanks to all.
If you look at one your post related same (more or less)1 person found this helpful
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