Skip to Main Content
  • Questions
  • Avoid rewriting a lot of column name

Breadcrumb

Question and Answer

Chris Saxon

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

You Asked

How can a function that takes a record as an argument take the result of a query as a parameter (without rewriting the name of all the columns)?


I could select the name of all column (3rd statement) but

I've a table (my_table) with a lot a columns and I will use a lot of this columns in my function. The type of the argument of my function is my_table%ROWTYPE. I don't want to write the name of the column I select.
I have tried to do that in the 4th and 5th statement, but it doesn't work.


perhaps there is a pseudo-column that enable to do that?

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;
 


with LiveSQL Test Case:

and Chris said...

There are a few ways you could do this. Which you use depends on how you want to call the function and what you're doing in it.

Here are a couple of examples using:

- table%rowtype - you need to fetch the data into a variable first
- sys_refcursors - these accept a cursor parameter

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

Rating

  (4 ratings)

Comments

How to improveof the 2nd solution

Pierre-Olivier GENDRAUD, April 06, 2022 - 10:13 am UTC

I prefer the second solution because I continue using only a sql statement.

It works but those points are a problem for me:

In a a little more complex situation ( 2 records type arguments_r1 and arguments_r2), my function will have to take 2 arguments with the same type. It's easy to switch 2 arguments that have the same type.
Therefore I would like to declare that the inputs arguments are cursors that match the records.

I have tried to do a query that return more lines. If there is a cursor inside it. It doesn't work anymore


Is there a way to fix these point?


Chris Saxon
April 06, 2022 - 12:30 pm UTC

You can declare cursors and variables/parameters bound to the cursor's rowtype:

declare
  cursor cur is select ...
  var cur%rowtype;


I don't understand what you mean by the second point. Please provide examples.

another point

Pierre-Olivier GENDRAUD, April 06, 2022 - 10:19 am UTC

In your example, the table has 2 lines.

I want that f compute every line s of the table. In this case f the (cursor to the )whole table, take the first line, make the computation.
Chris Saxon
April 06, 2022 - 12:28 pm UTC

You have to loop through the cursor results in the function

loop
  fetch ... into ...
  exit when cur%notfound
end loop;

SQL Macros

Rajeshwaran Jeyabal, April 06, 2022 - 4:00 pm UTC

How about a Macro, wont that fit here ?
how about something like this?

dynamically pass a table/view/rowsource using dbms_tf.table_t as optionally the needed columns as parameters...
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>


Chris Saxon
April 07, 2022 - 9:37 am UTC

I'm not sure this helps - the OP specifically stated they want to avoid passing the columns

Comment on followup questions

Stew Ashton, April 07, 2022 - 8:45 am UTC

"Therefore I would like to declare that the inputs arguments are cursors that match the records."

We can define "strongly typed ref cursors". They have to be declared in a package which then declares the function that uses the ref cursor.

"I have tried to do a query that return more lines. If there is a cursor inside it. It doesn't work anymore"

I assume you mean that the ref cursor returns multiple rows and you want to process each row and produce one output row for each input row. That would require a table function; I would suggest the "pipelined" variety.
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

Chris Saxon
April 07, 2022 - 9:37 am UTC

Good points

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library