Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    6

    Post Unanswered: How do I connect to a diff database within a pl/sql program

    Hi Guys,

    I have two database instances running both servicing different products P1 and P2.

    I would like to call certain apis of the product P2 from database instance 1(which services product P1). Any ideas on how to achieve that.

    The program would ideal look something like this.


    Procedure TEST
    IS

    BEGIN

    Do stuff.....

    Connect to database Instance 2
    -----
    Call the published APis of Product P2 under the relevant schema.
    Disconnect
    ----
    Process the response
    END;

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Exclamation Link or heterogeneous join ?

    Hello,

    if you use an Oracle databases (both instances) you only need to create an database linke to access the second instance. There is no need to connect to the other instance, cause the database link hold all necessary account information. Look into the documentation for creatinf database under CREATE DATABASELINK
    If you have different databases, f.e. on one side Oracle and on the other side MySQL or something else you can use ADBC to join the different tables (or better access the tables from the foreign database).
    If you need further information about heterogeneous joining, please mail me at m.peter@alligatorsql.com.

    To be fair, there is also an Oracle product that supports heterogeneous joining. The access will be done woth ODBC and there are a lot of problems
    using this software (and it is expensive to). Also you can only access the data via ODBC but there is no way to join this with your original table.

    Hope this helps ?

    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com
    Give it a try ... Crossing borders with AlligatorSQL Enterprise Edition

  3. #3
    Join Date
    Dec 2003
    Posts
    6
    Lovely....

    In my case both are Oracle.. though one instance is 8.1.7 and the other is 9i I think but that should be okay I guess...

    Just one question... Are they any known issues in this ???

    Thanks again..

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb

    Hello,

    no problem.
    The best you can do is to create a database link on your "master".
    Create the same user on the "slave" instance.

    Here is an example:

    create database link SLAVE_INSTANCE_NAME
    connect to scott identified by tiger using 'SLAVE_INSTANCE_NAME'

    SLAVE_INSTANCE_NAME = Instancename of the slave

    After the link is created you can access all objects (that are granted to scott on the slave instance)
    with @SLAVE_INSTANCE_NAME

    For example:

    SELECT * FROM test_tab@SLAVE_INSTANCE_NAME
    will select the table test_tab on the slave.

    CREATE TABLE test_123@test_tab and so on

    Hope that helps ?

    Best regards
    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com
    Give it a try ... Crossing borders with AlligatorSQL Enterprise Edition

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •