3 Replies Latest reply: Nov 12, 2009 5:38 AM by 703874 RSS

    Is it possible to use Stored Procedures in BI Publisher GUI?

    703874
      Hi All,

      Pardon my ignorance but in BI Publisher's latest documentation, I couldn't find anywhere how to use stored procedures in BI Publisher? Is it possible to use them or only SQL queries can work through that "Query Builder" provided in GUI?

      Thanks
      Sookie
        • 1. Re: Is it possible to use Stored Procedures in BI Publisher GUI?
          user464310
          I guess it is doable using data templates. Haven't tried it out though. I came across the documentation where it was mentioned that before-report triggers and much more can be done using data templates.
          • 2. Re: Is it possible to use Stored Procedures in BI Publisher GUI?
            524753
            Generation of XML can be done in many ways

            One :

            SELECT xmlgen.getXml(
            'SELECT empno "EMP_NO"
            , ename "NAME"
            , deptno "DEPT_NO"
            FROM emp
            WHERE deptno = 10'
            , 0
            ) FROM dual;

            <?xml version = '1.0'?>
            <ROWSET>
            <ROW num="1">
            <EMP_NO>7782</EMP_NO>
            <NAME>CLARK</NAME>
            <DEPT_NO>10</DEPT_NO>
            </ROW>
            <ROW num="2">
            <EMP_NO>7839</EMP_NO>
            <NAME>KING</NAME>
            <DEPT_NO>10</DEPT_NO>
            </ROW>
            <ROW num="3">
            <EMP_NO>7934</EMP_NO>
            <NAME>MILLER</NAME>
            <DEPT_NO>10</DEPT_NO>
            </ROW>
            </ROWSET>
            Using XML-SQL Utility also xml can be generated.
            But in Datatemplate , you can call queries, and then you can control the rows returned by using the filters , for this filters you can call function, pl/sql function. etc...

            If you are thinikng you still 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,


            Re: How to get xml file from plsql package
            • 3. Re: Is it possible to use Stored Procedures in BI Publisher GUI?
              703874
              Thanks user464310 - I will check the doc again.

              Thanks Vetsrini- I will try that approach if it will work.

              Regards
              Sookie