3 Replies Latest reply: Mar 20, 2013 3:11 AM by user5140701 RSS

    Stored procedures and dynamic columns

      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

        • 1. Re: Stored procedures and dynamic columns
          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
            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..

            create type numset_t as table of number;

            create function f1(x number) return numset_t pipelined is
            for i in 1..x loop
            pipe row(i);
            end loop;
            select * from table(f1(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

              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 '


              If you have a solution please do let me know.

              Thanks, Albert