This discussion is archived
6 Replies Latest reply: Jan 4, 2012 4:21 AM by Marwim RSS

BUG? BRIDGE statement to compare two tables

Marwim Expert
Currently Being Moderated
Hello,

I tried to compare two tables in different dbs and remembered a post about Cross Connectin Queries
http://barrymcgillin.blogspot.com/2010/11/cross-connection-queries.html
BRIDGE temparb AS "EB05 01"
(SELECT * FROM arb)
(SELECT * FROM temparb 
MINUS 
SELECT * FROM arb
)
UNION ALL
(SELECT * FROM arb
MINUS
SELECT * FROM temparb 
);
I would expect to get the differences between the table arb in my current schema and the table arb alias temparb in the other db. Yet it seems, that in this case only the table in my current schema is read.

I materialize the BRIDGE table with
BRIDGE temparb AS "EB05 01"
(SELECT * FROM arb)
and select the value I know to be only in the remote schema
SELECT id FROM temparb WHERE id = 2562;
SELECT id FROM arb WHERE id = 2562;
Both times I get no result. I even tried to use aliases on the table, same result. Tested in 3.0 and 3.1 EA3.

Has anyone tried this before?

Regards
Marcus
  • 1. Re: BUG? BRIDGE statement to compare two tables
    -K- Guru
    Currently Being Moderated
    Haven't tried it, but I use Database Links instead. Why don't you?

    Regards,
    K.
  • 2. Re: BUG? BRIDGE statement to compare two tables
    Marwim Expert
    Currently Being Moderated
    Hello K,

    we don't have the right to create db links. It's not impossible, but we have to ask a dba each time. And if you have to compare 15 dbs BRIDGE would be a handy feature.

    Regards
    Marcus
  • 3. Re: BUG? BRIDGE statement to compare two tables
    Dermot ONeill Journeyer
    Currently Being Moderated
    Hi Marcus,

    Have you tried it without the double quotes around the connection name?


    I have a connection called
    system_local
    which I run the following command in

    drop table testbridge_remote;
    drop table testdbrige;
    create table testbridge(col1 int);
    insert into testbridge values (1);
    insert into testbridge values (2);
    insert into testbridge values (3);
    commit;

    I have a connection called
    Connection Name With Space
    which I run the following in

    drop table testdbrige;
    create table testbridge(col1 int);
    insert into testbridge values (4);
    insert into testbridge values (5);
    insert into testbridge values (3);
    commit;


    Then in the system_local connection/worksheet I can run the following statement


    BRIDGE testbridge_remote as Connection Name With Space(select * from testbridge)
    +(SELECT * FROM testbridge_remote+
    MINUS
    SELECT * FROM testbridge
    +)+
    UNION ALL
    +(SELECT * FROM testbridge+
    MINUS
    SELECT * FROM testbridge_remote
    +);+


    It works ok.
    Returning rows in the remote table not in the local table , and rows in the local table not in the remote table .

    Regards,
    Dermot
    SQL Developer Team.
  • 4. Re: BUG? BRIDGE statement to compare two tables
    Marwim Expert
    Currently Being Moderated
    Hello Dermont,

    there are two errors in my example
    1. The line break in my example after the connection name
    2. The double quotes around the connection name
    BRIDGE temparb AS "EB05 01"
    (SELECT * FROM arb)
    (SELECT * FROM temparb 
    MINUS 
    SELECT * FROM arb
    )
    UNION ALL
    (SELECT * FROM arb
    MINUS
    SELECT * FROM temparb 
    );
    does not work
    BRIDGE temparb AS EB05 01(SELECT * FROM arb)
    (SELECT * FROM temparb 
    MINUS 
    SELECT * FROM arb
    )
    UNION ALL
    (SELECT * FROM arb
    MINUS
    SELECT * FROM temparb 
    );
    works

    Regards
    Marcus
  • 5. Re: BUG? BRIDGE statement to compare two tables
    Dermot ONeill Journeyer
    Currently Being Moderated
    Thanks for the feedback Marcus,
    This feature is only available in SQL Developer.
    There more info available here if its of interest.

    http://dermotoneill.blogspot.com/2010/11/cross-database-bridge-statement.html

    Regards,
    Dermot.
    SQL Developer Team
  • 6. Re: BUG? BRIDGE statement to compare two tables
    Marwim Expert
    Currently Being Moderated
    So the problem with the line break is one of those mentioned at the end
    --The BRIDGE command is still immature and the parsing of its syntax including spaces is still basic.
    ;-)

    I started experimenting with an example I copied from Barry McGillin's blog and it has a line break, until I found, that the line break is only in the blog text, but not in the screenshots.
    http://barrymcgillin.blogspot.com/2010/11/cross-connection-queries.html

    Thank you for the quick reply.

    Regards
    Marcus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points