This content has been marked as final. Show 8 replies
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
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?
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)
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