This content has been marked as final. Show 5 replies
For PL/SQL functions/ stored-procedures, try data template.. but I guess with your req't, it could only do as parameter(input)...
You should be able to call a pl/sql pipelined function to return a set of data. In your data model, you could have something like this: select a.*
from table(schema_name.pkg_my_rpt.func_get_data(:param_1, :param_2, :param_3))a where the function pipes the rows returned via a procedure call.
We have used it and it works well.
Search this forum.. Have answered this previoulsy.
Go thru these thread, you will get more idea's
Re: Stored procedures and dynamic columns
Re: Is it possible to use Stored Procedures in BI Publisher GUI?
Re: PL/SQL Stored Procedure w/ XML Template?
Use pipeline is one option.
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,
Is this applicable if I have more than 1000 inputs? Coz I try to input more than 1000 and an error comes up.
I try to use the store procedure way
Thx very much
Since oracle table has a limit of 1000 columns, table function is also bounded with that limit