BI Publisher: Taking it to the next level part II (PL/SQL Tables)

You may find this useful if you read my previous article (PL/SQL Taking it to the next level). This is an excerpt of what I posted as a solution to a user in the BI Publisher forum who was going to create staging tables for a BI Publisher Report. There is no need to create staging tables anymore, your just creating more work for yourself and possible performance issues.

Hi All,I am creating a report, which first calls a procedure to populate the custom table and then it uses the custom table to create the report.My Question to you all is, is there a way to call a procedure
first and then call the table from BIP.Thanks,Madhavan G V

Here's my response:

whoa, whoa,whoa.

You should use a pipelined function. Then you can cast a pl/sql table as and actual table. It will save you a read and write to disk, plus the table objects, and there is no need to worry about commits, rollbacks and clean up. This is truly a godsend for reporting, however, because of the anonymity of pipelined functions nobody ever uses it, which is such a shame as it is one of the most powerful tools in pl/sql (it's not just for ETL). Below is some sample
code. What you can see is the ability to join on the table. Basically you could do counts, max, min, etc all in one shot versus creating a function, plus, it can be reused...
good luck,

Ike


Here's an example:

==================================
==Sample Query
==================================
SELECT
GMD_SPEC_TESTS.SPEC_ID
,GMD_SPEC_TESTS.TEST_ID
,GMD_SPEC_TESTS.TEST_METHOD_ID
FROM
GMD_QC_TESTS
,GMD_SPEC_TESTS
,table(vca_rm_spec_pkg.get_valid_tests_table(:p_report_type, GMD_QC_TESTS.TEST_CODE, GMD_QC_TESTS.TEST_DESC)) test_tab
WHERE
GMD_QC_TESTS.TEST_ID = GMD_SPEC_TESTS.TEST_ID
AND NVL(GMD_QC_TESTS.DELETE_MARK, 0 ) != 1
and GMD_SPEC_TESTS.SPEC_ID=:HEADER_SPEC_ID
and test_tab.test_code = GMD_QC_TESTS.TEST_CODE
and GMD_QC_TESTS.TEST_CODE <> 'QAPRV'
ORDER BY TEST_CODE


==================================
==package body
==================================
function get_valid_tests_table (p_report_type in varchar2, p_test_code in varchar2 , p_test_desc in varchar2)
RETURN test_tbl_undef PIPELINED
is
l_test_rec vca_rm_spec_pkg.test_rec;
begin
G_PROC_NAME :='get_valid_tests_table';
if p_report_type = 'SEARCH' then
if g_test_tbl.count <> 0 then
for i in g_test_tbl.first..g_test_tbl.last loop
l_test_rec := g_test_tbl(i);
pipe row(l_test_rec);
end loop;
end if;
else
--this is the summary, what comes goes out.
l_test_rec.test_code := p_test_code;
l_test_rec.test_desc := p_test_desc;
pipe row(l_test_rec);
end if;
return;
end get_valid_tests_table;

procedure set_valid_tests_table (p_test_code in varchar2, p_test_desc in varchar2)
is
begin
G_PROC_NAME :='set_valid_tests_table';
if g_test_tbl.count is null or g_test_tbl.count = 0 then
g_test_tbl(1).test_code := p_test_code;
g_test_tbl(1).test_desc := p_test_desc;
else
g_test_tbl(g_test_tbl.count+1).test_code := p_test_code;
g_test_tbl(g_test_tbl.count).test_desc := p_test_desc;
end if;
return;
end set_valid_tests_table;


==============================
==package spec
==============================
type test_rec is record (test_code varchar2(30),
test_desc varchar2(150)
);
type test_tbl_undef is table of test_rec;
type test_tbl is table of test_rec INDEX BY BINARY_INTEGER;
g_test_tbl test_tbl;

--returns a pl/sql table pipelined, query casts as table
function get_valid_tests_table (p_report_type in varchar2, p_test_code in varchar2, p_test_desc in varchar2)
RETURN test_tbl_undef PIPELINED;

--returns a pl/sql table pipelined, query casts as table
procedure set_valid_tests_table (p_test_code in varchar2, p_test_desc in varchar2); --return varchar2;

PL/SQL: Taking it to the next level.

Disclaimer: The PL/SQL Examples presented here are theory and will not compile. However there are plenty of resources from oracle on this subject.

Ever wonder how you could query a pl/sql table? Well there is a way to do this now, since 9i. You must define a function to be “pipelined.” This is one of the PL/SQL’s more powerful abilities, ironically, it’s rarely used, it’s relatively unknown in the PL/SQL Development community. If you talked to your top PL/SQL guru and said, "Hey man these pipelined functions could be really awesome for reporting." The response from your guru would be this, "How dare you feel my head with such vile and lonesome propaganda!"

Essentially, for report writers this opens up some doors that we haven’t ever thought about or wish we had when writing some really challenging reports. This really comes in handy when your working with tough data models. With pipelined functions there is no need for staging tables, long concatenated lexical’s or multiple functions.

While a function will only return one value, there is an exception to this rule which is a pipelined function which can returns n-values. The pseudo syntax for defining a function of that nature is below.


function get_valid_employee_table (p_dept in varchar2) RETURN emp_tbl_type PIPELINED
is
l_emp_rec emp_pkg.emp_rec;
begin
loop through some pl/sql table that you pre-populated in your before report trigger or the calling program
l_emp_rec := some_pl_sql_table;
if ..
--do something
pipe row(l_emp_rec);
elsif ..
--do something
pipe row(l_emp_rec);
else
--do something
pipe row(l_emp_rec);
end if;
end loop;
return;
end get_valid_employee_table;


Sample Call:

select *
from TABLE(emp_pkg.get_valid_employee_table(‘finance’) managers
where managers.employee_id in (select employee_id from employees)

The inside skinny: When, Where and Why

When:
1. You want to avoid a huge concatenated lexical variable in your where clause that will cause performance issues.

2. There is a need for staging table. Typically, with staging tables your already be storing the data in memory so why make the report transaction based. Note: Staging tables in reports are caused by a bad data model or unruly requirements….it can happen :-)

Where:
1. Code this in your pl/sql package.
2. Use it either for reporting (see when) or an ETL Process.

Why:
1. There is no need to create half a dozen functions, you can create a record and call one function and cast it as a table. Boom, you saved yourself some redundancy in your query and in PL/SQL
2. Writes to disk are eliminated, performance plus
3. No need to manage transactions in a report (ie commit/rollbacks into staging tables). Less complex report…
4. Complex calculations can be preformed in SQL natively
a. ad-hoc counts
b. group by
c. joins/outer joins to sql tables or even other parallel enable functions.
5. Technical this can be viewed as polymorphism. Since its code that is logical being represented as a table or view (yes can you put in a view) it can be queried n- possible ways with your limitation only being that of SQL and or PL/SQL. Think about it...


Send me a shout! Is there a mistake or would you like to comment about this article?