CODEWITHSUNDEEP
oracle-database
Uzi
Uzi

Reputation: 453

How query from another database that is in another machine/server

I've been looking for answer to this but I can't seem to find the right answer online and my problem goes like this.

I'm trying to query a set of records from another table which is in an another database installed in a different machine. To make it clearer:

My stored procedure is running on IP: 192.168.XX.X1. I get to retrieve all the information I need in this server but I have another set of information or records that can only be retrieved from IP: 192.168.XX.X2.

I was thinking to achieve something like:

DECLARE
    -- given that both queries will only return 1 record
    CURSOR IS curSample1
        SELECT * FROM Database1.Table1;
    colSample curSample1%ROWTYPE;

    CURSOR IS curSample2
        SELECT * FROM Database2.Table1;
    colSample curSample2%ROWTYPE;

    vText1 VARCHAR(20);
    vText2 VARCHAR(20);
BEGIN
    OPEN curSample1;

    LOOP
        FETCH curSample1 INTO colSample1;
        EXIT WHEN curSample1%NOTFOUND;
            vText1 := colSample1.Column1;
    END LOOP;

    CLOSE curSample1;

    OPEN curSample2;

    LOOP
        FETCH curSample2 INTO colSample2;
        EXIT WHEN curSample2%NOTFOUND;
            vText2 := colSample2.Column2;
    END LOOP;

    CLOSE curSample2;

    dbms_output.put_line(vText1 || ',' || vText2);
END;

Any help you could provide will be much appreciated. Thank you very much.

Note: I'm trying this approach as this is the only way we could possibly do it as of now. Thanks again.

Upvotes: 1

Views: 4199

Answers (2)

phonetic_man
phonetic_man

Reputation: 1088

You will have to create a db link between your database 1 and database 2. For creating a database link it is not required to have both databases on the same server. Since in your case the databases are on different server you can start with the following steps.

You need a tns entry (pointing to database 2) in the tnsnames.ora file on your database 1 server. You can check if you have this entry by connecting to SQLPLUS from your database 1 machine to database 2.

sqlplus <username>/<password>@<tnsnames of database2>

If you are able to connect from your database 1 server then you can proceed with the following steps for creating the db link.

CREATE DATABASE LINK <dblink_name> CONNECT TO <username> IDENTIFIED BY <password> USING <tnsnames of database2>

Post this you can test your database link by running the following SQL command.

select * from Table@<dblink_name>;

Upvotes: 3

Ming
Ming

Reputation: 211

as i know you cannot query data cross database directly.

1,maybe you can use DBlink or DataSync to let the data which in other database can be query.

2,instead of pl/sql procedure, use other development language to do cross DB process is a good idea(ex independent java program).

3,instead of pl/sql procedure, use Oracle Java Procedure to do this.

Upvotes: 1

Related Questions