oracle - Convert T-SQL INNER JOIN on a Function to PL-SQL -

i have t-sql view this:

left join fnc_assetattributebytype(3) tmp on a.assetid = tmp.assetid; 

is there straight forward way can translate (i'm writing tool programmatically) without creating package or modifying header of function void other calls in other dml statements?

main questions: 1. can write pl function returns table? 2. if not, should do?

you can create function returns table type, , work on in sql using inside table() clause. user running sql needs execute privilege on function.

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 sql> select *   2    table(fnc_assetattributebytype(3))   3    left join table(fnc_assetattributebytype(3)) b on b.assetid = a.assetid;    assetid asset_name       asset_val    assetid asset_name       asset_val ---------- --------------- ---------- ---------- --------------- ----------          1 abc                      3          1 abc                      3          2 abc                      6          2 abc                      6          3 abc                      9          3 abc                      9