Thanks for the question.
Asked: April 06, 2022 - 7:23 am UTC
Last updated: April 07, 2022 - 9:37 am UTC
Version: ORACLE19
Viewed 1000+ times
CREATE TYPE arguments_r IS OBJECT ( q integer, b INTEGER ); CREATE OR REPLACE FUNCTION f (p IN arguments_r) RETURN INTEGER IS BEGIN RETURN 1; END; with a(a1,a2) as (select 1 , 2 from dual) select f(arguments_r(a.* )) from a; --doesn't work --with a(a1,a2) as (select 1 , 2 from dual) --select f(arguments_r(a.* )) from a; --with a(a1,a2) as (select 1 , 2 from dual) --select f(CAST(a.* AS arguments_r) ) from a;
create table t ( c1 int, c2 int, c3 int, c4 int, c5 int ); insert into t values ( 1, 2, 3, 4, 5 ); insert into t values ( 6, 7, 8, 9, 10 ); create or replace function f ( p in t%rowtype ) return integer is begin return p.c1; end; / begin for rws in ( select * from t ) loop dbms_output.put_line ( f ( rws ) ); end loop; end; / 1 6 create or replace function f ( p in sys_refcursor ) return integer is col t%rowtype; begin fetch p into col; return col.c1; end; / select f ( cursor ( select * from t ) ) from dual; F(CURSOR(SELECT*FROMT)) ----------------------- 1
Pierre-Olivier GENDRAUD, April 06, 2022 - 10:13 am UTC
declare cursor cur is select ... var cur%rowtype;
Pierre-Olivier GENDRAUD, April 06, 2022 - 10:19 am UTC
loop fetch ... into ... exit when cur%notfound end loop;
Rajeshwaran Jeyabal, April 06, 2022 - 4:00 pm UTC
demo@XEPDB1> create or replace function foo( 2 p_tab dbms_tf.table_t , 3 p_cols dbms_tf.columns_t default null) 4 return varchar2 5 sql_macro 6 as 7 l_sql long; 8 begin 9 if p_cols is null then 10 l_sql := ' select * from p_tab '; 11 else 12 l_sql := ' select '; 13 for i in 1..p_cols.count 14 loop 15 l_sql := l_sql || p_cols(i) ||','; 16 end loop; 17 l_sql := trim(',' from l_sql) ||' from p_tab '; 18 end if; 19 return l_sql; 20 end; 21 / Function created. demo@XEPDB1> select * from foo(dual); D - X demo@XEPDB1> select * from foo(dept,columns(deptno)); DEPTNO ---------- 10 20 30 40 demo@XEPDB1> select * from foo(dept,columns(deptno,dname)); DEPTNO DNAME ---------- -------------- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS demo@XEPDB1>
Stew Ashton, April 07, 2022 - 8:45 am UTC
create table tab(a,b,c,d,e) as select 1,2,3,4,5 from dual union all select 6,7,8,9,10 from dual; create or replace package pp as type tt_tab is ref cursor return tab%rowtype; function f(p1 in tt_tab) return sys.odcinumberlist pipelined; end pp; / create or replace package body pp as function f(p1 in tt_tab) return sys.odcinumberlist pipelined is begin for rec in p1 loop pipe row(rec.a); end loop; return; end f; end pp; / select * from table(pp.f(cursor(select * from tab))); COLUMN_VALUE 1 6
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library