I have the following issue: Every row in my base table have a flag column ('flg' in the listing below) and an function argument column ('arg'). If in a row #N the flag is 'Y', then function has to be called; after, it shall return, let's say, a column (actually a bunch of them, but i'll simplify as much as i can). And finally, the row #N should transform into a sub-table - full join of a row #N and the column, returned by the function. If the flag is 'N' then a result for the row #N shall be as the row itself plus NULL in that "function return column".
So here's an example:
create or replace package SIEBEL.TEST_PACKAGE as
type ret_type is table of number;
function testFunc(inp number)
return ret_type
pipelined;
end TEST_PACKAGE;
/
create or replace package body SIEBEL.TEST_PACKAGE is
function testFunc(inp number)
return ret_type
pipelined
is
i number;
begin
dbms_output.put_line('Function call, arg = ' || to_char(inp));
if (inp is null OR inp = 0) then
pipe row (null);
else
for i in 1..inp loop
pipe row (i);
end loop;
end if;
end testFunc;
end TEST_PACKAGE;
/
with base_table as
(
select 'Shall invoke' col0, 'Y' flg, '2' arg from dual
union
select 'Shall not invoke' col0, 'N' flg, '0' arg from dual
)
select * from base_table t0, table(siebel.test_package.testFunc(t0.arg)) t1;
It will return what i actually want:
COL0 | FLG | ARG | COLUMN_VALUE
--------------------------------------------
Shall invoke | Y | 2 | 1
Shall invoke | Y | 2 | 2
Shall not invoke | N | 0 |
The thing is, even for the 'N' flag the function is still called - the database output will show
Function call, arg = 2
Function call, arg = 0
If a real world i have hundreds of records with 'Y' flag, ~100K with 'N'. Alse my actual function is much more complicated and have a lot of stuff in its namespace, so every function call is crucial in terms of perfomance.
What i do want is the database output for the example:
Function call, arg = 2
I could achieve it with
with base_table as
(
select 'Shall invoke' col0, 'Y' flg, '2' arg from dual
union
select 'Shall not invoke' col0, 'N' flg, '0' arg from dual
)
select t.*, t1.column_value
from base_table t, table(SIEBEL.TEST_PACKAGE.testFunc(t.arg)) t1
where t.flg = 'Y'
union all
select t.*, null as column_value
from base_table t
where t.flg = 'N';
but then all indexes became useless - every 'order by' instruction will take alot to complete.
Please, help me to achieve the desired behaviour of function calls and still to save the primal rows order.
Feel free to ask me if anything is not clear.
Best Regards, Alexey
For flag = "N" the function is not called if you conditionally join
set serveroutput on
with base_table as
(
select 'Shall invoke' col0, 'Y' flg, '2' arg from dual
union
select 'Shall not invoke' col0, 'N' flg, '0' arg from dual
)
select * from base_table t0
left join table( test_package.testFunc(t0.arg) ) t1 on (t0.flg = 'Y');
Script Output
Package created.
Package body created.
COL0 FLG ARG COLUMN_VALUE
---------------- --- --- ------------
Shall invoke Y 2 1
Shall invoke Y 2 2
Shall not invoke N 0
3 rows selected.
Server Output:
Function call, arg = 2
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments