This is a detailed guide to set Default Schema, Database Link targets and Default Server for PLSQL Cross Applications References.
- Open your Oracle Project.
In the ribbon menu, click on "More Settings".
- Select a source.
Click on [Default DB items] to set default Database Items references for the selected source.
- Set Default Server name, Default Database name, Default schema name
- Click Advanced Settings to set Database link target item name (target schema or target database)
Case 1: To Specify a Database Link targeting a Schema
As shown in the above image:
- DB Link: “@EuropeDB_Sales” is a Database link
- DB Target: “Europe_Sales” is the Schema name targeted by Database link
Note: Here, since Database Link @EuropeDB_Sales refers to a Schema named Europe_Sales, Is Target Database is unchecked
Case 2: To Specify a Database Link targeting a Database
As shown in the above image:
- DBLink: “@USA_DB.Sales” is a Database link
- DB Target: USA_DB specifies the name of the Database targeted by the Database Link
- Is Target Database: Set TRUE to specify that Database Link targets a Database and not a Schema
Note: When a database link targeting a Database is used, please ensure that the Default Server of the calling application (pkg_demo_body.sql) and caller application (pkg_usa_sales.sql) should be same (e.g., DB link in this example)
Sample Project to demonstrate PLSQL Cross App References using Database links
PLSQL Source 1: Default
File 1: pkg_demo_body.sql
Note: (It is a calling package, it will reference database USA_DB using @USA_DB.Sales and Europe_Sales schema using @EuropeDB_Sales)
Server - <Default Server>
database - <Default Database>
Schema - <Default Schema>
(Here, @USA_DB.Sales Database link refers to USA_DB database, @EuropeDB_Sales Database link refers to Europe_Sales schema and <Default Server> is the schema name)
PLSQL Source 2: USA
File 1: pkg_usa_sales.sql
Note: (This is called from Default Source (pkg_demo_body.sql) using Database link @USA_DB.Sales)
Server - <Default Server>
database - USA_DB
Schema - USA_Sales
PLSQL Source 3: Europe
File 1: pkg_europe_sales.sql
Note: This is called from Default Source (pkg_demo_body.sql) using Database link @EuropeDB_Sales
Server - <Default Server>
database - Europe_DB
Schema - Europe_Sales
Application Dependencies Setup
Default has dependencies on USA and Europe.
Analyze Project with new settings applied.
Database links can be seen under All Database Links and their impact analysis.
Database Links references can be seen as shown below:
Sale_data procedure is referenced using Database link as shown below:
Pkg_usa_sales.uf_SetBracket function is referenced using Database link as shown below:
You can download the sample SQL code used to demonstrate the above settings and follow the trial easily.
More from Optimizing Code Analysis