How to return record from an Oracle function with JOIN query?

Question

I can easily create function that returns a myTable%ROWTYPE but what if my query (in the function) as another column from ANOTHER table?

t1_rec t1%ROWTYPE;

Do I append new fields to the major table or what?

SELECT t1.col1, t1.col2, t1.col3, t2.col4...

Question:

How do I efficiently create a record type to contain the result set of the above query?

What I want is something like

t1_rec t1%ROWTYPE (plus another column for t2.col4%TYPE inside t1_rec)

Sorry for the vague question at first.

Please elaborate a little more about what exactly you are looking for. — Jacob
– Jacob, Commented Jul 16, 2013 at 7:26

Frank Schmitt · Accepted Answer · 2013-07-16 08:34:43Z

2

You can use a strongly typed cursor and its rowtype:

-- example data
create table t1(pk number not null primary key, val varchar2(30));
create table t2(
  pk number not null primary key, 
  t1_fk references t1(pk), 
  val varchar2(30));

insert into t1(pk, val) values(1, 'value1');
insert into t2(pk, t1_fk, val) values(1, 1, 'value2a');
insert into t2(pk, t1_fk, val) values(2, 1, 'value2b');

declare
  cursor cur is 
  select t1.*, t2.val as t2_val 
  from t1
  join t2 on t1.pk = t2.t1_fk;

  function get_data(arg in pls_integer) return cur%rowtype is
      l_result cur%rowtype;
    begin
      select t1.*, t2.val as t2_val 
        into l_result 
        from t1 
        join t2 on t1.pk = t2.t1_fk
        where t2.pk = arg;
      return l_result;
    end;
begin
  dbms_output.put_line(get_data(2).t2_val);
end;

UPDATE: you can easily wrap the cursor and function inside a PL/SQL package:

create or replace package pkg_get_data as 

  cursor cur is 
  select t1.*, t2.val as t2_val 
  from t1
  join t2 on t1.pk = t2.t1_fk;

  function get_data(arg in pls_integer) return cur%rowtype;
end;

(package body omitted)

edited Jul 16, 2013 at 8:34

answered Jul 16, 2013 at 7:56

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rey Libutan Over a year ago

Thanks for this answer sir, definitely gave me ideas (and more keywords to search in google later) but what if I want my record types to be accesssible package-wide?

Frank Schmitt Over a year ago

You can declare the cursor inside the package.

Rey Libutan Over a year ago

Thanks for this answer sir I find it very disturbing seeing queries like that in package headers -_- or is this just something those unfamiliar with oracle feel.

Rey Libutan Over a year ago

For example my t1 contains around 10 columns and I am to select only 2 for those while t2 contains around 10 columnas as well and I am to select only 1 (3 columns in total to select in query), Is it worth to to define a record? and manually defining each field of the record for every selected column in the query?

Frank Schmitt Over a year ago

I usually use procedures with a sys_recursor in/out parameter instead of a function. The procedure then performs a OPEN p_cur FOR <query> - no need to define any custom types.

|

Collectives™ on Stack Overflow

How to return record from an Oracle function with JOIN query?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related