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?

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