This discussion is archived
11 Replies Latest reply: Dec 11, 2012 12:33 AM by marco RSS

table function as physical table in Oracle BI EE

marco Newbie
Currently Being Moderated
Hi all,

how do I import oracle table function as physical table in Oracle BI EE ? Let's say it is
select * from scheme.table_function1(sysdate)
What is correct syntaxis for setting it as BI physical table? How do I specify input parameter (sysdate)?

Is it possible at all?
  • 1. Re: table function as physical table in Oracle BI EE
    prassu Pro
    Currently Being Moderated
    Hi marco,
    Open the table properties in the physical layer---->general tab-->table type-->here you can use the select statement,stored procedure,physical table.

    based on your requirement select the option.

    mark if helpful/correct.

    thanks,
    prassu
  • 2. Re: table function as physical table in Oracle BI EE
    marco Newbie
    Currently Being Moderated
    prassu, thanks for your reply.

    I still can not understand the conception. Is it possible to create BI EE report with input parameter (let's say it is ADATE) for table function, (pipelined function in other words) ?

    ADATE in report is used not in WHERE clause, but as table (pipelined) function parameter.

    Could someone provide step-by-step explanation on how to use it in BI EE ? Sure if it is possible at all.

    Upd: As far as I can read it here - yes, it is possible:
    http://oraclebizint.wordpress.com/2007/09/21/oracle-bi-ee-101323-ref-cursors-and-pipelined-functions/

    But the problem is I can not find how to configure repository properly, I mean step-by-step.

    Edited by: marco on Dec 5, 2012 10:06 AM
  • 3. Re: table function as physical table in Oracle BI EE
    Srini VEERAVALLI Guru
    Currently Being Moderated
    The best way is using database views, try to create a db view using required functions and import into the rpd.
    or else you can use the view select statement in the rpd as Prassu said.

    You need to select 'Allow direct database requests by default' for Physical layer's(top most object without collapse) database object, for the same you properties to set that.

    If helps pls mark

    Edited by: Srini VEERAVALLI on Dec 5, 2012 12:15 PM
  • 4. Re: table function as physical table in Oracle BI EE
    marco Newbie
    Currently Being Moderated
    Srini VEERAVALLI,

    creating view with nested pipelined function is... let's say, not ideal decision. It is hardcode. The view will always return the same result depending on input parameter you saved in view.

    Let us say I'd like to query
    select * from scheme.pipelined_function(sysdate-50)
    So I should create view1:
    create or replace view scheme.view1 as 
    select * from scheme.pipelined_function(sysdate-50)
    Next time I need sysdate-1 instead of sysdate-50, so what? Create another view?

    My need is to use pipelined function in BI EE (without any view) for selecting different results depending on user's choise.
  • 5. Re: table function as physical table in Oracle BI EE
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Can you share your complete requirements that helps me/'any one' to give some suggestion..
    Based what you said now can get it done with-in BI itself (I might be wrong..)

    The same its not possible with current_date in BI?

    Edited by: Srini VEERAVALLI on Dec 5, 2012 1:30 PM
  • 6. Re: table function as physical table in Oracle BI EE
    marco Newbie
    Currently Being Moderated
    Srini VEERAVALLI, thanks for your reply.

    Well, my requirement is to use
    pipelined function as data source in BI
    .


    You mentioned current_date, but input parameter can be just anyone, not just sysdate.
    For example, input parameter can be number of department, country id, etc.


    As I mentioned above, it is possible, I found example how to use it, but can not understand how to pass input parameter from user report input into repository. It should be some variable or something.

    I am looking forward to see your reply.
    Best regards.

    Edited by: marco on Dec 5, 2012 11:56 AM
  • 7. Re: table function as physical table in Oracle BI EE
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Assuming your base query is like
    select * from scheme.pipelined_function(sysdate-50)

    and assuming that the value 50 you want to change as per report requirements.. in this case you can go by session variable,
    and you are setting the value of session variable using SET VARIABLE option so that the query will change as need.

    If this works for you then you can extend to actual requirements.

    Edited by: Srini VEERAVALLI on Dec 5, 2012 2:11 PM
  • 8. Re: table function as physical table in Oracle BI EE
    972376 Newbie
    Currently Being Moderated
    A pipelined function can be a clever way to use procedural logic to simulate a table.
    I don' t think there is a direct way to import the function for use as a table into the RPD directly.
    The trick of using a view should allow you to import it, but I think the view needs to call the pipeline function using the TABLE operator, like this:
    select * from TABLE(scheme.pipelined_function(sysdate))
    I do not know of any way to have OBIEE pass a run time parameter to a view - that would require bind variables or a parse.
    I don't think it works that way. I think it imports a statically defined object.

    If you can share a little more of what your code looks like or what you are trying to accomplish, the clever people who respond on these forums may be able to help figure out a solution.
  • 9. Re: table function as physical table in Oracle BI EE
    marco Newbie
    Currently Being Moderated
    Another example:

    http://obiee100.blogspot.com/2011/07/oracle-bi-ee-101323-ref-cursors-and.html

    This proves again that using pipelined function in OBIEE is possible.
    But how do I pass input parameter from BI report into pipelined function in repository?
  • 10. Re: table function as physical table in Oracle BI EE
    Srini VEERAVALLI Guru
    Currently Being Moderated
    In that link it was demonstrated using 'Direct Database Request' if you want to go with that you can use presentation variables in 'Direct Database Request'.
    Create a prompt with presentation variable and report using presentation variable.

    Or else check my last post on 12/5
  • 11. Re: table function as physical table in Oracle BI EE
    marco Newbie
    Currently Being Moderated
    I've got no access to my computer at work during weekend, so I'm just collecting appropriate articles about pipelined function in OBIEE. Here is one of them:
    http://www.oraclenerd.com/2011/08/obiee-stored-proc-pipelined-function.html

    Upd: another one
    http://oracle-bi.siebelunleashed.com/articles/callingdb-function-in-obiee/

    UUpd: similar issue was discussed at oracle forum before:
    How to pass multiple parameters to Query using START WITH, CONNECT BY OBIEE

    UUUpd: using variable in BI
    http://gerardnico.com/wiki/dat/obiee/set_request_variable_dashboard_prompt

    UUUUpd: the first really-easy-to-use hint (imho) I've found
    http://stackoverflow.com/questions/13789824/table-function-as-physical-table-in-oracle-bi-ee/13805307?iemail=1#13805307

Legend

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