• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

Oracle column differences like a UNIX "diff" command

In an Oracle database, there are two columns in two different tables of the same type - VARCHAR2(4000). The columns are basically a pseudo code.  The columns contain lines of code with embedded line returns.

The need is to be able to determine the difference between the two columns, lines modified, new lines, and deleted lines.  The results becomes a basic UNIX command “diff” of the two columns.

The code needs to be able to report the differences on demand from a Visual Studio 2008 C# application.

Example:

Table 1 column:

--      02073 ALLTEL
NET_CYCLE_TXT            :=  'Changed - NETWORK IS BILLED ON A CURRENT CALENDAR MONTH' ;
NET_CRX_L1_CH            :=      5000.00;
NET_CRX_L2_CH            :=      4250.00;
NET_NET_L1_CH            :=        900.00;
NET_NET_L2_CH            :=      1850.00;
NET_TOT            :=      NET_CRX_L1_CH + NET_CRX_L2_CH + NET_NET_L1_CH + NET_NET_L2_CH;

--Special Message Text--
NET_SPBILL_3_TXT      := 'INSTALL/DISCONNECT DATE REFLECTED IN CHARGE LINE - CHARGES ARE PRORATED BASED ON DATE';

ZGD_NET            :=      NET_TOT;

Table 2 column:

--      02073 ALLTEL
NET_CYCLE_TXT            :=  'NETWORK IS BILLED ON A CURRENT CALENDAR MONTH' ;

NET_CRX_L1_CH            :=      4250.00;
NET_CRX_L2_CH            :=      4250.00;
NET_NET_L1_CH            :=        900.00;
NET_NET_L2_CH            :=      1850.00;
NET_EDT_L1_CH            :=      500.00;
NET_EDT_L2_CH            :=      1500.00;
NET_TOT      :=      NET_CRX_L1_CH + NET_CRX_L2_CH + NET_NET_L1_CH + NET_NET_L2_CH +           NET_EDT_L1_CH + NET_EDT_L2_CH;

--Special Message Text--
NET_SPBILL_3_TXT      := 'INSTALL/DISCONNECT DATE REFLECTED IN CHARGE LINE - CHARGES ARE PRORATED BASED ON DATE';

ZGD_NET            :=      NET_TOT;
0
dsjohndavid
Asked:
dsjohndavid
1 Solution
 
MikeOM_DBACommented:

Forget about re-inventing the wheel and load the code into a source code / version control management software.

My 2c.
0
 
MikeOM_DBACommented:

Or:

1) Use a 'split' function (lots available on Oracle forums) to separate the lines of the source varchar column and load the lines into an array.

2) Do the same for the target varchar you want to compare to.

3) compare both arrays and report the differences.

0
 
dsjohndavidAuthor Commented:
That is what I have done. The challenge comes when trying to determine if a line is new, deleted, or just modified between the one column and the other when the lines differ. Any suggestions?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeOM_DBACommented:

Well....that's why I said you would be "re-inventing the wheel".

I tried it once and it's quite complex, whenever you find a different line, you have to check ahead in both sources for matching line(s) and decide if the line was added or removed. Most "diff" utilities report changes as delete/insert pairs.

Good luck!


0
 
MikeOM_DBACommented:

PS: Also you must decide if you are going to ignore differences due to characters in  "upper/lower case", the number of consecutive spaces (or tabs), empty lines, etc...

0
 
tropicallyCommented:

Is this a one time thing you're trying to do.

Other approaches other than using pl/sql.

Dump both columns into individual seperate files,
Use a diff tool that supports command line and output the diff to a third file.


0
 
gheistCommented:
select d1,d2 from table where not (d1=d2)
0
 
dsjohndavidAuthor Commented:
I did just this and used "WinMerge" to determine the differences.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now