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

Bug: Cross Connection Query not working in 3.1, 3.2.1

Marwim Expert
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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