1

how do I import oracle table function as physical table in Oracle BI EE ? Let's say it is

select * from scheme.table_function1(adate)

What is correct syntaxis for setting it as BI physical table? How do I specify input parameter (adate)?

Marchello
  • 65
  • 1
  • 3
  • 11

1 Answers1

1

Create a new physical table manually in the repository, set the table type to "Select" and include your sql query.

Are you using a repository variable as input parameter? Assuming ADATE is a repository string variable, the syntax would be

select * from scheme.table_function1(  'valueof(ADATE)'  )

You will have to manually add the columns to the new physical table and make sure they match with the sql results.

Links:
Opaque views
Variable Syntax

Victor HDC
  • 525
  • 1
  • 6
  • 12
  • Victor HD, great, I'll try it at office ) – Marchello Dec 10 '12 at 21:51
  • Victor HDC, it works, but I set input parameters for pipelined function manually. Still can not pass parameters using variable. Any hints? – Marchello Dec 11 '12 at 08:37
  • Looks like good OBIEE variables manual, trying to go... http://gerardnico.com/wiki/dat/obiee/set_request_variable_dashboard_prompt – Marchello Dec 11 '12 at 09:07
  • Are you using a session variable? It has to be a repository variable. Please include the syntax you are using so I can test it – Victor HDC Dec 11 '12 at 17:31
  • 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? – Marchello Dec 13 '12 at 08:27
  • Tried SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export(aquarter,ayear)) but got error: There was an error while updating row count for "B2ORACLE.WORLD".."ORACLEBI"."cash_limits": [nQSError: 17011] SQL statement execution failed. [nQSError: 17001] Oracle Error code: 904, message: ORA-00904: "AQUARTER": invalid identifier at OCI call OCIStmtExecute: select count(*) from (SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export(aquarter,ayear))) T1. – Marchello Dec 13 '12 at 08:38
  • Tried SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export('valueof(aquarter)','valueof(ayear)')) and it gives no error, but in this case query doesn't return any data. – Marchello Dec 13 '12 at 08:47
  • This way works in repository: SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export('valueof(aquarter)','valueof(ayear)')) – Marchello Dec 13 '12 at 09:42
  • can see result in repository, but Analysis gives 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 – Marchello Dec 13 '12 at 12:31
  • 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 – Marchello Dec 13 '12 at 12:43
  • Do you have init blocks for variables aquarter and ayear? If you don't, try assinging default values to both of them. (In the admin tool go to Manage / Variables) – Victor HDC Dec 13 '12 at 16:22
  • You don't need single quotes for numeric values so the syntax could be SELECT * FROM TABLE(ORACLEBI.PKG_CASH_LIMITS.fn_b2export( valueof(aquarter), valueof(ayear) ) ) this might avoid unnecessary number conversions – Victor HDC Dec 13 '12 at 16:39
  • It looks like you need the table to respond to user input, If that is the case you must use session variables but according to the guide you cannot create opaque views with session variables. If you want to try, create a SESSION variable named ADATE2 and change the syntax to VALUEOF(NQ_SESSION.ADATE2) instead of VALUEOF(ADATE) – Victor HDC Dec 13 '12 at 18:41
  • As an alternative, you could execute that function for all the quarters of the current year and save the results in a table and use that as data source. – Victor HDC Dec 13 '12 at 18:44