Reverse Engineering Tools For Pl Sql Update
Reverse Engineering a Data Model using the Oracle Data Dictionary Reverse Engineering a Data Model Using the Oracle Data Dictionary by This article shows you how to use the Oracle Data Dictionary to obtain:. Table definitions. Constraints. Indexes. Views. Sequences.
Reverse Engineering Tools For Pl Sql Update Statement
Triggers. PL/SQL functions and procedures The Problem Let's say you have to work with an Oracle database, using a data model that somebody else wrote.
Maybe you're extending the data model or building an application that references it. There's only one problem: whomever created the original data model left without writing a line of documentation.
Pl Sql Update Example
What do you do? How to you reverse engineer the data model to unearth the table definitions, constraints, indexes, views, sequences, triggers, and PL/SQL functions and procedures? This ends up being an easy task if you use the Oracle data dictionary. The Oracle Data Dictionary Just like you use Oracle tables to store your data, Oracle uses tables to store its data. A set of tables, called the Oracle data dictionary, contains information about all the structures (tables, views, etc.) and procedural code (triggers, PL/SQL procedures, etc.) created by each user.
For example, there's a table called USERTABCOLUMNS that contains information about all the columns you've defined, including: what table the column belongs to, the data type (number, varchar, etc.), what the default value is, whether the column can be null, etc. The Oracle data dictionary is huge and contains a lot of esoteric stuff, but when you whittle it down to only the info you need, it's not so menacing. Here are the data dictionary tables I find useful. You can do SELECTs on them, just as you would any other table in Oracle: USERTABLES Lists each table that belongs to your Oracle user. USERTABCOMMENTS Shows comments on the tables and views. USERTABCOLUMNS Tells you the names, data types, default values, etc. Of each column in each table.
USERCOLCOMMENTS Shows comments on the columns. USERCONSTRAINTS Gives you all constraints (either single- or multi-column), such as primary key, foreign key, not null, check constraints, etc. USERCONSCOLUMNS Maps constraints to columns (since a constraint can act on one or many columns). USERINDEXES Lists indexes defined on columns (either defined explicitly when creating the data model or defined automatically by Oracle, as is the case with indexes on primary keys). USERINDCOLUMNS Maps indexes to columns. USERVIEWS Lists all views, along with the text used to originally create them. USERSYNONYMS Lists the synonyms and original table names.
USERSEQUENCES Lists all sequences, including min value, max value, and amount by which to increment. USERTRIGGERS Contains trigger names, criteria for activating each trigger, and the code that is run. USERSOURCE Contains the source code for all PL/SQL objects, including functions, procedures, packages, and package bodies.
Query Builder, a tool built into Toad, now supports the reverse engineering of queries which is limited. Quest offers another. Delete queries, one at a time. To reverse engineer a query 1. Enter a query into the Editor Or Open a file containing a query in the Editor. Right-click in the query and. On the other hand, manual analysis of legacy code without assistance of original developers is time consuming and error-prone. For the purpose of reengineering PL/SQL legacy code in the steel making domain, we developed tool support for the reverse engineering of PL/SQL code into a more abstract and comprehensive representation.
All of the above tables (the USER. tables) only contain objects defined by the current Oracle user. Oracle also maintains a set of tables of identical structure that start with ALL. These show you every object that you have access to, regardless of whether you created that object (e.g., our beloved friend DUAL). Similarly, Oracle provides DBA. tables that contain info about all users' objects, but this group of tables is only accessible to the database administrator.
Reverse Engineering the Data Model In the following sections, I'll show you the queries you need to do to find the following:. Note: the following queries have been tested with Oracle 8i and Oracle 9i.
I have not tried them out on other versions. The queries:. Find out what tables have been defined in your system: select TABLENAME from USERTABLES TABLENAME is really the only important info we can get from Oracle's data dictionary table USERTABLES. When tables are created, most of the action takes place in the definition of individual columns, which we'll look at later. For example, if you have four tables defined in your system, your query will return four rows: TABLENAME EMPLOYEES OFFICES SOFTBALLTEAMS EMPLOYEESAUDIT.
For each table, get any comments written by the data model author: select COMMENTS from USERTABCOMMENTS where TABLENAME = ' TABLENAME' and COMMENTS is not null Note that the TABLENAME must be written in all uppercase letters. Example: if we do this query for the EMPLOYEES table, we find the following comment: COMMENTS This is a table to hold all current, past, and future employees. Application developers might find the views EMPLOYEESCURRENT, EMPLOYEESPAST and EMPLOYEESFUTURE useful.
In my experience, very few developers document their tables within Oracle (if the tables are documented, the documentation is generally done in some file somewhere else). But if you want to be a conscientious developer and ensure that your comments show up in the data dictionary for future programmers to find, you can use the command: comment on table TABLENAME is ' This is my comment.'
. If you only want basic info about each column (name, type, and whether it's nullable), the easiest way to get it is to DESCRIBE the table (or DESC, for short). Let's see what columns the EMPLOYEES table contains: SQL desc employees; Name Null? Type - - - EMPLOYEEID NOT NULL NUMBER(38) LASTNAME NOT NULL VARCHAR2(200) FIRSTNAME NOT NULL VARCHAR2(200) EMAIL VARCHAR2(100) PRIMARYOFFICEID NOT NULL NUMBER(38) STARTDATE NOT NULL DATE ENDDATE DATE SALARY NUMBER(9,2) YEARSEXPERIENCE NUMBER MANAGEMENTTRACKP CHAR(1) SHORTBIO VARCHAR2(4000) LIFESTORY CLOB PHOTO BLOB But if you want more detailed - and parseable - information about your tables, you will have to query from the data dictionary.
Books.google.com.tr - The SE 2004 of the ACM/IEEE computing curriculum project recommends software design and architecture as one of its ten essential areas of study. Software Architecture and Design Illuminated is the ideal text for undergraduate and graduate students delving into this critical area of the software development. Software Architecture and Design Illuminated.