Re: PL/SQL - is inserting of %ROWTYPE variable possible?

Hi,

"Bogien" <bogien_at_xcom.pl> ???????/???????? ? ???????? ?????????: news:3E1B57D3.DDBC8532_at_xcom.pl...
> Hello,
>
> Suppose I have a table named "tab" with columns "a", "b", "c", "d", "e".
> Now, I declare a variable named "rec" of type "tab%ROWTYPE", which
> reflects single record of the table.
>
> Question:
> Can I insert such variable into the table in PL/SQL at once?
>
> So fat, I've been performing this task this way (doesn't look nice...):
> INSERT INTO tab (a, b, c, d, e) VALUES (rec.a, rec.b, rec.c, rec.d,
rec.e);
>
> Is something like "INSERT INTO tab VALUES rec" possible?

You could write something like follows:

create or insert package TEST
as

TabRow Tab%ROWTYPE;

procedure TabInsert(ARow Tab%ROWTYPE);

end TEST;
/
create or insert package body TEST
as

TabInsertStmt VarChar2(4000);

procedure TabInsert(ARow Tab%ROWTYPE)
is
begin
  TabRow := ARow;
  execute immediate TabInsertStmt;
end;

procedure Init
is
  type TNames is Table of VarChar2(30);
  ANames TNames := TNames();
  Q1 VarChar2(2000);
  Q2 VarChar2(2000);

  procedure AddName(Q IN OUT NOCOPY VarChar2, AName VarChar2)   is
  begin
    if Q is not null then
      Q := Q || ', ';
    end if;
    Q := Q || AName;
  end;

begin
  select Column_Name
  bulk collect into ANames
  from user_tab_columns
  where table_name = 'TAB'
  order by column_id;

  for I in 1..ANames.Count loop
    AddName(Q1, ANames(I));
    AddName(Q2, 'TEST.TabRow.' || ANames(I));   end loop;

  TabInserStmt := 'insert into Tab(' || Q1 || ') values (' || Q2 ||')'; end;

begin
  Init;
end TEST
/

Regards,
  Sergey Balter Received on Thu Jan 09 2003 - 02:56:15 CST