dblink

Best way to handle LOBs in Oracle dblink'ed tables

Hi, 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 can do stuff like: select column_a, column_b from data_user.sample_table@TEST_LINK ...

PostgreSQL DbLink Compilation on Solaris 10

After successfully building dblink on solaris 10 using Sun C 5.9 SunOS_sparc 2007/05/03 and gmake. I ran gmake installcheck and got the following output: ========== running regression test queries ========== test dblink ... FAILED ====================== 1 of 1 tests failed. The differences that caused some test...

Why does Char(1) change to Char(3) when copying over an Oracle DBLINK?

I have 2 databases, and I want to transport an existing table containing a CHAR column from database A to database B. Database A is Oracle 9i, has encoding WE8ISO8859P1, and contains a table "foo" with at least 1 column of type CHAR(1 char). I can not change the table on database A because it is part of a third party setup. Database B...

Referencing Oracle user defined types over DBLINK?

I'm working in two different Oracle schemas on two different instances of Oracle. I've defined several types and type collections to transfer data between these schemas. The problem I'm running into is that even though the type have exactly the same definitions (same scripts used to create both sets in the schemas) Oracle sees them as ...

Oracle sql types over dblink

I have two schemas: A and B (Oracle 9). At the A there is a dblink to B. At the B there is a package, that i calls from A. Procedures in B package can returns varying count results and i think that returning a collection is a better way for this reason. create type B.tr_rad as object ( name varchar2(64) ,code number ,vendor ...

Why can't an Oracle XMLType be sent over a DBLink?

I have some PLSQL code that calls a remote procedure in order to send an XMLType. What it does, though, is to devide the XMLType into multiple varchar2(4000) parameters, which the procedure accepts. The remote procedure is called via a DBLink. Why does the XMLType have to be split? Is this restriction applicable to recent database versi...

Query SQL Server from Oracle - force metadata refresh

I am a SQL Server developer, with a task in Oracle. DBA set up a DBLink in Oracle that points at a SQL Server database. I am writing a view on the SQL Server data and then a view on the Oracle side to join it with additional Oracle data. Problem: if I change the definition of the view on SQL Server, even "Select * From myview@dblink" er...

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 way to do this? The following does NOT work: create database link anotheruser.remote_db...

Is it possible to use a database link between an oracle database and a postgresql database on different physical servers

I've trying to streamline an intranet application where some of the data is stored locally on the postgresql database of the intranet server and other related data is stored in our enterprise oracle 10g2r2 database. What I would like to do is create a view on either database box wouldn't really matter that combines for simplicity sake...

Oracle Generic DB Link not working

I'm trying to use oracle's hsodbc generic database link driver to access a postgresql database from my oracle 10gr2 database server. I think I have everything configured but I'm receiving this error from the sqlplus promt after trying a remote query. SQL> select * from temp_user@intranet; select * from temp_user@intranet ...

Is it possible to programmatically create a DBLink in SQL server 2005 in C#?

Is it possible to programmatically create a DBLink in SQL server 2005 in C#? Suppose I have database A and B. I want to create a DBlink in A to connect B. I will capture the B database info from user and create the DBLink in database A. Is this possible in C# .Net version 2.0? ...

MySQL equivalent of PostgreSQL's dblink module?

PostgreSQL's dblink module allows for SQL statements to be written that execute other SQL statements against a remote PostgreSQL database. Is there an equivalent to PostgreSQL's dblink for MySQL? ...

Copying metadata over a database link in Oracle 10g

Thanks in advance for your help experts. I want to be able to copy over database objects from database A into database B with a procedure created on database B. I created a database link between the two and have tweaked the get_ddl function of the dbms_metadata to look like this: create or replace function GetDDL ( p_name in ...

Select and Insert across dblink

I am having a bit of trouble with a select into insert across a dblink in oracle 10. I am using the following statement: INSERT INTO LOCAL.TABLE_1 ( COL1, COL2) SELECT COL1, COL2 FROM REMOTE.TABLE1@dblink s WHERE COL1 IN ( SELECT COL1 FROM WORKING_TABLE) When I run the statement the following is what gets run against the remote ser...

Oracle stored procedure DB Link question

I have a stored procedure FOO belonging to user A. User B has EXECUTE permission on FOO. One of the procedures in FOO uses a DB Link to retrieve data from BARTABLE@REMOTEDB. A has a DB Link to REMOTEDB connecting as user AREMOTE while B has a DBLink with the same name connecting to REMOTEDB but as user BREMOTE. When B executes FOO, w...

Oracle clob over dblink with different charset

Hello, Here is the scenario: Oracle A: charset WE8ISO8859P1 Oracle B: charset WE8MSWIN1252 Oracle A <- dblink -> Oracle B I can't access Oracle B directly, firewall issues :( I have to get some binary files from OracleB and these files are in a column of type CLOB (don't ask me why and I can't change to BLOB). I'm using a "select ...

Why does DbMetal Generate Table Attribute with "main"?

I'm a newbie with DBLink and Sqlite. When DbMetal generates Nortwind.cs (C#) it marks some methods with a Table attribute: [Table(Name="main.Categories")] // C# After I used DbMetal to create a Nortwind.vb (VB.Net) file, the test program aborted because there was no table named "main.Categories". However, when I removed "main.", it ...

Postgres, plpgsql: Is there a way to connect to other DB from inside of a stored procedure?

I have two DB's one is feed by filtered data from another, now i'm using perl script witch executes query on foreign DB, stores a result in a csv file, and loads it to local DB using \COPY sytnatx Is there a way to write plpgsql function witch will connect to foreign DB and load filtered data in local DB ( I know it can be done in ie. p...

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

Hi All,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 to connection problems or anything else),so my question is How to check if the data...

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 dblink('host=dev.toto.com user=toto pass...