Search code examples
sqloracle-databaseoracle11g

trying to connect using dblink

can i connect to server DPS from server MDC using dblink so i already create dblink from MDC to JKT, JKT to DPS, and DPS to JKT

dblink image

i already try this syntax: select * from (select * from salary@XE.DPS.ID.ORACLE.COM;)@XE.JKT.ID.ORACLE.COM;

but I get an error ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action: Error at Line: 25 Column: 56

Solution

  • That's the same network, isn't it? If so, you can create direct database link from DPS to MDC.

    You would need a "bridge" if databases involved in DPS and MDC aren't "compatible" - for example, database versions difference is too high and you can't establish direct connection. Something like this (note that the following example is just for illustration purposes. Maybe I used wrong database versions, but this is just for you to get the idea what I'm talking about): you're running Oracle 12c on MDC and old Oracle 8i on DPS; then you'd need JKT which runs 10g and is able to communicate with both 8i and 12c.

    As of syntax you used: that won't work, I'm afraid - you can't "embed" queries like that. As of error you got, it is about semi-colon:

    select * 
from (select * 
      from salary@XE.DPS.ID.ORACLE.COM;   <-- this semi-colon
     )@XE.JKT.ID.ORACLE.COM;

    If you really need JKT in between, create a view in that schema (view selects data from salary table located in DPS database):

    -- this is on JKT database
create or replace view v_salary as
  select * from salary@xe.dps.id.oracle.com;

    and then access it from MDC:

    -- this is on MDC database
select *
from v_salary@XE.JKT.ID.ORACLE.COM