CODEWITHSUNDEEP
oracle-databaseoracle11goracle11gr2
zerkms
zerkms

Reputation: 254956

Fetching bunch of fields into a custom object defined in a package

Let's suppose I have a package A that has

type type_bla is record (id number, ...);

Also in the same package body I have a query, that fetches all the fields needed to construct the object. If I had a stored object I could do:

select type_bla(t1.id, t2.foo, t1.bar ...)
into instance_of_type_bla
from table t
inner join table2 t2 ON ...

But since I have a custom type defined in the package - it has not a constructor, so I obliged to change it to:

select t1.id, t2.foo, t1.bar ...
into instance_of_type_bla.id, instance_of_type_bla.foo ...
from table t
inner join table2 t2 ON ...

Is it more elegant way to fill such sort of objects?

Upvotes: 2

Views: 174

Answers (1)

Justin Cave
Justin Cave

Reputation: 231711

You should be able to simply select the data directly into the record just like you would if you declared a %ROWTYPE record.

I'll declare a package PKG_FOO where the GET_REC function populates the custom record

SQL> create or replace package pkg_foo
  2  as
  3    type my_rec is record( col1 number, col2 number, col3 varchar2(10) );
  4    function get_rec
  5      return my_rec;
  6  end;
  7  /

Package created.

SQL> create or replace package body pkg_foo
  2  as
  3    function get_rec
  4      return my_rec
  5    is
  6      l_rec my_rec;
  7    begin
  8      select 1, 2, 'Justin'
  9        into l_rec
 10        from dual;
 11      return l_rec;
 12    end;
 13  end;
 14  /

Package body created.

And just to show that it works

SQL> declare
  2    l_rec pkg_foo.my_rec;
  3  begin
  4    l_rec := pkg_foo.get_rec;
  5    p.l( 'Col1 = ' || l_rec.col1 );
  6    p.l( 'Col2 = ' || l_rec.col2 );
  7    p.l( 'Col3 = ' || l_rec.col3 );
  8  end;
  9  /
Col1 = 1
Col2 = 2
Col3 = Justin

Upvotes: 3

Related Questions