Ramblings from a Bihari!

Just another WordPress.com weblog

Did you know that you can compare RAW columns in a DB?

You can compare two similar tables or two identical tables in the same database using simple SQL query. The Only limitations for this query is it works on only one databse. This will not work for comparisons of table across the network or tables present in different database schemas.
Example:-
create table table1 (id number(18) primary key, price RAW(255));
create table table2 (id number(18) primary key, price RAW(255));

Now you can insert same data in both the tables using JDBC or any other way.(can u insert RAW, BLOB, CLOB directly from sql prompt like insert into table1 values()??). Now change the tables by updating the RAW column in both tables. You can find the non matching by firing this simple SQL query:-

select id as changed_ID from table1 where id in (select id from table2 where table1.price != table2.price and table1.id = table2.id);

This query will fetch the non matching rows of table1 and table2.
For having the same comparison across the network or across the schema go to the following link:-
http://www.oracle.com/technology/oramag/code/tips2004/012504.html

Advertisements

May 24, 2007 - Posted by | techie stuff

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: