Oracle database objects downloading

ODA installed. We see a blank window.
Now is the time for DB objects downloading.
You must press the "Administration" button.

The "Administration" window appears

You press the "Insert Objects" button.

Next window allows you to call few adapters.

The first adapter allows to connect to your analyzed database and to download its objects.

The "Adapter" field value is your logical name for this database connect.

You can populate all items and press "Create properties file" button for parameters file creating and you can use this file on batch downloading.

For the batch downloading you run command:

java -Xms512m -Xmx1256m -jar oda.jar -batch <Parameters file created earlier> admin

Else you can press the "Ok" button for online downloading.

ODA Oracle object dependencies analyzer. Part 1

I want to familiar you with the ODA. It's a tool for impact analysis of program code on the one side and the database objects on the other side.
You can download the ODA from our ODA site.
The ODA is Java Swing application and requires Java Runtime Environment (JRE 1.6 Version and higher).
Further, you create an Oracle user, which will be created the ODA repository.
The user repository owner must have "CREATE TABLE" and "CREATE SEQUENCE" and unlimited quota size permissions.
Here's the sample of a script:
  -- Create the user
  create user ODA_OWNER
  default tablespace USER
  temporary tablespace TEMP
  profile DEFAULT;
  -- Grant/Revoke role privileges
  grant connect to ODA_OWNER;
  grant resource to ODA_OWNER;
  grant select_catalog_role to ODA_OWNER;
  -- Grant/Revoke system privileges
  grant select any table to ODA_OWNER;

  grant unlimited tablespace to ODA_OWNER;
For the ODA starting you run command:

java -Xms512m -Xmx1256m -jar oda.jar admin

In Login prompt you connect to Oracle user - owner of ODA repository(8 tables and 1 sequence).
At first time the ODA repository created automatically in Login ODA user schema. In future you can drop or create new repository in Login window too.
ODA user must see DBA views. 

You can see the installation details there.

Dependency is always a relationship between objects and a few objects:

• The program unit use tables, views, others program units
• The form trigger use DB objects
• The view look to few tables or views
• The view used in few program units

For relationships search I can use Oracle dictionary views if this relation there is between DB objects or simple string search in the program units(PL/SQL) or just text(Word,scripts).
Well, that in both these cases, there is something that can help us.
In first case there are DB views(PUBLIC_DEPENDENCIES).
In second case the search more difficult, but we have all database object's list downloaded into the ODA repository and if text search found database object name in the text we know what's type of this object and we can understand what's kind of dependency we found.

So, our first step - to download  metadata of database objects.

Problems that must be solved in order to build dependencies analyzer

I saw the problems which I wrote in previous post and I decided to develop a tool that will answer to this questions (and with that to study the Java language too ). 

So what do I have?

• Some Oracle database instances
• Some legacy applications use this instances (Forms application, Informatica, Word files...)

It's clear, for one overall answer I must have one common repository and some adapters that work with different types of applications.

How the dependencies will be shown ?
I like a tree visual object. The tree will give me the opportunity to show the dependency in two directions: 

• object that used in some codes
• code which some objects uses

So I need two tabs for two directions.

Every time I chose a tree branch, depending on the direction, I want to see the context of use of object, just a fragment of code where's the object's name. I need a window in which I can show track / snippets of code. Also I want to see whole PL/SQL procedure code or text fragment. I want to see the object reference allocated by color.

The separate task - how to show code. There are different tasks to show PL/SQL package, PL/SQL procedure, Oracle form or INFORMATICA workflow.

The tree gives me the opportunity to watch dependence with moving from level to level. But I need to see all dependencies of the object as a grid, where I can see all referenced objects with its details and the usage type.

The object analyzer works also with some database instances so it's necessary to work with DB links and synonyms.

I need a report about the found dependencies for an offline work.

Why we search database object dependencies

In large companies you often observe many multi-database and multi-platform legacy systems. This setup makes it almost impossible to discover information regarding the dependencies between database objects, programs, forms and reports. Applications can include several hundred stored procedures, Oracle forms and reports using thousands of objects inside the Oracle database (tables, views, packages, synonyms and DB links).

Lack of technical documentation, or partial documentation, incompatibility between application's documentation and implementation complexity are just some of the reasons why it is almost impossible to determine the various dependencies to any given object. Legacy systems have lost a common structure with a common logic and it has become hard to analyze and identify the object's dependencies and relationships.

We can see something similar on the internet. At most you can see the logic of a website, but the structure and relationships on the internet itself are impossible to know without a web crawler.

I see some reasons for to search object's dependencies:

1. I wants to change object definition and I want to know the objects that will be invalid after the change (DB objects).
2. What's external object use DB objects. I know the some external objects work with database:
            -  Oracle* Form/Reports
            -  xNIX and sql scripts
            -  Word files (Global/Detail design documents)
            -  Flat files (Java, C, XML)
            -  Informatica workflows work against DB

3. Reverse engineering of legacy systems

Only if we search dependencies in DB we have Oracle support as dependencies information in dictionary views 

• ALL_DEPENDENCIES
• DBA_DEPENDENCIES
• USER_DEPENDENCIES
• PUBLIC_DEPENDENCIES

For  the answer to the second question we don't have any information. We use a kind of search methods for a string search if it's possible.


For the third reason it doesn't enough to know what's dependency so how did the object use. We want to know what's context of the object usage, what's a type of table usage: select/insert/update/delete.

For a big company it's desirable to receive one overall answer for all its applications.