Abdul, one of the readers asked how to access AR module tables/views in APEX?
APEX can be used to build applications on Oracle Applications (EBS) database. However you need do some preparation to access EBS database objects in APEX. It also depends upon installation/setup of APEX in your environment. Please note free APEX hosting site (apex.oracle.com) can not be used to connect to your servers.
The questions to ask before preparing APEX to access EBS objects are:
- Is APEX installed on the same database server as Oracle EBS?
- Is APEX installed on different database server say Reporting/Business Intelligence (BI) database which is clone of Oracle EBS database?
Suppose you want to build a report based of a view ar_customers_v in APEX, and database schema for APEX is XXAPEX. Steps involved to access this view in APEX are:
If APEX is installed on same database server as Oracle EBS:
1. Create a synonym on the view in XXAPEX schema.
CREATE SYNONYM xxapex.ar_customers_v for apps.ar_customers_v;
2. Grant privileges on ar_customers_v to XXAPEX.
GRANT SELECT ON apps.ar_customers_v to xxapex;
This is straight forward, you create a synonym for the view in XXAPEX schema and give select grants on the view to XXAPEX from APPS schema.
If APEX is installed on different database server than Oracle EBS:
For this scenario, assume EBS database server is 'DVL' and APEX database server is 'BIDVL'. Now steps involved in preparing a EBS view to be accessed in APEX are:
1. Create a database link in APEX database 'BIDVL' to EBS database 'DVL'
CREATE PUBLIC DATABASE LINK ebs_dblink
CONNECT TO XXAPEX
IDENTIFIED BY PWD
USING 'DVL';
Database link enables you to access database objects in remote database. The other database need not be Oracle database. Once database link is created, you can refer to a table or view on the other database by appending @dblink to the table or view name.
In the above command, database link is created with name 'ebs_dblink' which connects to XXAPEX user in 'DVL' remote database. USING clause has a connect string which is a service name of remote database. It must be defined in TNSNAMES.ORA.
The user running the above command must have privilege to create database link. Below SQL will help you check users having that privilege.
SELECT *
FROM dba_sys_privs
WHERE privilege = 'CREATE DATABASE LINK'
and admin_option = 'YES'
2. Create a synonym on the view in XXAPEX schema in APEX database i.e. BIDVL
CREATE SYNONYM xxapex.ar_customers_v for apps.ar_customers_v@ebs_dblink
3. Grant privileges on ar_customers_v to XXAPEX in EBS database DVL.
GRANT SELECT ON apps.ar_customers_v to xxapex;
The last two steps are similar to the steps if APEX and EBS reside in same database, except that synonym is created via database link. Below image shows how it works.
Once preparation is done for that database object, you can build report on that view using sql query say 'select * from ar_customers_v'. For APEX developers, it does not make any difference if the view is accessed via database link or is in same database. The same approach can be followed for other database objects like PL/SQL packages, APIs, etc.
Why does XXAPEX schema exists in EBS database server, if APEX installation is done in separate database?
Good question. This is where collaboration between APEX developers and DBAs in architecting how APEX works in your environment before building any applications, plays a crucial role. When database link is created you are connecting as user in remote database with a password. They should be valid user name and password for authenticating database link.
Say you created database link with APPS user. For security purposes, DBAs change passwords frequently. So when DBA changes APPS password in remote database, DBA has to ensure that all the database links pointing to that database are recreated with new password. It can be really annoying to troubleshoot such database link problem if there is no process in place.
To simplify the above problem, your DBA can create a new user say 'XXAPEX' in EBS database and never worry about changing password as it happens with APPS user. It is little convenience. It is totally upto DBAs on how to maintain sanity of database link.
Subscribe to this comment's feedwritten by Prafulla , June 02, 2009
You are doing a great job for APEX community. Please keep it up. I have few questions for you plese try to answer it.
First, let me explain you our existing system.
We don't have Oracle Applications system. We have Developer 2000 Legacy software(using forms/reports 6i) system on Oracle Database 9.2.0.8 & also have APEX 3.1 o as b2b system with same database i.e. Oracle 9.2.0.8.
Synopsis:
Two front end system i.e. (1) D2K Forms & Report system (2) APEX 3.1 B2B system
One Database i.e. Oracle 9.2.0.8
They have installed APEX 3.1 on Oracle 10 g Application Server & by using Oracle Net Manager they have created connection to Oracle 9.2.0.8 database.
Now we need to develop a new application in APEX 3.2 with Same Database i.e. Oracle 9.2.0.8. I think APEX 3.2 does not support Oracle Databse 9.2.0.8. Hence we have decided to install APEX 3.2 either on Oracle 10g or 11g Enterprise edition, because they are going to migrate Oracle Database 9.2.0.8 to Oracle 10g/11g. But for development we need to install APEX 3.2 and connect to Oracle Databse 9.0.2.8. Is it possible to install APEX 3.2 to Oracle 10g or 11g Application server & then connect to Database 9.2.0.8?? Also we need to migrate Oracle Forms/Reports 6i to APEX 3.2? Please help me how to migrate those reports!!
I will appreciate your help.
written by Prafulla , June 12, 2009
Thanks for your help. APEX forms migration notes really help me to get my work done.
Did you get the chance to find the notes to install APEX 3.2 on Oracle 10g/11g & connecting to Oracle 9i?
I will appreciate your help.
Thanks,
Prafulla
written by Vishal , September 23, 2009
We are archiving data from Apps to another DB(Target-ABC Schema) from where we access data via a DB Link.
We have a schema called Archive (which pulls data from the Target-ABC Schema) based on a View created on the target table using a db-link.
We need to show this information through the Self Service Forms. Unlike the Standard forms which work on Data Groups, self service forms do not
work on the concept of Data Groups, is there a way/method in which the DATA can be shown on the Self Service Forms.
--Vishal Subrahmanyam
written by Vishal , September 28, 2009
We can very much see the View in the schema we created, and the view also returns data when queried, but it the Form which OPENs up in a Web Browser (Self Service Form) which has NO data.
We need to see the Archived information from this screen which no longer exists in the APPs Schema.
Hope this is clear.
Regards,
Vishal Subrahmanyam
written by Vishal , October 01, 2009
We did not create any new OAF page or anything. We have a responsibility suffixed with " - Archive" for each resposibility under a module.
For instance a XYZ GL Super User will have a "XYZ GL Super User - Archive" which has the same set of Forms/Functions/Menus as that of the regular one. The only difference is that when we access the Archived Data from the ARCHIVE resp. it hits the VIEWs created using the dblink.
GL_JE_BATCHES would be ARCHIVE.GL_JE_BATCHES (a view) pointing to the GL_JE_BATCHES_H(the history table) in which the Archived Data is lying.
Hope this gives a good picture.
Regards,
Vishal.
written by Vishal , October 01, 2009
Added more technical details below...........
ARCHIVE.GL_JE_BATCHES (a view) pointing to the GL_JE_BATCHES_H(the history table)
create view ARCHIVE.GL_JE_BATCHES as select col1, col2, ......from GL_JE_BATCHES_H@SOURCE_TO_TARGET;
The GL_JE_BATCHES_H(the history table) lies in a diff DB, which holds the Archived Data.
Regards,
Vishal S
written by Peter Ridhardson , November 03, 2009
I have created te public database link with the owner's name, password and tnsnames.ora entry. It works fine when I SELECT in sqlplus.
When i try to use the same synonyms from within APEX the error occurs.
Can you help, please, cheers Peter
written by Prasad Dasari , May 08, 2010
Finally we are able to run the APPS Tables in SQL Workshop that is fine. But the problem here is i am not able to create report
by using apps tables, still it is not showing apps table in the list.Please help on this.
written by muzeeb , September 16, 2010
I would like to migrate Oracle apps 10g forms to Oracle apps 6i forms and If i convert 10g forms to 6i , if the forms will work or not .
or
I would like to migrate Oracle 10g forms to Oracle 6i forms and If i convert 10g forms to 6i , if the forms will work or not .
written by Steve Teale , September 29, 2010
Ignoring the rather obvious - why on earth would you want to go from 10g back to 6i (supportability issues etc) I would ask you how you plan on converting them ?
I'm not aware of a conversion tool for that direction. If you open a 10g form in 6i forms designer I think from memory you get a message in the tool with a message similar to 'this module has been saved with a different version of the tool' or words to that effect.
Have you had someone do development work for apps and they've used the 10g client instead of the 6i forms your apps version is currently on ?
written by Steve Teale , September 29, 2010
written by Steve Teale , September 29, 2010
mea culpa
written by amitsury , October 19, 2010
we have developed the apex application and publish the application on Apex schema installed in one database. Now i need to access some table from separate database. we do not want to access the table over DB link. Can we configure data source in apex repository schema to access the object on other database by passing userid/password@connection_string.
I mean smiler to other java base web application access the database by configuring the data source info in web server as userid/password@connection_string pattern.
Appreciate your time and feedback on this in advance
One suggestion I need here, we need to comeup for a Reconsiliation Process and we have to install application on 10g DB(non-ebs sys) which will be connect thorugh multiple DB sources. Is Apex is right solution for that and how we will connect other DB with APEX server, we want to avoid DB LINK.
*** Naren