In some developments, situation demands to write sql query on PL/SQL table type.
Oracle provides pipe line commands to achieve this .
I found it handy to use along with Oracle Application Express ( APEX )which needs report query region to have customized data display.
How to query from PL/SQL?Use below simple steps and its ready for query
1) Create database data type and table type.
Note the type declaration should match pl/sql record/table type so data can be "poured" into database type
create or replace type contract_lines_obj_t as object
(
line NUMBER,
instance_id NUMBER,
end_customer NUMBER,
product VARCHAR2 (240),
qty NUMBER,
description VARCHAR2 (450),
status VARCHAR2 (50),
start_date DATE,
end_date DATE,
price NUMBER,
unit_price NUMBER,
exception_status varchar2(2),
exception_code varchar2(200),
exception_information varchar2(4000)
);
/
create or replace type contract_lines_ntt as table of contract_lines_obj_t;
2) Develop a pl/sql function to get the table type populated
function contract_lines(pid in number)
return contract_lines_ntt
pipelined is
pragma autonomous_transaction;
pout_contract_lines_tab contract_lines_tab_type ;
v_exception_key number;
v_index number;
l_return_status varchar2(100);
begin
-- populate data and get pl/sql table type as out parameter
begin
xxrh_show_contract_lines( pid ,
l_return_status ,
pout_contract_lines_tab );
exception
when others
then
v_exception_key := pout_contract_lines_tab.next(pout_contract_lines_tab.last);
if v_exception_key is null
then
v_exception_key := 1;
end if;
pout_contract_lines_tab(v_exception_key).exception_status := 'Y';
pout_contract_lines_tab(v_exception_key).exception_code := SQLCODE;
pout_contract_lines_tab(v_exception_key).exception_information := SQLERRM || ' Stack: ' || dbms_utility.format_error_backtrace;
end;
if(pout_contract_lines_tab.count > 0)
then
v_index := pout_contract_lines_tab.first;
loop
exit when v_index is null;
commit;
pipe row (
contract_lines_obj_t(
pout_contract_lines_tab(v_index).line
, pout_contract_lines_tab(v_index).instance_id
, pout_contract_lines_tab(v_index).end_customer
....
, pout_contract_lines_tab(v_index).exception_code
, pout_contract_lines_tab(v_index).exception_information
)
);
v_index := pout_contract_lines_tab.next(v_index);
end loop;
else
commit;
pipe row (contract_lines_obj_t(null
........
, null
));
end if;
return;
exception
when others
then
commit;
pipe row (contract_lines_obj_t(null
, null
.......
, 'Y'
, SQLCODE
, SQLERRM || ' Stack: ' || dbms_utility.format_error_backtrace
));
return;
end contract_lines;
Running of above function causes data to be populated in the table type
3) Now you are ready to query data populated already
select LINE , INSTANCE_ID, END_CUSTOMER, PRODUCT , QTY , DESCRIPTION , STATUS ,START_DATE , END_DATE , PRICE , UNIT_PRICE
from table(xxrh_supx_orcl_pkg_ypatil.contract_lines(3103165));
Note the from clause More details at : http://www.adp-gmbh.ch/ora/plsql/pipeline.html
http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm
P.S. Oracle should have some function to clear projects pipeline as well :) to have work for all eligible hands!
