6 Replies Latest reply: Jan 4, 2012 6:21 AM by Marwim RSS

    BUG? BRIDGE statement to compare two tables

    Marwim
      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-
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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