plsql - Oracle: Pipe Query Results Without Knowing The Column Types -
i know possible:
sql> create or replace type tp_asset object (assetid number, asset_name varchar2(100), asset_val number) 2 / type created sql> create or replace type tp_tab_asset table of tp_asset; 2 / type created sql> create or replace function fnc_assetattributebytype(p_num in number) return tp_tab_asset pipelined 2 v_tp_asset tp_asset; 3 begin 4 in 1 .. 3 5 loop 6 v_tp_asset := tp_asset(i, 'abc', * 3); 7 pipe row (v_tp_asset); 8 end loop; 9 return; 10 end; 11 / function created but seems incredibly stupid. why want maintain list of columns in 2 places? i'm translating t-sql oracle , i'd following:
create or replace function fnc_assetattributebytype( p_attributetypeid in number) return ******table???????? pipelined begin j in ( select a.assetid, shortname, longname, attributevalue dbo$asset inner join dbo$asset_attribute aa on a.assetid = aa.assetid inner join dbo$attribute att on aa.attributeid = att.attributeid attributetypeid = p_attributetypeid ) loop pipe row (j); end loop; return; end; tell me if i'm sane or if not way of oracle super sayans
there's 1 alternative think of declaring type inside package, can using %rowtype columns of view (borrowing @justin cave).
sql> col shortname format a20 sql> col longname format a20 sql> drop table dbo$asset; table dropped sql> drop table dbo$asset_attribute; table dropped sql> drop table dbo$attribute; table dropped sql> create table dbo$asset (assetid number); table created sql> create table dbo$asset_attribute (attributeid number, assetid number, shortname varchar2(100), longname varchar2(100)); table created sql> create table dbo$attribute (attributeid number, assetid number, attributevalue number, attributetypeid number); table created sql> insert dbo$asset values (1); 1 row inserted sql> insert dbo$asset_attribute values (10, 1, 'att1', 'attribute1'); 1 row inserted sql> insert dbo$asset_attribute values (20, 1, 'att2', 'attribute2'); 1 row inserted sql> insert dbo$attribute values (10, 1, 999.99, 444); 1 row inserted sql> insert dbo$asset values (2); 1 row inserted sql> insert dbo$asset_attribute values (30, 2, 'att1', 'attribute1'); 1 row inserted sql> insert dbo$asset_attribute values (40, 2, 'att2', 'attribute2'); 1 row inserted sql> insert dbo$attribute values (40, 2, 888.99, 555); 1 row inserted sql> create or replace view vw_assetattributebytype 2 select a.assetid, shortname, longname, attributevalue, attributetypeid 3 dbo$asset 4 join dbo$asset_attribute aa on a.assetid = aa.assetid 5 join dbo$attribute att on aa.attributeid = att.attributeid; view created sql> create or replace package pck_asset 2 type tp_assetattributebytype table of vw_assetattributebytype%rowtype; 3 4 function fnc_assetattributebytype(p_attributetypeid in number) return tp_assetattributebytype pipelined; 5 end pck_asset; 6 / package created sql> create or replace package body pck_asset 2 3 function fnc_assetattributebytype(p_attributetypeid in number) return tp_assetattributebytype pipelined 4 begin 5 j in (select * 6 vw_assetattributebytype 7 attributetypeid = p_attributetypeid) 8 loop 9 pipe row(j); 10 end loop; 11 end fnc_assetattributebytype; 12 13 end pck_asset; 14 / package body created sql> select * 2 table(pck_asset.fnc_assetattributebytype(444)); assetid shortname longname attributevalue attributetypeid ---------- -------------------- -------------------- -------------- --------------- 1 att1 attribute1 999,99 444 sql> the thing need add filter column attributetypeid view in example. there means of working around , it's simple though uses application context filter inside view. if must keep view columns in query can instead , alas:
sql> col shortname format a20 sql> col longname format a20 sql> drop table dbo$asset; table dropped sql> drop table dbo$asset_attribute; table dropped sql> drop table dbo$attribute; table dropped sql> create table dbo$asset (assetid number); table created sql> create table dbo$asset_attribute (attributeid number, assetid number, shortname varchar2(100), longname varchar2(100)); table created sql> create table dbo$attribute (attributeid number, assetid number, attributevalue number, attributetypeid number); table created sql> insert dbo$asset values (1); 1 row inserted sql> insert dbo$asset_attribute values (10, 1, 'att1', 'attribute1'); 1 row inserted sql> insert dbo$asset_attribute values (20, 1, 'att2', 'attribute2'); 1 row inserted sql> insert dbo$attribute values (10, 1, 999.99, 444); 1 row inserted sql> insert dbo$asset values (2); 1 row inserted sql> insert dbo$asset_attribute values (30, 2, 'att1', 'attribute1'); 1 row inserted sql> insert dbo$asset_attribute values (40, 2, 'att2', 'attribute2'); 1 row inserted sql> insert dbo$attribute values (40, 2, 888.99, 555); 1 row inserted sql> create or replace context ctx_asset using pck_asset; context created sql> create or replace view vw_assetattributebytype 2 select a.assetid, shortname, longname, attributevalue 3 dbo$asset 4 join dbo$asset_attribute aa on a.assetid = aa.assetid 5 join dbo$attribute att on aa.attributeid = att.attributeid 6 attributetypeid = sys_context('ctx_asset', 'attributetypeid'); view created sql> create or replace package pck_asset 2 type tp_assetattributebytype table of vw_assetattributebytype%rowtype; 3 4 function fnc_assetattributebytype(p_attributetypeid in number) return tp_assetattributebytype pipelined; 5 procedure prc_set_attributetype(p_attributetypeid in number); 6 7 end pck_asset; 8 / package created sql> create or replace package body pck_asset 2 3 procedure prc_set_attributetype(p_attributetypeid in number) 4 begin 5 dbms_session.set_context('ctx_asset', 'attributetypeid', p_attributetypeid); 6 end; 7 8 function fnc_assetattributebytype(p_attributetypeid in number) return tp_assetattributebytype pipelined 9 begin 10 -- sets filter on application context view 11 prc_set_attributetype(p_attributetypeid); 12 13 j in (select * 14 vw_assetattributebytype) 15 loop 16 pipe row(j); 17 end loop; 18 end fnc_assetattributebytype; 19 20 end pck_asset; 21 / package body created sql> select * 2 table(pck_asset.fnc_assetattributebytype(555)); assetid shortname longname attributevalue ---------- -------------------- -------------------- -------------- 2 att2 attribute2 888,99 sql> i made lot of assumptions on table structures , may incorrect, forgive me mistakes. idea show sample code running.
Comments
Post a Comment