I want to insert,update and select records in a table that is in another database. I created a link to that table. I didn't have much luck
accessing it via apex. so then I created a view in this database that just selects everything from the table via the link. This is accessible
via the object browser. The schema owner and the apex developer both can select and update records from that view.
But if I try to use it in application builder:
(I choose an existing application and try to add a second page to it)
form on table or view
table/view owner is correct
(create form) I search for table/view name and it is listed as a view
but it returns:
ORA-20000: Schema MYSCHEMA has no select privilege on table or view MYVIEW.
Has anyone seen this and have an idea? Thanks so much.
Did you grant the rights on MYVIEW directly to the parsing schema user?
----- Blog: http://www.oracle-and-apex.com ApexLib: http://apexlib.oracleapex.info BuilderPlugin: http://builderplugin.oracleapex.info Work: http://www.click-click.at
The parsing schema owns the view. However just to check this out I logged in as system and granted select,insert,update,delete
on that table to the schema. That didn't fix it.
I wonder if this "parsing" mean that the database on the other side of the link has to be
10.2.0.3 or 11? It is not it is 10.1.0.5.
The database apex is in is 11gr2.
Also I tried creating a table from select * from the view, so it is a table in 11gr2. In that case there is no problem in application builder.
I guess it would work to have the "real" table be in 11gr2 and update the "shadow" table in 10gr1 if there's an easy way to do it
like have a materialized view update every night? Do links from 10gr1 to 11gr2 work for this?
Edited by: lake on Nov 8, 2010 9:34 AM
Never mind. It does work if you create the link exactly the way apex wants it.
This is easily done if you create the link in apex as described here:
By default, the CREATE DATABASE LINK system privilege is not granted to a provisioned workspace or database user. To use this feature, a DBA or administrator needs to grant this specific privilege to the database user in the user's workspace. See "Creating Database Links" in Oracle Database Administrator's Guide
To create a database link:
On the Workspace home page, click SQL Workshop and then Object Browser.
Object Browser appears.
Select Database Link and click Next.
Follow the on-screen instructions.
Note that Database Link names must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.
Then create a view on it and use that in application builder.