8 Replies Latest reply: Mar 4, 2011 3:17 PM by JustinCave RSS

    Connecting to another database

    TexasApexDeveloper
      Hi folks,
      I have a customer that wants to take an instance of XE and have it connect to another Oracle 10g instance on a server. They DO NOT want a dblink used to connect the XE database to their 10g database. Is it possible to add an entry to the XE's tnsnames.ora file to see the 10g database and select from it for som ereporting and nothing else?

      I would prefer to have EITHER APEX installed in the 10g database (Can't) or
      a dblink in the 10g database I could build a view to use on the XE side (Refer back to Can't in prior item)...

      Thank you,


      Tony Miller
      Webster, TX

      On the road of life...There are 'windshields', and there are 'bugs'
      (splat!)
      "Squeegees Wanted"

      If this question is answered, please mark the thread as closed and assign points where earned..
        • 1. Re: Connecting to another database
          Srini Chavali-Oracle
          >
          I have a customer that wants to take an instance of XE and have it connect to another Oracle 10g instance on a server. They DO NOT want a dblink used to connect the XE database to their 10g database. Is it possible to add an entry to the XE's tnsnames.ora file to see the 10g database and select from it for som ereporting and nothing else?
          >

          If the two servers can ping/see each other, then your solution of adding an entry in tnsnames.ora will work.

          http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/gettingstart.htm#sthref134

          HTH
          Srini
          • 2. Re: Connecting to another database
            JustinCave
            Adding an entry to the tnsnames.ora file on the XE machine won't allow the XE database to query the 10g database (though it may be a useful initial step).

            If you don't want to create the database link, the other options I could imagine
            - Create a .Net stored procedure in the XE database that connects to the 10g database and runs a query. I can't imagine why this would be allowed if a database link is disallowed but it is not technically a database link. You could do the same thing with a Java stored procedure but my understanding is that Java stored procedures aren't allowed in XE
            - Create a DBMS_SCHEDULER job (or a .Net stored procedure) that calls a script on the XE database that connects to the 10g database (potentially via SQL*Plus) and then load the results into XE
            - Create a web service on the 10g database that the XE side can call via UTL_HTTP to extract whatever data is needed

            Justin
            • 3. Re: Connecting to another database
              clcarter
              Is it possible to add an entry [ to the ] tnsnames.ora
              That's exactly how to configure a client. The important bits are:

              1. host (where the database lives)
              2. port (how to get to the listener)
              3. ORACLE_SID or service_name ( particulars for the instance)

              If a database user is set up with the same user name and password in each database, setting up the link is fairly simple. Its much easier to connect via a link than it would be to take a schema export for every target database and load it up in a different instance, unless one likes busy work ;)

              The 10g net services doc at http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/part1.htm#i997104 is a good place to get started.
              • 4. Re: Connecting to another database
                JustinCave
                Srini,

                Perhaps I'm missing something. Adding the entry to the tnsnames.ora file would allow an executable on the system where the XE database is running to connect to the 10g database. But it would not allow the XE database to connect to the 10g database without jumping through additional hoops to effectively shell out from the XE system to call one of those external executables. Adding the tnsnames.ora entry would obviously make it easier to create a database link to the 10g system, but that seems to be disallowed.

                Justin
                • 5. Re: Connecting to another database
                  Srini Chavali-Oracle
                  Justin,

                  Agreed - I am assuming the requirement is something as basic as using sqlplus to connect to the remote database.

                  >
                  ... Is it possible to add an entry to the XE's tnsnames.ora file to see the 10g database and select from it for som ereporting and nothing else?
                  >

                  If the requirement is something more complex, then the solution would be different :-)

                  Thanks
                  Srini
                  • 6. Re: Connecting to another database
                    TexasApexDeveloper
                    What is required is this:
                    The customer has a database owned by another company, the other company does NOT want a dblink built in the server that contains the data they want to do reports on. We want to use APEX in teh XE database to generate some reports for end users (Customer is using MS Access to download data and do reports.. Yuck...)

                    So if I add an entry into the XE's tnsnames.ora and try to connect to the other server and run selects, will this work?

                    Thank you,

                    Tony Miller
                    Webster, TX
                    • 7. Re: Connecting to another database
                      zaferaktan
                      sure you can, we do similar stuff with XE+APEX on Ubuntu and Db server running on a Sparc Box - have shell scripts controlling the flow. We also use the external directories. One system (Oracle) mounts and writes to that, and other can read from the same external directory (check Ask Tom for external directories - he also have a new article in the latest Oracle magazine, which I read yesterday). You can do similar stuff even if you are running XE+Apex on Windows and Oracle DB server of your customer is also on Windows (or Unix).
                      • 8. Re: Connecting to another database
                        JustinCave
                        If you want the XE database to read data from the proprietary database, you would need a database link created in the XE database that points to the proprietary database. From the standpoint of the remote database, an incoming connection is an incoming connection regardless of whether it is coming via a database link. Unless the vendor is being a real pain and is doing something like checking V$SESSION to try to determine whether an incoming connection is from another Oracle database server, that shouldn't create any issues. Now, for basic security reasons, you may want to create a new user that the database link would use to connect to the vendor's database, but that's the only intervention that should be necessary on the vendor system.

                        Assuming you want to create a database link on the XE system that connects to the vendor's database, having the tnsnames.ora entry on the XE system certainly simplifies the syntax.

                        Justin