4 Replies Latest reply: Jun 22, 2010 4:36 PM by 646237 RSS

    Accessing database/schema on another server...

      Hello - I have a need that will require me to access additional information on a schema on a different server than what my APEX installation is running on (ie 3rd party data that is not part of the inherent schema that APEX can access)...

      My APEX server is running version on top of oracle 11g Enterprise Edition Release - 64bit Production. Let's call this server "A".

      The target server is currently an Oracle server, but will eventually be migrating to a Teradata installation (no - i have no say in the matter!). Let's call this server "B". No idea what versions for either case.

      I understand that if I needed to access different schemas on the same server (ie server A) that it would be easy enough to do using grant statements.

      Also, I understand that to access a schema on server B I could use a DBLink to do so.

      My questions are:

      1) Can Oracle make a DBLink to teradata? I found the following thread that seems to indicate that it is possible (or at least used to be - not sure if it is still a valid setup) Re: Connecting To Teradata from Oracle

      2) Is there an alternative way of making this external data source available? A tech in our "IT Architecture" team says that DBLinks are not recommended or a best practice. He suggests adding the datasource "directly"?

      This thread (add multiple schemas in one workspace seems to talk about adding multiple schemas to a workspace but I do not have access to that part of our oracle server (i'm only a workspace admin).

      3) If I create a view that accesses the tables across the DBLink to server B when it is Oracle and then update the DBLink to point to the new teradata server when the migration occurs - will it break anything within APEX?

      My assumption is that as long as the table names are the same APEX only cares that the view is valid and not what powers the view.

      The rationale I was given against using DBLinks was that "it doesn't make sense for APEX to go down to the oracle database (its native underlying server/schema) to go across to another server and back to the oracle database which then gets passed up to APEX. It makes more sense for APEX to go right to the other data source". Normally I would say that they (Apex/infrastructure) are the same server so it doesn't really matter that it might have to pass through 1 additional "service" or "interface" but this area is not my specialty.

      Also - I think technically speaking our APEX service is already separated from it's native schema/data on different servers (for load balancing) so in that case there really might be a middle server getting in the way (ie APEX Server A-> native schema/data server where the DBLink would be -> Server B) making an extra hop unnecessary (APEX Server A -> Server B). Note: I'm pretty sure our architect doesn't know this is the case so that is not part of his rationale.


        • 1. Re: Accessing database/schema on another server...
          1) There are a wide variety of databases you can access from Oracle using db links. The main purpose of a db link is to provide connectivity between non-homogeneous databases via use of some type of driver or translator. Few databases provide native connectivity with anything other than their own products.

          2) I would ask this other dba source what he/she would recommend and if he/she has any documentation. The long and the short of it is (as I explained above) few database vendors provide native support for other database engines, which is necessary for the kind of "direct connection" this person is implying. My suspicion is that this person is an ODBC user and equates the use of ODBC drivers with "direct connections", which is far from accurate. ODBC only provides a generic interface to a given database, at the cost of speed, functionality, and efficiency due to command translation and overhead.

          3) Oracle in general doesn't care about the back end of a connection to another database and neither does APEX. As long as you can create a database link to the database in question, you shouldn't have any problems querying against that data, albeit at a reduced efficiency due to transport overhead, networking, drivers, etc.

          Really, APEX is meant to be run against and integrates best with Oracle databases. If your main data resides on another platform, APEX may not be the best solution for your needs.
          • 2. Re: Accessing database/schema on another server...

            Thanks for the quick reply. Just had a meeting with my co-worker so I have some more clarification.

            He suggested using direct database connections to the different data sources at the application layer. For instance, if you think about PHP or Java, you can use something like this:

            $dbhost = 'localhost';
            $dbuser = 'root';
            $dbpass = 'password';

            $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

            $dbname = 'petstore';

            He was wondering if you can do something like this in APEX. My guess is that you cannot (or at least not in a pretty or easy way) and that DBLinks would be the only solution. Furthermore, with a DBLink it seems as though you centralize server connection information whereas this method would involve keeping that logic at the page item level (which would be a pain to update if the migration to teradata required it).
            • 3. Re: Accessing database/schema on another server...
              Ah, he's used to using PHP.

              Okay, so here's the thing. APEX is essentially replacing PHP as your database integration layer. I've worked with PHP, and it is very flexible, but it also has to be installed as a module on the webserver engine. Having never tried to mix application engines (APEX + mod_php), I have absolutely no idea what would happen. Maybe one of the gurus can chime in here.

              If you could get APEX and PHP to cohabitate, I don't see any reason you couldn't do what your co-worker is suggesting. I guess the question I would want to answer is why. If you are trying to combine disparate systems, I think I would opt to go with straight PHP just to make things simpler and easier to debug. I would anticipate that it would be a nightmare to try to have to figure out which rendering engine (PHP or APEX) was doing the work and when. Thinking about it in those terms, I don't think you can combine APEX and PHP, because whatever you did, one engine would be submitting something to the other to do. Sounds really messy.
              • 4. Re: Accessing database/schema on another server...
                I think he was using java/php as an example versus actually trying to say we need to integrate the two technologies. In other words, is there a PL/SQL equivalent to what it is he is used to?? (he actually uses java more i think)

                Anyway - it seems as though it is not possible (to specify machine data sources in a query) at the application level, so I will push forward with the DBLink approach. The only concern here is that it is an unsupported technology from our companies viewpoint so there is some risk that a year from now we could find ourselves with a broken app when the move to teradata occurs.