How to Create a Visual Expert Project with Oracle PL/SQL Code
Visual Expert can analyze Oracle PL/SQL code using two different methods:
- From an Oracle database – by connecting directly to your database to extract and analyze the code
- From files or folders – when your PL/SQL source code is stored locally
In both cases, you may need to remove ambiguities after the analysis to avoid duplicate tables or procedures when schema names are not explicitly specified in the code.
Option A – Analyze PL/SQL Code from an Oracle Database
Use this method to connect directly to your Oracle database and analyze the PL/SQL code stored there.
Prerequisites
Before connecting to an Oracle database, you need to:
- Install an Oracle Client on your system
- Ensure the database user has the required permissions
- Configure your connection settings
Visual Expert supports several connection types:
- For Basic, Advanced, ODBC, and Data Source Name (DSN), refer to Connection to Oracle DB using Different Connection Types
- For Oracle Cloud, refer to Configuring Oracle Cloud Database Connection
Step 1 – Create the Project
- Click 'New' on the ribbon menu and select 'Oracle PL/SQL' in the next pop-up.
Step 2 – Connect to the Oracle Database
- Select 'Oracle Database' as the source type.
- Choose your connection type (Basic, Advanced, ODBC, DSN, or Oracle Cloud) and enter the required credentials.
- Click 'Test Connection' to verify the connection, then click 'Next'.
Step 3 – Select the Objects to Analyze
When connecting to a database, Visual Expert can access all objects available through this connection, including schemas, tables, procedures, and system objects. It is recommended to filter the objects you wish to analyze:
Click on "More Filters" to access additional filtering options:
Step 4 – Configure and Launch the Analysis
- Once your Oracle object selection is completed, click 'Next', then select "Open project setup".
- Name your Visual Expert project.
- Set the scheduler to refresh your code analysis automatically on a regular basis.
- Select "Launch the analysis".
- After analysis, you will see Oracle and PL/SQL objects in the treeview.
- You will also see the sources of code in your VE project.
Go to [Code Analysis - Select Source Code].
Once the analysis is complete, proceed to Remove Ambiguities to ensure accurate results.
Option B – Analyze PL/SQL Code from Files or Folders
Use this method when your PL/SQL source code is stored in local files or folders.
Step 1 – Create the Project
- Click 'New' on the ribbon menu and select 'Oracle PL/SQL' in the next pop-up.
Step 2 – Select Files or Folders
- Select 'Folders and/or Files' as the source type.
- Click on "Add files" to specify the location of your Oracle PL/SQL code.
Then click on "Finish".
Step 3 – Configure and Launch the Analysis
- Select "Open project setup".
- Name your Visual Expert project.
- Set the scheduler to refresh your code analysis automatically on a regular basis.
- Select "Launch the analysis".
- After analysis, you will see Oracle and PL/SQL objects in the treeview.
Once the analysis is complete, proceed to Remove Ambiguities to ensure accurate results.
Remove Ambiguities: Duplicate Tables and Procedures
Why This Step?
Select firstname, lastname from employee Select firstname, lastname from myschema.employee
This basic example may create an ambiguity:
By default, the Visual Expert code parsers cannot determine whether employee and myschema.employee refer to the same table. Unless this ambiguity is resolved, Visual Expert will duplicate the table employee.
Please read this article to remove possible ambiguities in your project.