7 Replies Latest reply on Aug 2, 2019 12:51 PM by EdStevens

    Can you work with multiple connected databases the way you can in SAS

    rjsosi

      Hi

       

        We're using SQL Developer Version 19.1.0.094.

       

      I know you can have multiple databases connected to the product at the same time.

       

      Is it possible to work with these multiple database the way you can in SAS where you can query table data from each exclusive databases against each other?

       

      I see Database Diff in the tools section with a source and a destination but I fear it might just be a source schema and a destination schema from with in the same database.

       

      Other wise can we work among multiple databases, in other words, can I query MYSQL objects along with Netezza objects concurrently? That sort of thing...

       

      Thanks!

        • 1. Re: Can you work with multiple connected databases the way you can in SAS
          thatJeffSmith-Oracle

          yes, you just need to create database links

           

          then you can query across that from one database to another

          • 2. Re: Can you work with multiple connected databases the way you can in SAS
            rjsosi

            How though? I "Googled" this a few times and have yet to find any documentation or even web links that supports this?

             

            Are there any other sources out there about this I could go to?

             

            Thanks again!

            • 3. Re: Can you work with multiple connected databases the way you can in SAS
              EdStevens

              rjsosi wrote:

               

              How though? I "Googled" this a few times and have yet to find any documentation or even web links that supports this?

               

              Are there any other sources out there about this I could go to?

               

              Thanks again!

              You coulnd't find anything on database links?

              I'll give you a hint ... it's not a feature of SQL Dev, it's a feature of the database itself. So perhaps you were looking in the wrong places.

              https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205

               

              The idea is that once you create a database link, pointing to another database (which doesn't have to be oracle) you reference that link in your SQL.

               

              Off the top of my head: (there may be minor syntax errors)

               

              CREATE DATABASE LINK FUBAR

                CONNECT TO SCOTT IDENTIFIED BY "TIGER"

                USING "fubar";

               

              Then create an entry for "fubar" in the tnsnames.ora of the server running the database where the link is created:

               

              FUBAR =

                (DESCRIPTION =

                  (ADDRESS_LIST =

                    (ADDRESS = (PROTOCOL = TCP)(HOST = other_db_host_name)(PORT = 1521))

                  )

                  (CONNECT_DATA =

                    (SERVICE_NAME = fubar)

                  )

                )

              Then reference it in your SQL:

               

              select * from emp@fubar

               

              • 4. Re: Can you work with multiple connected databases the way you can in SAS
                rjsosi

                Also,and just for example, I currently have a mysql connection and a DB2 connection that are both working. What would I do if I wanted to do a query where I join a table from MySQL to the DB2 database?

                 

                Do you have any example syntax?

                 

                Let's say I created the SCOTT/TIGER training database from Oracle and I have the Employee database in DB2 and the Department database in MYSQL.

                 

                How would you write the following query to combine the two tables from both?

                 

                I'm going to substitute what the DB link would be in front of the table names using just the name of the database type ( so the DB2 table is called DB2.employee and the MYSQL table is going to be called MYSQL.department)

                 

                So it would be something like

                 

                Select e.name , e.deptno, d.sal
                from DB2.employee e
                join MYSQL.department d
                on e.empno = d.empno;
                

                So what would the SQL Developer version of this be? Also include any block phrasing around the code : For example from a SAS block, If I did a query I'd put

                 

                proc sql;
                
                Select e.name into :ename, e.deptno into :deptno, d.sal into :sal
                from DB2.employee e
                join MYSQL.department d
                on e.empno = d.empno;
                
                quit;
                

                 

                With the "proc sql;" and the "quit;"  which are used in SAS to denote the head and the tail of that block.

                 

                How would we do this in SQL Developer?

                • 5. Re: Can you work with multiple connected databases the way you can in SAS
                  rjsosi

                  You know I was writing my question to the other person using the same Scott/tiger type example before I read you answer.

                   

                  Knowing a bit of oracle I know you're creating a link from your current oracle tnsnames file to your target 'foreign' db, then using that link name to connect to your foreign table.

                   

                  So it would have to be from the environment of your current DB like, say DB2 to create a link over to MYSQL and you query would look something like:

                   

                  Select e.name into :ename, e.deptno into :deptno, d.sal into :sal  
                  from employee e  
                  join department@MYSQL d  
                  on e.empno = d.empno;
                  

                   

                  Using the link called MYSQL.

                   

                  So you have no real interactive environment, like in SAS, you're just creating links , which might be problematic if you don't have administrative privileges.

                   

                  What about installing an Oracle database  locally and using that as your interactive environment?

                   

                  Then you could create your links locally in your tnsnames file as well as use PL/SQL as your interactive programming language.

                  • 6. Re: Can you work with multiple connected databases the way you can in SAS
                    Glen Conway

                    The other posters talked about database links to non-Oracle databases, but knowing the right words are everything when it comes to searching for things on the web.  Oracle terminology that will help you find stuff:

                     

                    1) Oracle Heterogeneous Services

                    2) Oracle Gateways / Oracle Transparent Gateways

                     

                    Try reading over a recent document for 12c ( https://docs.oracle.com/en/database/oracle/oracle-database/12.2/heter/index.html  ) or even really old documentation going back to 8i ( https://docs.oracle.com/cd/A87860_01/doc/server.817/a76960/hs_conce.htm  )

                     

                    The general idea?  Each gateway to a specific non-Oracle database makes that database look like another Oracle database to your Oracle session.  I assume each gateway requires a license.  Not sure if that will be an obstacle for you.

                     

                    Cheers

                    • 7. Re: Can you work with multiple connected databases the way you can in SAS
                      EdStevens

                      Glen Conway wrote:

                       

                      The other posters talked about database links to non-Oracle databases, but knowing the right words are everything when it comes to searching for things on the web. Oracle terminology that will help you find stuff:

                       

                      1) Oracle Heterogeneous Services

                      2) Oracle Gateways / Oracle Transparent Gateways

                       

                      Try reading over a recent document for 12c ( https://docs.oracle.com/en/database/oracle/oracle-database/12.2/heter/index.html ) or even really old documentation going back to 8i ( https://docs.oracle.com/cd/A87860_01/doc/server.817/a76960/hs_conce.htm )

                       

                      The general idea? Each gateway to a specific non-Oracle database makes that database look like another Oracle database to your Oracle session. I assume each gateway requires a license. Not sure if that will be an obstacle for you.

                       

                      Cheers

                      Yes, Oracle Transparent Gateway (heterogeneous services) comes into play if the target db is not Oracle. But it still comes back to creating db links. 

                      As for licensing for OTG, there is no cost if one uses OTG for ODBC.  There is a cost if one uses db-specific OTG connectors.

                       

                      https://edstevensdba.wordpress.com/2018/09/19/the-oracle-transparent-gateway/