7 Replies Latest reply: Nov 15, 2012 10:46 AM by Marwim Branched to a new discussion. RSS

    Bug: Cross Connection Query not working in 3.1, 3.2.1

    Marwim
      Hello,

      in {message:id=10066256} i learned how to use Cross Connection Queries. In 3.1 and 3.2.1 it seems not to work any more? Has this feature been removed or the syntax changed?
      When I use an example that works in 3.0 I only get
      ORA-00900: invalid SQL statement
      Regards
      Marcus
        • 1. Re: Bug: Cross Connection Query not working in 3.1, 3.2.1
          Dermot Oneill-Oracle
          Hi Marcus,

          I tried your example in the previous post and it works ok for me in SQL Developer 3.2.1
          Are you sure the statement is identical in both SQL Dev 3.0 and 3.2.1 ?
          Are you sure the connection names are identical in both SQL Dev 3.0 and 3.2.1?
          Can you provide a simple example that works in SQL Dev 3.0 but does not work in 3.2.1.

          Thanks
          Dermot.
          SQL Developer Team
          • 2. Re: Bug: Cross Connection Query not working in 3.1, 3.2.1
            Marwim
            Hello Dermot,

            it took some time, because we are on the verge of a new release ;-)

            In SQL Developer 3.0
            I create a connection "test 1" for a schema and run
            CREATE TABLE test_ccq(
              x  NUMBER
              );
            INSERT INTO test_ccq(x)
            SELECT     LEVEL + 1
            FROM     dual
            CONNECT BY LEVEL < 20;
            
            COMMIT;
            I create a connection "test 2" for another schema in the same database and run
            CREATE TABLE test_ccq(
              x  NUMBER
              );
            INSERT INTO test_ccq(x)
            SELECT     LEVEL
            FROM     dual
            CONNECT BY LEVEL < 20;
            
            COMMIT;
            From connection "test 1" I execute
            BRIDGE TEMPccq AS test 2(SELECT * FROM test_ccq)
            (SELECT * FROM TEMPccq
            MINUS
            SELECT * FROM test_ccq
            )
            UNION ALL
            (SELECT * FROM test_ccq
            MINUS
            SELECT * FROM TEMPccq
            );
            As expected I get two rows as difference
            X
            --
             1
            20
            I export the connections and import them in
            SQL Developer 3.2.1
            When I execute the same query I get
            ORA-00900: invalid SQL statement
            00900. 00000 -  "invalid SQL statement"
            *Cause:    
            *Action:
            Using a simpler select does not work
            BRIDGE TEMPccq AS test 2(SELECT * FROM test_ccq)
            SELECT * FROM TEMPccq
            Using connection names without spaces does not work.


            ------------------------------------
            Running on Win XP SP3

            SQL Developer 3.0
            Version 3.0.04.34
            Java 1.6.0_11

            SQL Developer 3.2.1
            Version 3.2.10.09.57
            Java 1.6.0_11

            Changed from default settings
            in sqldeveloper.conf
            AddVMOption -Dide.user.dir=T:\Sqldeveloper


            Setting the GUI language from default German to English makes no difference
            AddVMOption -Duser.language=en

            All extensions (Tools - Preferences - Extensions) are unchecked, except
            - Real Time SQL Monitoring
            - Scheduler
            - Snippet
            - Data Modeler
            - Versioning Support

            Regards
            Marcus
            • 3. Re: Bug: Cross Connection Query not working in 3.1, 3.2.1
              Dermot Oneill-Oracle
              Hi Marcus,

              I know there has to been a change to connection naming recently.
              Spaces are no longer allowed. I'm not sure how these are handled during an upgrade of SQL Developer, but I'm guessing this is the issue you are experiencing.

              I was unable to create a connection with a space in the name, using SQL Developer 3.2.1
              But if I name the connections test_1 and test_2 , the bridge command works ok.

              http://dermotoneill.blogspot.com/2012/10/cross-connection-query-issue.html

              jdk:1.6.0_21
              win 7
              straight install, not an upgrade....

              Regards,
              Dermot
              SQL Developer Team
              • 4. Re: Bug: Cross Connection Query not working in 3.1, 3.2.1
                Marwim
                Hello Dermont,

                as said above I deactivated some extensions +(Tools - Preferences - Extensions)+ to speed up starting SQL Developer. Now I startet a new install without migrating preferences from another version, then I deactivated the extensions one by one. It seems Cross-Connection-Queries need the Migrations Core extension, because when I activate it the query works.

                Regards
                Marcus
                • 5. Re: Bug: Cross Connection Query not working in 3.1, 3.2.1
                  Dermot Oneill-Oracle
                  Thanks Marcus,

                  Sorry about that, I didn't pick up on the disabled extensions, when mentioned.
                  The BRIDGE command was developed for querying from non Oracle databases to Oracle databases. That's why it lives in the migration extension.
                  It works against two Oracle databases, but database links maybe more suitable and supported by SQL*Plus and all other tools.
                  BRIDGE only works in SQL Developer.

                  Anyway thanks for the update.
                  Regards,
                  Dermot
                  SQL Developer Team.
                  • 6. Re: Bug: Cross Connection Query not working in 3.1, 3.2.1
                    Marwim
                    but database links maybe more suitable and supported by SQL*Plus and all other tools
                    Sure, but the use of DB-links is restricted here and Cross-Connection-Queries are handy if you just need to compare two tables.
                    • 7. Re: Bug: Cross Connection Query not working in 3.1, 3.2.1
                      Marwim
                      Hello Dermont,

                      just to add another "feature" ;-)
                      Dragging a table into a worksheet to get a select statement also depends on Migration Core.

                      Regards
                      Marcus