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
191
votes
8 answers

postgresql: INSERT INTO ... (SELECT * ...)

I'm not sure if its standard SQL: INSERT INTO tblA (SELECT id, time FROM tblB WHERE time > 1000) What I'm looking for is: what if tblA and tblB are in different DB Servers. Does PostgreSql gives any utility or has any functionality…
Mayank
  • 5,454
  • 9
  • 37
  • 60
85
votes
9 answers

How do I do large non-blocking updates in PostgreSQL?

I want to do a large update on a table in PostgreSQL, but I don't need the transactional integrity to be maintained across the entire operation, because I know that the column I'm changing is not going to be written to or read during the update. I…
S D
77
votes
7 answers

How to use (install) dblink in PostgreSQL?

I am used to Oracle and to create a dblink in my schema and then access to a remote database like this: mytable@myremotedb, is there anyway do to the same with PostgreSQL? Right now I am using dblink like this: SELECT logindate FROM…
Spredzy
  • 4,982
  • 13
  • 53
  • 69
64
votes
6 answers

Postgres Error: More than one row returned by a subquery used as an expression

I have two separate databases. I am trying to update a column in one database to the values of a column from the other database: UPDATE customer SET customer_id= (SELECT t1 FROM dblink('port=5432, dbname=SERVER1 user=postgres password=309245', …
user3182502
  • 671
  • 1
  • 5
  • 5
57
votes
4 answers

PostgreSQL: Query has no destination for result data

I am trying to fetch data from remote db by using dblink through function but getting an error "query has no destination for result data". I am using plpgsql language to do the same. Function: CREATE OR REPLACE FUNCTION fun() RETURNS text…
Sarfaraz Makandar
  • 5,933
  • 16
  • 59
  • 84
33
votes
3 answers

ERROR: function dblink(unknown, unknown) does not exist

I have defined a foreign server pointing to another database. I then want to execute a function in that database and get back the results. When I try this: SELECT * FROM dblink('mylink','select someschema.somefunction(''test'', ''ABC'')') or…
HuFlungPu
  • 501
  • 2
  • 6
  • 9
23
votes
8 answers

How to create a DB link between two oracle instances

How to create a DB link between two Oracle instances. Let's say A and B are two instances. I want to access the data in instance B from the instance A.
Arunkumar
  • 279
  • 1
  • 3
  • 7
17
votes
2 answers

Installing DBLink for Postgres 9

Can someone please shed some light on how to install the requirements for Postgres 9 (Centos) to be able to use dblink for inter-postgres db querying. There seems to be no clean documentation out there on how to install dblink requirements in…
Matty Bear
  • 517
  • 1
  • 4
  • 16
17
votes
3 answers

How to SELECT in Oracle using a DBLINK located in a different schema?

We have an Oracle DBMS (11g) and the following configuration: A DB user "MYUSER" Two schemas "MYUSER" and "SCHEMA_B" User "MYUSER" can access "SCHEMA_B" and has READ permissions on its tables A public DB link "DB_LINK" located in "SCHEMA_B" The…
Steffen
  • 171
  • 1
  • 1
  • 4
16
votes
3 answers

Specify dblink column definition list from a local existing type

I am using dblink to move certain data between databases. Everything is save and sound but I am wondering if there is a more convenient way to define the column definition list of a dblink query result. I can do something like this: SELECT * FROM…
bentrm
  • 1,018
  • 3
  • 10
  • 26
15
votes
2 answers

postgres dblink escape single quote

Related Link: String literals and escape characters in postgresql Here is my error: ERROR: type "e" does not exist Here is my query: SELECT * FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword', …
Phill Pafford
  • 83,471
  • 91
  • 263
  • 383
14
votes
6 answers

Best way to handle LOBs in Oracle distributed databases

If you create an Oracle dblink you cannot directly access LOB columns in the target tables. For instance, you create a dblink with: create database link TEST_LINK connect to TARGETUSER IDENTIFIED BY password using 'DATABASESID'; After this you…
morais
  • 2,951
  • 5
  • 27
  • 27
13
votes
3 answers

Oracle: Using a database link in a stored procedure : table or view does not exist

I currently have an issue whereby I cannot reference a table in a linked database within a stored procedure. I get the error message: ORA-00942: table or view does not exist Here are the steps I took on the host machine (running oracle 10g) to set…
Clarkey
  • 1,553
  • 5
  • 22
  • 34
11
votes
5 answers

How to use synonym of a DBlink in Oracle?

I have created a synonym for a dblink. create synonym dblink2 for dblink1 But when I query anything using the synonym instead of the dblink, I'm getting connection description for remote database not found error. SELECT * FROM DUAL@DBLINK2 How do…
Nitish
  • 1,686
  • 8
  • 23
  • 42
10
votes
3 answers

Oracle Syntax for Creating Database Link Owned by Another User

The typical syntax for creating a db link is as follows: create database link remote_db_link connect to remote_user identified by remote_password using 'remote_db' But I'd like my DB link owned by another account after it's created. Is there a…
Jeff
  • 8,020
  • 34
  • 99
  • 157
1
2 3
…
34 35