Remote CLOB table access via DBLINK in ora-22992

Access Remote Using select statements locally, if the remote table has a CLOB field, it will be wrong: ora-22992;
If you really want to see the content of the clob field, you have to create a table locally and use the following two statements:
I just tested insert into table select * from remote table successfully.
Remote table contains CLOB
Conclusion: successful in my environment
(1) create table aaa select * from remote table
(2) insert into table select * from remote table

However, there are also the following statements on the Internet, although there are mistakes, but it is also a method,

Using a temporary table to obtain blob objects from dblink at, 2006-12-05, is a good choice for data sharing through database link. But the real use of dblink but encountered a small problem: from the remote database query blob field always return ORA-22992 error, as follows:

Select blobcolumn from remotetable @ dl_remote;

ORA-22992: unable to use the lob locator selected from the remote table

After finding the solution, someone suggested using materialized views to solve this problem. The only drawback of materialized views is the synchronization mechanism. If the synchronization time is set too short, a large amount of system resources are occupied, which puts a lot of pressure on the server. If the setting time is too long, foreground users are unacceptable.

Later, asktom gave an excellent solution: using a global temporary table.

SQL> Create global temporary table foo

2 (

3 x blob

4)

5 on commit Delete rows;

Table created

SQL> insert into Foo select blobcolumn from remotetable @ dl_remote where rownum = 1;

1 row inserted

SQL>

After the local temporary table is inserted, there is no problem in local operations.

 

 

 

 

 

-- Another article

Discussion on DBLink problem ORA-22992 in Oracle Forum

 

After finishing my work, the sentence is the final answer. I believe that IT is necessary for everyone to understand what to say without translation. Don't know if this is related, but we were also having a problem that was causing the ORA-22992 error, and the solution turned out to be surprisingly simple. A full day of searching the web didn't turn up this answer, but then one of our DBAs accidentally stumbled over something buried in some Oracle documentation that provided the answer. we have a database table that contains a couple primary key fields (a varchar and an integer), plus a BLOB that holds Word documents ENTs. one of our programs needs to be able to connect to a remote Oracle instance and copy Word privileges ents based on certain primary keys. our code first attempted to do that like this: insert into [local Word doc table] ([key column1], [key column 2], [blob column])
Values ('[key 1 literal]', [key 2 literal],
(Select [blob column] from [Word doc table] @ [remote instance]
Where [keys = remote keys]) Attempting to execute that was giving us the "cannot use LOB locators selected from remote tables" error. the documentation that our DBA turned up was ded a bunch of SQL examples of using remote BLOBs which he thought wocould be helpful. but what provided the solution was the sentence following the SQL examples: "In statements structured like the preceding examples, only standalone LOB columns are allowed in the select list ". I took that to mean that if you're going to access a BLOB on a remote database, then that BLOB column has to be the ONLY column you're referencing. so I broke our program's SQL up into this: insert into [local Word doc table] ([key 1 col], [key 2 col], [blob col]) values
('[Key 1]', [key 2], NULL) update [local Word doc table] set [blob col] =
(Select [blob col] from [Word doc table] @ [remote instance]
Where [keys = remote keys])
Where [keys = local keys] I was amazed to find that the above works like a charm. we 've got a 100 meg Word document going from one Oracle instance to the other with no problem. since doing a Google search on "cannot use LOB locators selected from remote tables" turns this page up near the top of its list of links, i'm hoping that by posting this I can save another programmer somewhere the two or three days of banging your head against the screen that I just went though.