This discussion is archived
3 Replies Latest reply: Nov 12, 2009 3:38 AM by 703874 RSS

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

703874 Newbie
Currently Being Moderated
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?
    467313 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks user464310 - I will check the doc again.

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

    Regards
    Sookie

Legend

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