1

here's a table with a high number of columns

create table test (
id    number,
col_1 varchar2(50),
col_2 varchar2(50),
.
.
col_n varchar2(50)
);

and the table is filled with some example data

insert into test values(1,'a1','a2',...,'an');
insert into test values(2,'a1','a3',...,'am');
insert into test values(3,'a4','a2',...,'ax');

now i need to copy one row (fore example the row with id=1) and change just one column's value, if the result does not be similar to another row (without considering id). some thing like this:

declare
 r test%rowtype;
 var1 number;
begin
 insert into r from test where id = 1;
 r.col_2='a3';
 select count (*) into var1 from test where 'the row without id' = r;
 if (var1 = 0) then
  insert into test values r;
 end if;
end;

but i don't know how to write the select part in oracle. consider table test has lots of columns so you can't write all of the columns in where clause.

jalal rasooly
  • 705
  • 3
  • 7
  • 24
  • 1
    Oracle doesn't really limit the number of columns that can be referenced in a `where` clause and it does limit the number of columns in a table. If there are just a lot of columns, use a meta data table to extract them and generate the code. – Gordon Linoff Jan 05 '14 at 21:54
  • 1
    [Related question, perhaps, but not quite the same](http://stackoverflow.com/q/9133120/266304). Maybe also worth considering the possibility of a race condition between when you do the count and when you do the insert; a small window, but two sessions could still clash and insert duplicates. A unique index over all the columns might be a comsiderable overhead but could be considered, maybe, and would speed up the count too. – Alex Poole Jan 05 '14 at 22:15
  • Perhaps `where id is null` in your `select` query? – Rachcha Jan 06 '14 at 02:24

1 Answers1

2

It is sort of hard to understand what you need. I'll take a shot here. Let me know if this is on track....

To make things easy, can you create a unique index on the table?

    create unique index test_uidx on test (col_1, col_2, ... col_n);

And then let oracle do the work:

    declare
      r     test%rowtype;
      var1  number;
    begin
      select * into r from test where id=1;  --- get the row
      r.col_1  := 'some new value';          --- change 1 value

      begin
         insert into test values r;          --- insert the row
      exception
        when dup_val_on_index then           --- row was a dup
          null;                              --- and did not insert
      end;
   end;
Brian McGinity
  • 5,777
  • 5
  • 36
  • 46
  • i think i can create an index. how does it work? does it insert the row first and if the inserted row was duplicated, oracle will delete that row? can you explain more? – jalal rasooly Jan 06 '14 at 07:31
  • when a row was duplicate then it would not be inserted and an exception will happen – hmmftg Jan 06 '14 at 13:13
  • 1
    When the exception is thrown, control goes into the exception handler, the record is not inserted and the "null;" line reached and then processing continues. If the "null;" was changed to "raise;" then a dup_val_on_index error is raised. By just doing "null;" the error is suppressed. Make sense? – Brian McGinity Jan 06 '14 at 14:44