Stack Overflow
Stack Overflow

Questions tagged [dblink]

An object or module that enables access to a remote database.

In Oracle a database link is a schema-level object that enables users to access objects on a remote database. It is possible to create a database link to access a non-Oracle database.

In PostgreSQL dblink is a module, which enables connections to other PostgreSQL databases, from within a session.

Questions tagged should also be tagged with the appropriate RDBMS.

523 questions
5
votes
6 answers

How to check if a database link is valid in Oracle?

I have a main database with only setup data at the headquarter and several databases at different branches.I created a database link for each branch server. In some case I would like to query all the valid links (as some links could be invalid due…
Khaled
  • 841
  • 3
  • 13
  • 22
5
votes
1 answer

Error: a column definition list is required for functions in dblink using PostgreSQL 9.3

I have the following function: In which I am updating one database table by joining other database table by using the dblink(). I have installed: create extension dblink; The more details as shown below: CREATE OR REPLACE FUNCTION Fun_test ( …
MAK
  • 6,824
  • 25
  • 74
  • 131
5
votes
1 answer

How to have sysdate result remoted across database link?

I'm running a query across a database link to a Sybase server from Oracle. In it's where clause is a restriction on date, and I want it tied to sysdate, so something like this: select * from some_remote_view where some_numeric_key = 1 and …
Trant
  • 3,461
  • 6
  • 35
  • 57
5
votes
2 answers

Drop or create database from stored procedure in PostgreSQL

I have a function that looks like this: BEGIN DROP DATABASE IF EXISTS db_1; END; I'm getting the following error: ERROR: DROP DATABASE cannot be executed from a function or multi-command string. Is it not possible to drop a database from a…
Rudecles
  • 199
  • 3
  • 13
5
votes
1 answer

postgresql slow query (dblink and inner join)

I have this query containing a dblink since I need to connect to another database and it seems so slow (50.343 seconds for just 124 records). Is there any way to make it fast? Below is the code: select * from customer INNER JOIN …
Patrick Pelino
  • 53
  • 1
  • 1
  • 4
5
votes
2 answers

What does @ ! mean in a From Statement

I am trying to determine what this code is doing (Oracle SQL) — especially the at-sign exclamation mark in the from clause. INSERT INTO "LOCATIONS" "A1" ("LOCATION_ID", "SEQUENCE", "POINT_TYPE") SELECT…
user739866
  • 891
  • 1
  • 9
  • 18
4
votes
2 answers

INSERT ALL INTO a table over a dblink .. Is it possible?

When I execute the following: INSERT ALL INTO table@database_link(columnName) VALUES (columnValue) SELECT columnValue FROM localTable; I get an error SQL Error: ORA-02021: DDL operations are not allowed on a remote database 02021. 00000 - …
vicsz
  • 9,552
  • 16
  • 69
  • 101
4
votes
1 answer

cannot query SQL Server system tables over db link created using DG4MSQL

I am trying to create db link from Oracle 11g to SQL Server 2005 using DG4MSQL gateway. After creating db link I am not able to query SQL Server system views (sys.services or sys.objects) using JDBC driver, but I am able to query all user tables…
hnm
  • 789
  • 2
  • 9
  • 24
4
votes
1 answer

PostgreSQL ERROR: duplicate connection name

I have a function, and after added some changes, I started to get ERROR: duplicate connection name Function dropped and created new one Here My function create extension dblink; create or replace function Log_Save (Moderator integer, Subject…
Igor Cova
  • 3,126
  • 4
  • 31
  • 57
4
votes
1 answer

How can I close Oracle DbLinks in JDBC with XA datasources and transactions to avoid ORA-02020 errors?

I have a JDBC-based application which uses XA datasources and transactions which span multiple connections, connected to an Oracle database. The app sometimes needs to make some queries using join with a table from another (Oracle) server using a…
aro_biz
  • 131
  • 3
  • 8
4
votes
2 answers

Select * From table As a table dblink

To select from another database I try to use dblink or fdw extension of Postgres, like this: CREATE EXTENSION dblink; SELECT * FROM dblink ('dbname = bd_name port = 5432 host = 10.6.6.6 user = username password = password', 'SELECT id, code FROM…
Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140
4
votes
1 answer

Postgresql create stored database link

I know that in Oracle it's possible to create stored dblink and after that use it in query. For example: Script for creation dblink: CREATE PUBLIC DATABASE LINK my_link CONNECT TO my_schema IDENTIFIED BY shema_password USING 'remote'; And after…
HAYMbl4
  • 1,450
  • 2
  • 15
  • 29
4
votes
3 answers

Managed ODP.NET Calls to PUBLIC Database Link tables cause TNS errors

Our application uses managed ODP.NET code to call a variety of Oracle Procedures. For one of our clients, who is using public database links, and referencing the linked tables within the procedure, that call is failing. Upon further testing, any…
Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
4
votes
2 answers

Execute multiple queries as one dblink transaction

I am working in a Java application where I need to execute these the two queries (as Strings in java) at the same time and rollback the transaction if there where errors. SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname…
Kaz Miller
  • 949
  • 3
  • 22
  • 40
4
votes
2 answers

postgresql trigger with dblink doesn't return anything

I created a trigger to replicate inserts from a table 'mytable_db1' in database1 into the same table 'mytable_db2' on database2. Both databases are on the same server. CREATE OR REPLACE FUNCTION trigger_osm_test_insert() RETURNS trigger…
Thierry
  • 65
  • 1
  • 5
1 2
3
…
34 35