This content has been marked as final. Show 3 replies
user601798 wrote:Create a view on top of the synonym.
When trying to develop an application (e.g. interactive report), APEX does not see tables from remote database even after public synoym is created in local OracleXE schema that current workspace points to?
I can with no issues -- via APEX SQL Workshop -- run SQL that accesses the remote database tables through the dblink.
Apex can work with the views. It does not work well with synonyms. But keep the synonym, since that makes your view indepentent of the name of the DB link.
So the typical recommened structure would be:
remote database table
-> access via db link
-> synonym on remote table
-> View on Synonym
-> Apex uses View (Wizards etc.)
If using a non oracle DB you might come across problem with certain concepts like ROWIDs. Obviously cyou can't build a form depending on a rowid, when working with a non oracle database. It should work with a normal primary/unique key however.
Edited by: Sven W. on Aug 15, 2012 12:46 PM
create or replace public synonym sy_my_table for other.remote_table@remote_db;
create or replace view my_table as select * from sy_my_table;
grant all on my_table to my_user;
Still can not see my_table in APEX wizards.
select * from my_table;
select * from sy_my_table;
both work fine from APEX SQL Workshop.
Please advise and thanks for your time.