Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Q: select with set operators and brackets
Hi!
It should also work by cursor, just want to perform only a check.
You open a cursor, that will compare your tables by using the significant columns
only (special cases see below).
Fetch the first row of the cursor and perform a check, if the row was found.
If at least one row is found, the tables are not equal. At this point, you got the
result and there is no need to watch the other rows.
Don't forget to close the cursor at last.
begin
declare
cursor cur_cmp is
(SELECT col_1, col_2, ... FROM table_a MINUS
SELECT col_1, col_2, ... FROM table_b)
UNION
(SELECT col_1, col_2, ... FROM table_b MINUS
SELECT col_1, col_2, ... FROM table_a);
row_cmp cur_cmp%ROWTYPE;
begin
open (cur_cmp); -- open cursor
fetch (cur_cmp) into row_cmp; -- try to get the first row
if (cur_cmp%FOUND) -- check if row was found
then
Special cases for compare:
- If all columns are significant and the column order is equal in both tables, you
can declare your cursor like this:
cursor cur_cmp is
(SELECT * FROM table_a MINUS
SELECT * FROM table_b)
UNION
(SELECT * FROM table_b MINUS
SELECT * FROM table_a);
Gombos Bertalan wrote:
> <snip>
> Okay. I know that it raises an exception when result is not a single
> row. My only goal is to compare two tables. I'm not intrested in any
> other result. Supposing A and B are the tables to compare, (A minus B)
> union (B minus A) is empty if tables equals. I would like to decide bye
> one select.
>
> Bye:
> --
> G o m b o s B e r t a l a n
> system engineer, Oracle developer
> mailto:bgombos_at_freemail.c3.hu
--
Jörn Fieg WMD GmbH to replay, delete >remove_this< from the adress Hamburg / GermanyReceived on Mon Nov 22 1999 - 12:43:52 CST