Remove Duplicate Tables

Why this procedure?

Consider the following SQLs:

    Select  firstname,  lastname  from  employee 
    Select  firstname,  lastname  from  myschema.employee  

This basic example creates an ambiguity:

By default, the Visual Expert code parsers cannot know whether employee and myschema.employee refer to the same table. Unless we remove this ambiguity, Visual Expert will duplicate the table employee.

To avoid similar issues in your code, you need to specify a schema, database and server, that Visual Expert will apply by default when they are not explicitly indicated with a prefix before the Oracle object.

In the example above, if myschema is specified as default schema, then the Visual Expert code parser will automatically identify the table employee as myschema.employee, and the table employee will not be duplicated in the VE project.

Identify your schema, database and server name.

First, find the name of the database and schema containing the duplicated objects.
Then, use these value as default database and schema in your VE project.

a. Open a list of objects in Visual Expert and select some duplicated objects.

List of Oracle or SQL Server Duplicated Tables
b. Click “Locate” in the navigation bar : the database and schema containing the objects are displayed.
locate a table in oracle or sql server schema database and server
c. note down the server, database and schema names.

Specify schema/database/server name in VE settings.

  • Open your Visual Expert Expert Project
  • Open the tab “Project – Source Code Location” (screenshot)
  • Select the first item in the table – click on “Default DB Items” – enter the name of the default Server, Database and Schema.
    Default DB Items
  • Select the second item in the table (Oracle PL/SQL code) - click on “Default DB Items” – enter the name of the default Server, Database and Schema.

    Important note: it is key to specify these settings for both types of code (PB and Oracle). They rely on 2 separate code parsers, both parsers need to identify properly the DB Objects referenced in the code.

  • Click OK, close the settings window and launch the code analysis as prompted by VE.
    Refresh Analysis

Check the results

Once the analysis process is completed, check that the objects are no longer duplicated. 

List of Oracle or SQL Server tables without duplicated tables

Eventually, you will see both PowerBuilder and Oracle objects in the treeview:
List of PowerBuilder and Oracle PL/SQL objects found after code analysis