Ahh -- data warehouse -- now that is a horse of a different color (or colour as you would spell ;)

I for some reason thought this was about AUDITING.  for auditing, i wouldn't like this, for capturing changes to feed a DW, thats something more interesting....



well, the primary keys should always come in pairs in this mod table so


ops$tkyte@ORA920> select pk_field,
  2         max( decode( rn, 2, mod_date, null ) ) mod_date,
  3         max( decode( rn, 2, mod_by, null ) ) mod_date,
  4         max( decode( fnum, 3, 'color', 4, 'something else', 5, 'etc...' ) ) mod_column,
  5             max( decode( rn, 1, val, null )) old_val,
  6             max( decode( rn, 2, val, null )) new_val
  7    from (
  8  select t.*,
  9         row_number() over (partition by pk_field order by mod_date) rn
 10    from t
 11         )
 12   group by pk_field
 13  /

PK_FI MOD_DATE  MO MOD_COLUMN     OLD_VAL    NEW_VAL
----- --------- -- -------------- ---------- ----------
UID1  06-JAN-03 PS color          Green      Blue
UID2  16-JAN-03 JD color          Red        Yellow

ops$tkyte@ORA920>
ops$tkyte@ORA920> 

If you KNOW that there are pairs (each PK_FIELD is in there two times), rownum after an order by will work

ops$tkyte@ORA920> select t.*,
  2         mod(rownum+1,2)+1 rn
  3    from (select * from t order by pk_field, mod_date) t
  4  /

MOD_DATE  MO PK_F       FNUM VAL                RN
--------- -- ---- ---------- ---------- ----------
17-FEB-02 JD UID1          3 Green               1
06-JAN-03 PS UID1          3 Blue                2
17-FEB-02 PS UID2          3 Red                 1
16-JAN-03 JD UID2          3 Yellow              2

ops$tkyte@ORA920> select pk_field,
  2          max( decode( rn, 2, mod_date, null ) ) mod_date,
  3          max( decode( rn, 2, mod_by, null ) ) mod_date,
  4          max( decode( fnum, 3, 'color', 4, 'something else', 5, 'etc...' ) ) mod_column,
  5          max( decode( rn, 1, val, null )) old_val,
  6          max( decode( rn, 2, val, null )) new_val
  7    from (
  8  select t.*,
  9         mod(rownum+1,2)+1 rn
 10    from (select * from t order by pk_field, mod_date) t
 11         )
 12   group by pk_field
 13  /

PK_F MOD_DATE  MO MOD_COLUMN     OLD_VAL    NEW_VAL
---- --------- -- -------------- ---------- ----------
UID1 06-JAN-03 PS color          Green      Blue
UID2 16-JAN-03 JD color          Red        Yellow



fnum was your "number" field from your table... 

Mod_Date  Mod_By pk_field number  colour
17-FEB-02 JD     UID1     3       Green
06-JAN-03 PS     UID1     3       Blue
                                  ^^^^^^^^


apparently i didn't need the decode, my "val" field is your "colour" field.

ops$tkyte@ORA920> select pk_field,
  2          max( decode( rn, 2, mod_date, null ) ) mod_date,
  3          max( decode( rn, 2, mod_by, null ) ) mod_date,
  5          max( decode( rn, 1, colour, null )) old_colour,
  6          max( decode( rn, 2, colour, null )) new_colour,
             max( decode( rn, 1, column2, null )) old_column2,
             max( decode( rn, 2, column2, null )) old_column2,
             .........
  7    from (
  8  select t.*,
  9         mod(rownum+1,2)+1 rn
 10    from (select * from t order by pk_field, mod_date) t
 11         )
 12   group by pk_field
 13  /

 

procedure return_result(pin in varchar2,pout out cur_get_record)
as
    l_rec testtable%rowtype;
begin
if pin ='ws' then
dbms_wm.gotoworkspace('workspace1');
end if;

open pout for select * from testtable;
fetch pout into l_rec;
dbms_output.put_line( l_rec.col1 || ', ' || l_rec.col2 );

if dbms_wm.getworkspace() != 'LIVE' then
  dbms_wm.gotoworkspace('LIVE');
end if;

end;

ops$tkyte%ORA10GR2> Create table testtable (col1 primary key ,col2 )
  2  as
  3  select rownum, 'test'||rownum from all_users where rownum <= 2;

Table created.

ops$tkyte%ORA10GR2> exec dbms_wm.enableversioning('testtable');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_wm.createworkspace('workspace1');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_wm.gotoworkspace('workspace1');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> update testtable set col2='updated2';

2 rows updated.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> exec open :v for select * from testtable;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print v

      COL1 COL2
---------- --------------------------------------------
         1 updated2
         2 updated2

ops$tkyte%ORA10GR2> exec open :v for select * from testtable;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_wm.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print v

      COL1 COL2
---------- --------------------------------------------
         1 test1
         2 test2

ops$tkyte%ORA10GR2> exec dbms_wm.DISABLEVERSIONING( 'TESTTABLE' , TRUE );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_wm.removeworkspace('workspace1');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> drop table testtable purge;

Table dropped.