This discussion is archived
3 Replies Latest reply: Mar 20, 2013 1:11 AM by 968579 RSS

Stored procedures and dynamic columns

665054 Newbie
Currently Being Moderated
I have two questions:

1. Can Oracle BI execute Oracle Stored procs. So instead of using a sql select statement, would it be possible to call a stored proc?

2. My select all statement / stored proc, returns a variable amount of columns depending on some external factor. Is it possible to create a report, using a template with this setup? Therefore, the first time I call the selct / stored proc, it returns 5 columns and I want these 5 columns to be displayed in my report, in a table, using a template, whereas, next time, the call might only return 4 columns and now these 4 columnc need to be displayed in a table?

As example:

call 1: column a, column b, column f, column g, column h
call 2: column a, column b, column r, column m

Thanks!
  • 1. Re: Stored procedures and dynamic columns
    524753 Guru
    Currently Being Moderated
    use a pipeline functionality,

    and return the pl/sql table from the procedure.

    you can call it here in sql as select * from functionname(parameter)...

    this is going to give you the number of columns based on the the pl/sql table you are returning.
  • 2. Re: Stored procedures and dynamic columns
    524753 Guru
    Currently Being Moderated
    l want to use the pl/sql package in datatemplate,
    Yes, we can use them in different ways, by using the oracle table function or Pipelined Table Functions.

    Pl/sql package will return the collection variable , which you can cast it as table in the query..
    sample

    create type numset_t as table of number;
    /

    create function f1(x number) return numset_t pipelined is
    begin
    for i in 1..x loop
    pipe row(i);
    end loop;
    return;
    end;
    /
    select * from table(f1(3));

    COLUMN_VALUE
    1
    2
    3

    In the datatemplate , you can just call the

    select * from table(f1(3));

    Or even the parameter can be passed to the function, in which your entire logic can be written, which will return the collection variable.

    Datatemplate wil be more powerful , when used like this,
  • 3. Re: Stored procedures and dynamic columns
    968579 Newbie
    Currently Being Moderated
    Hi,

    I'm encountering an error when generating a report that is calling a stored procedure with passed parameters. The stored procedure is called in a data template. The error im encountering is:

    The XML page cannot be displayed
    Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


    --------------------------------------------------------------------------------

    Missing equals sign between attribute and attribute value. Error processing resource 'http://10.60.60.33:9704/xmlpserver/se...

    <ALLOCATION REQUESTS SUMMARY>
    ---------------------^

    If you have a solution please do let me know.

    Thanks, Albert

Legend

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