Oct 072013
 
begin
  update all_the_cols_in_one_fell_swoop
  set row = a_plsql_table;
end;
/
"set row" is a handy way to update most/all the columns in the target table easily. 
This is also it's biggest drawback however, as it'll (probably unnecessarily) update 
the primary key too, potentially introducing some locking issues to your application.

For a discussion on some of the issues with using “set row” and how to overcome them, have a read of this oracle-developer.net article.

  2 Responses to “A Simple Way to Update All the Columns”

  1. Very nice !

    I stumbled across ORA-54017: UPDATE operation disallowed on virtual columns last week when trying to use this technique on a table with virtual columns, now I learnt a workaround.

    create table all_the_cols_in_one_fell_swoop
    (
    real int,
    ButDisallowedOnVirtualColumns as (real + 0)
    );

    insert into all_the_cols_in_one_fell_swoop(real) values(0);

    declare
    a_whole_row all_the_cols_in_one_fell_swoop%ROWTYPE;
    begin
    a_whole_row.real := 1;
    a_whole_row.ButDisallowedOnVirtualColumns := 1;

    update all_the_cols_in_one_fell_swoop
    set row = a_whole_row;
    end;
    /
    declare
    *
    ERROR at line 1:
    ORA-54017: UPDATE operation disallowed on virtual columns
    ORA-06512: at line 7

    REM But
    declare
    type
    WorkaroundByAdrianBillington_T is record
    (
    real all_the_cols_in_one_fell_swoop.real%type
    );

    WorkaroundByAdrianBillington WorkaroundByAdrianBillington_T;
    begin
    WorkaroundByAdrianBillington.real := 1;

    update
    (
    select real
    from all_the_cols_in_one_fell_swoop
    )
    set row = WorkaroundByAdrianBillington;
    end;
    /

    PL/SQL procedure successfully completed.

    • Great stuff Matthias – I hadn’t thought how this would affect virtual columns. Thanks for sharing.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)