On metalink, every one said there's no solution ....
Oracle can't do that ...
but you Tom, have 2 solutions and it works very well !

Thanks

Hey Tom... love the site. Thanks for all your help.

So I'm a bit confused on why one works and not the other.  Any insite would be appreciated. I'm 8.1.7.4 on windows.

Thanks.

SQL> desc doc_template
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_DOC                                    NOT NULL NUMBER
 BL_TEMPLATE                                        BLOB
 ID_CR                                     NOT NULL NUMBER
 TS_CR                                     NOT NULL DATE
 ID_UP                                     NOT NULL NUMBER
 TS_UP                                     NOT NULL DATE
 CD_CNTY                                   NOT NULL NUMBER
 TX_IMAGE_NM                                        VARCHAR2(25)
 TX_PAGE                                            VARCHAR2(100)
 TX_FILE_NAME                                       VARCHAR2(60)
 TX_FORM_NAME                                       VARCHAR2(20)
 TX_TEMPLATE_NAME                                   VARCHAR2(100)

SQL> insert /*+append */ into doc_template 
  2  select *   from ( select a.*, rownum rnum from (  
  3  select * from doc_template@gray order by rowid ) a  where rownum <= 15 ) 
  4  where rnum >= 5;
select * from doc_template@gray order by rowid ) a  where rownum <= 15 )
       *
ERROR at line 3:
ORA-22992: cannot use LOB locators selected from remote tables


SQL> 
SQL> 
SQL> insert /*+append */ into doc_template 
  2  select *  from  doc_template@gray  where rownum <= 15 ;

15 rows created.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL>  

The following statement encountered a error during parse:

insert /*+append */ into doc_template
select * from ( select a.*, rownum rnum from (
select * from doc_template@gray order by rowid ) a where rownum <= 15 )
where rnum >= 5
==============
Error encountered: ORA-22992
********************************************************************************

insert /*+append */ into doc_template
select * from doc_template@gray where rownum <= 15

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.71 1.39 0 11 1019 15
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.71 1.39 0 11 1019 15

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)

Rows Row Source Operation
------- ---------------------------------------------------
16 COUNT STOPKEY
15 REMOTE


Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
16 COUNT (STOPKEY)
15 REMOTE [GRAY]
SELECT "ID_DOC","BL_TEMPLATE","ID_CR","TS_CR","ID_UP","TS_UP",
"CD_CNTY","TX_IMAGE_NM","TX_PAGE","TX_FILE_NAME",
"TX_FORM_NAME","TX_TEMPLATE_NAME" FROM "DOC_TEMPLATE"
"DOC_TEMPLATE"

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

but the result is not BLOB,
and,if I want to convert the result to LONG type,
what shall we do?
thanks!

My mean is:
what return-type is it when using 'dbms_lob.substr'?
BLOB or CLOB ?
Thanks your !

varchar2 if the input was a clob
raw if the input was a blob


describe would have told you this
documentation would have too

SQL> desc dbms_lob

....
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
 

Oh,yes! Thanks very much! But,
the type(long raw) will be washed out in future,why ORACLE still use it? "dbms_lob.substr", Do I must use it ? If so, DO I must use to_lob function to convert the long raw type to the blob type ? Can I convert the long raw type to long type directely ?
Thanks!

How do you do,
I want to access a remote table(included a long field) using my program, but now, I find the table's field have been changed to blob type already, if I dont't modify my program, what can I do?
Thanks!

Hi Tom,

I have the following situation:

I have 2 databases db1 and db2. db1 has a table 'a' with structure

create table a
(
id number,
img blob
);

db2 has a table 'b' with structure

create table b
(
id number,
img blob
) but img is always null in 'b'. Now I want to create a view v1 which should have the id from table 'b' and image from table 'a'. If I do

create view v1 as
select b.id, a.image@lnkdb1 img
from b, a
where a.id = b.id

I am getting ORA-22992: cannot use LOB locators selected from remote tables. How can I accomplish this? I read a suggestion earlier in this article and I tried to create a view on db1 with dbms_lob.substr(img) and then when I created the view the img column is coming as RAW but I want blob. Please help.

Thanks.

Is there any other option I can try?

Thank you tom!

Tom, I am try to use option #1 above by creating the view on the remote system and accessing that view using the DB link.
I have created the view but when I try to access it within a SELECT statement, I get a “ORA-00942: table or view does not exist” error. The view can be accessed on the remote sever so I know it does exist and is working, I just cannot see it from the local server using the db link.
Any suggestions? Thanks

We have the same problem with selecting blob data across a db link. Someone here came up with a function that looks like it’s working.

Pusdo code:

function getblob() return blob is
b blob;
sqlStr varchar2(1000);
begin

sqlStr := ‘select theblob from thetable where id=123’;l
execute immediate sqlstr into b;
return b;

end;

then:
select getblob() from dual;

This is in relation to the original question...I guess even today the situation is the same - we can't select lob over db links. But one other thing I noticed is that -one can insert or update the lob over db link!
There is a thread on OTN that shows a "workaround" for it:
http://forums.oracle.com/forums/thread.jspa?threadID=317043&start=0&tstart=0

Thanks,

Hello Tom,

I have a requirement to get a clob from a remote database(10g) to a local one(11g).
The clob has to undergo a conversion by a function from a cartridge installed on remote database (this is why i cant copy the clob first and process it locally).
I am also very limited to what i can do on the remote database.

I tried to go with a view(that performs the conversion) as suggested, however i receive:
"ORA-64202: remote temporary or abstract LOB locator is encountered", when i try to
insert as select from that_view@remote_site into a local table.


Forums suggest, that it would work if the local database was also 10g, but in 11g it does not work. Is this true? Is there some workaround?

Tom,

Could you please explain why can we insert clob over db link into a table but not select it?

LOAD CLOB over database link
log_data - CLOB

merge into LOG_TEST d
using LOG_TEST@SP1GBT s
on (d.id = s.id)
when not matched then insert (
d.id, d.log_date, d.module, d.message, d.log_data)
values (
s.id, s.log_date, s.module, s.message, s.log_data)

Hi Tom ,

I have a similar situation where I am supposed to archive the data data to another schema in another system. I used DBLINKS. for my SIT purpose I used Normal Instances and a dblink between the same. My archival data worked without any issue. My workaround was using materialized views.

However when I moved the code the two RAC instances for purposes of UAT I was back to the same issue of cannot use LOB locators.

The databases were on Solaris Oracle 11.2.0.3.

Just to let you know.