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

    Stored procedures and dynamic columns

    665054
      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
          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
            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
              user5140701
              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