I have 2 Oracle database 12.2 Version۔ One database has schema and the other database has Oracle Apex 18.2. I want to show blob column in apex through view because apex uses parsing schema option. An error occurs when I create a view via dblink in the apex database.
create or replace view v_temp
as
select * from table@dblink;
ORA-22992: cannot use LOB locators selected from remote tables
I found a function that creates a force view via dblink:
create or replace function BLOB_DBLINK
( dblnk in varchar2
,tbl in varchar2
,col in varchar2
,rwid in urowid)
return blob
is
retval blob;
tmpraw raw(2000);
tmplen number;
tmpchk number;
chksize number;
begin
--preset vars
chksize:=2000;
dbms_lob.createtemporary (retval,true);
execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmplen using rwid;-- precalc
tmpchk:=floor(tmplen/chksize);-- applicate frist chunks
for i in 0 .. tmpchk-1
loop
execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid;
dbms_lob.append(retval,tmpraw);
end loop;-- applicate last entry
if (tmplen-(tmpchk*chksize)) > 0 then
execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||(tmplen-(tmpchk*chksize))||','||((tmpchk*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid;
dbms_lob.append(retval,tmpraw);
end if;
return retval;
end;
But this function is very slow. Is there any other way to create view over the dblink?
Vinish Kapoor