Dec 022013
 
create view of_your_tables_so_it_is 
    of an_object
    with object oid (your_relational_data) as
  select your_relational_data, 
         into_an_object_model
  from   your_relational_tables;
I'm not sure whether or not this approach is "better" than using Hibernate or other object-relational mapping tools.
At least you have a better idea how the mapping works by looking at the database I guess.

For further reading on this, have a look at this devshed article, here on oracle-base or Oracle’s object-relational dev guide.

For a script for the above, keep reading. Continue reading »

Oct 282013
 

If you want the colums in your tables to appear in a different order, you can clone the table, drop the old and rename the new:

-- hard way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
);

create table reordered_columns as 
  select because_the_current_order,
         is_confusing
  from   i_want_to_reorder;

drop table i_want_to_reorder purge;

rename reordered_columns to i_want_to_reorder;

drop table i_want_to_reorder purge;

Alternatively, you can do this much quicker (especially if the table is “large”) by just creating a view with the new column order:

-- easy way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
);

rename i_want_to_reorder 
to i_want_to_reorder_tab;

create view i_want_to_reorder as
  select because_the_current_order,
         is_confusing
  from   i_want_to_reorder_tab;

drop view i_want_to_reorder;
drop table i_want_to_reorder_tab purge;

If your application is setup nicely and has synonyms to your table (rather than connecting directly) then you just need to repoint the synonyms (rather than renaming). Of course, this always begs the question why do you need to re-order the column list – I can’t think of any (good) reasons.

UPDATE As pointed out by Matthias in the comments, there’s the 12c approach: make columns invisiable and visible again:

-- 12c way
create table i_want_to_reorder (
  is_confusing              integer,
  because_the_current_order integer
); 

alter table i_want_to_reorder 
modify ( is_confusing invisible );

alter table i_want_to_reorder 
modify ( is_confusing visible );

desc i_want_to_reorder

BECAUSE_THE_CURRENT_ORDER NUMBER(38)
IS_CONFUSING	          NUMBER(38)