Piotr Kononow
There are two mechanisms in Oracle database that let you comment (describe) your database schema elements. Those are:
- Code Comments and
- Comments in Data Dictionary
Let's have a closer look at those methods, what they give you and what are their limitations.
Code Comments
Oracle database, just like any other SQL database, lets you add comments to your SQL and PL/SQL code.
This applies only to those scripts that Oracle saves with an object rather than just creating a structure. Those are:
- Stored procedures
- Functions
- Packages
- Views
Data Dictionary Comments
Oracle enables ability to add comments to various elements of database schema. Most database tools enable editing and viewing those comments:
Table columns in Oracle SQL Developer
COMMENT Statement
Oracle SQL supports proprietary Comment statement. Every RDBMS has it's own syntax and different support. In Oracle this looks like this.
-- commenting table
COMMENT ON TABLE myschema.mytable is 'This is my comment for table';
-- commenting view
COMMENT ON TABLE myschema.myview is 'This is my comment for view';
-- commenting table/view column
COMMENT ON COLUMN myschema.mytable.col1 is 'This is column comment';
You can use it for following database elements:
- Tables and columns
- Views and columns
- Materialized views and columns
- Operator
- Indextype
So What Can You Comment?
Chart below shows which objects you can describe in Oracle database with those two options:
What Are Problems With Those Capabilities?
Well, there are a few problems with commenting database schema, and generally metadata, in Oracle:
- You can set structured metadata only for some of the objects
- There is just one metadata field - description
- Description field can have maximum 4000 characters
- It is not convenient to search and browse code comments
- Code comments don't allow generating a documentation - developers always need to analyze code to learn about packages, procedures and functions interfaces
- Oracle removes comments outside declaration what makes it more difficult to create standard header comments
- To document programs you need to modify existing code which is always risky
In short - it is more difficult than it should to discover, document and use Oracle databases.
How To Do It Better?
There is a better way, though. Try lightweight program Dataedo - you will be able to document your database structures and code, with rich text with images (!), use global search and generate beautiful documents (HTML and PDF) for your team.
You can easily import your existing data dictionary comments from Oracle to Dataedo repository and then build your documentation on top of that. You can also write it back to the database.
