Hi all,

When I tried to copy data from table1 in db1 to table2 in db2 using DB Link, I got error that "the table doesn't exist" if the table contains a BLOB column.

How can I solve this problem?

Hi,

That's a restriction, I think.

****************************************

From the docs:

LOB Restrictions

LOB columns are subject to the following restrictions:

Distributed LOBs are not supported. Therefore, you cannot use a remote locator in SELECT or WHERE clauses of queries or in functions of the DBMS_LOB package.

The following syntax is not supported for LOBs:

SELECT lobcol FROM table1@remote_site;
INSERT INTO lobtable SELECT type1.lobattr FROM
table1@remote_site;
SELECT DBMS_LOB.getlength(lobcol) FROM table1@remote_site;


(This statement produces error: ORA-22992 cannot use LOB locators selected from remote tables.)

However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns:

CREATE TABLE t AS SELECT * FROM table1@remote_site;
INSERT INTO t SELECT * FROM table1@remote_site;
UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site);
INSERT INTO table1@remote_site ...
UPDATE table1@remote_site ...
DELETE FROM table1@remote_site ...


For the first three types of statement, which contain subqueries, only standalone LOB columns are allowed in the select list. SQL functions or DBMS_LOB APIs on LOBs are not supported. For example, the following statement is supported:

CREATE TABLE AS SELECT clob_col FROM tab@dbs2;


However, the following statement is not supported:

CREATE TABLE AS SELECT dbms_lob.substr(clob_col) from tab@dbs2;

I was searching for a performance related mail-trail on *LOBs over dblink and hit upon this.

We are using following dynamic SQL in our procedure:

g_insertquery := 'insert into ' || x.table_name || '@' || g_database_link_name || ' (select * from ' ||
x.table_name || ' where ' || x.entity_key_column_name || ' = :1)';

and it is used for tables with *LOB columns as well and it works without any problems. This is on 9i.

Hi Anand,

>> I was searching for a performance related mail-trail on *LOBs over dblink

Great! So the moral is that you can insert BLOB's/LOB's over a database link, if you use dynamic SQL, right?

That's very helpful, thanks for sharing . . . .

It is supported. The restriction is against user-defined objects.

By the way, you are trying to perform an insert, not an update, which is what the thread title shows. How would you update a BLOB? That's an entirely different animal.