If you're not allowed to create database links, then you can't access anything using a database link..
but probably I'm misreading your question...
I am able to create DB link and use that DB link on the APEX. The problem is with respect to BLOB column,
I am developing an apex application using the apex cloud (apex.oraclecorp.com). I am using the DB link to show the data on APEX UI. I have a blob data which is stored in the remote DB, and I want to display the BLOB data as text on a popup or a new page or a download link.
I tried using display only item but APEX throws an error saying that remote DB is not supported. Also, It would be great if I can just show a download link on the UI for the BLOB data, again when I try to select the format as BLOB in the column attributes, When I give the table@dblink as BLOB table I get an error saying that "table@DBLINK not found".
Is there any way to display the BLOB data as text or download it which is present in my local DB?
I don't think your question is clear enough. can you be more specific about what you are trying to achieve and also provide proper information about your apex & database version.
What you mean by displaying blob as text?? What is stored in your blob columns?
I am building an application for parsing java exception logs, I have to store java exception stack trace which I can not store as VARCHAR2 due to large chunk of data. So I am storing the data in BLOB.
Now I need to give an option on the APEX UI to view/download the stack trace which a BLOB column.
I am using APEX 4.2.2 and ORACLE 11G DB
I tried doing the same on the link 17.10 About BLOB Support in Forms and Reports before posting on the forum :
my table is present in a remote DB which I am accessing through a DB link, when I put table_name@DBlink in the BLOB TABLE NAME field on APEX it throws an error for "table@DBLINK not found".
Table Name Yes Case sensitive name of table containing target column of type
This is where I am stuck,
Try creating a view based on that db link and use that view instead of table@DBLINK
I tried creating a view on the table , it is throwing an error stating that
Error creating view:
ORA-22992: cannot use LOB locators selected from remote tables
Thanks for your help
Found lot of discussions on this error , https://forums.oracle.com/thread/471047
I tried creating a materialized view for the remote table but I dont have the privileges on the APEX cloud DB to create a tablespace.
create materialized view/snapshot ...
as select * from <table>@dblink
I am new to all this , is it possible to use an existing tablespace ?
Can you please suggest a way to do this ?
yeah try using DEFAULT
Alternatively you can workaround this by inserting a record into a temporary table from the remote db and then - download from the local table and delete it!!
insert into test1 (select * from BLOBS@TEST2_LINK)
Assuming your DB Link is working try this:
dbms_lob.substr(a."LOGBOOK_ENTRY",4000,1)||'...' AS blob_contents
. . .