Javadoc is a tool for generating API documentation in HTML format from doc comments in source code. Wouldn’t it be nice if a similar tool existed for PLSQL? Well you’re in luck because someone already made that available via an open-source utility known as pldoc.
Automatically document your database schema with SQL Developer using the DB Doc feature. Generate HTML pages and allow your teams to click through the PLSQL source and tables without access to development databases!
SQL Developer has extended pldoc into ‘DB Doc.’ DB Doc includes the PLSQL documentation functionality and adds the ability to also document the following objects:
- Tables
- Views
- Editioning Views
- Indexes
- Queues
- Sequences
- Materialized Views
- Synonyms
- Database Links
- Directories
- Editions
- Application Express
- Stored Java Procedures
- XML Schemas
- XML DB Repository
No additional work is required to take advantage of the regular database object documentation. However, if you want to harness the full power provided by pldoc, you need to start building additional documentation into your PLSQL objects via these commenting schemes seen in this sample code.
Here’s a quick preview of how that looks when you add it to your PLSQL
CREATE OR REPLACE PACKAGE CUSTOMER_DATA IS /** * Project: Test Project (<a href="http://pldoc.sourceforge.net">PLDoc</a>)<br/> * Description: Customer Data Management<br/> * DB impact: YES<br/> * Commit inside: NO<br/> * Rollback inside: NO<br/> * @headcom */ /** * Record of customer data. * * @param id customer ID * @param name customer name * @param regno registration number or SSN * @param language preferred language */ TYPE customer_type IS RECORD ( id VARCHAR2(20), name VARCHAR2(100), regno VARCHAR2(50), language VARCHAR2(10) ); /** Table of customer records. */ TYPE customer_table IS TABLE OF customer_type INDEX BY BINARY_INTEGER; /** * Gets customer by ID. * * @param p_id customer ID * @param r record of customer data * @throws no_data_found if no such customer exists */ PROCEDURE get_customer ( p_id VARCHAR2, customer_rec OUT customer_type); /** * Searches customer by criteria. * * @param p_criteria record with assigned search criteria * @param r_records table of found customers <b>(may be empty!)</b> */ PROCEDURE get_by_criteria ( p_criteria customer_type, r_records OUT customer_table); /** * Creates a customer record. * * @param customer_rec record of customer data */ PROCEDURE create_customer ( customer_rec customer_type); /** * Changes customer data. * * @param customer_rec record of updated customer data */ PROCEDURE update_customer ( customer_rec customer_type); END;
When you build the DB Doc set with SQL Developer, the PLSQL pages will be built using the information inside these /* */ blocks
Running DB Doc in SQL Developer
You will need to define a connection for the schema you want to document. You can then mouse-right-click on the connection in the connection tree and select ‘Generate DB Doc…’
You only have a few options. Decide which types of objects you want included. Decide if you want blank pages if there are no objects for that object type. And provide a directory to write the files to. Please note there’s a bug in the current version that prevents SQL Developer from running in an acceptable amount of time if a large amount of FLASHBACK data attached to a table. This can be avoided by either
- Running as a low privileged user – CONNECT is all that is required to documents a schema
- Removing tables from the list of objects to be documented
This bug has already been fixed and addressed for the next release.
Instead of putting in a bunch of screenshots, here’s the full HTML report to play with
Get started with a very small DB Doc HTML documentation set generated by Oracle SQL Developer v3.1.
It has a bit further to go before it’s perfect. A few bugs yes, but I’d also like to see support for generating an overview page like what you see in pldoc. I was thinking maybe a dynamic page that lists the date, connection name, and version of SQL Developer used to generate the pages. Then the end users can mod it up or replace it with whatever serves their purposes. And then maybe make this available via the command-line so you can build it into your build process.
And so we continue to ‘the next version!’
50 Comments
Has export to CSV or Excel already been requested?
Massimo
No, and this feature hasn’t seen much changes, or requests from customers, in a long time.
Ten years later and I’m still trying to figure out what the @headcom tag is used for.
Hi Jeff, I just tried it with SQLDev 21.2.1. But I was unable to produce the doc tab for packages as you showed here: https://www.thatjeffsmith.com/dbdoc/index.html . Any idea why? Thx.
Unable as in complete or partial failure?
>Unable as in complete or partial failure?
Regarding the “doc tab”. It’s completely missing. Looks like pldoc is not supported anymore in the recent SQLDev version.
Is there any news on an ability to generate DB Doc externally, for example from a CI solution?
No, sorry.
Jeff,
What is the difference between the Worksheet and the Editor?
I am using the File Browser to edit some files and they are being opened in the Worksheet, not on the editor.
Why is that?
Thanks
Lots of differences.
But to answer your real questions, it comes down to file extensions. This is controlled in the preferences.
Can you elaborate?
I see the File Type preference, but do not see anything there related to worksheet/editor.
you can see some file extensions are set to ‘sql script’ and others are set to ‘plsql’ – that’s what determines if a file opens in a plsql editor or in a SQL Worksheet
has this issue been seen?
ERROR! String index out of range: -1
this is trying to show the details on a package
not by ME, and i’m not aware of it as a current issue. best to check with support and your reproducible test case though to be sure
Hi Robert,
I got the same error, and found it was due to using
@throws
Take this out and it works ok.
btw thatjeffsmith can i please upvote the request for full support of comments in PACKAGE BODY (as in PACKAGE), and also request some capacity to choose which OBJECTS will be documented, instead of everything.
Thanks.
Peter.
Hi Jeff, Is there any tool which can document and visualize PL/SQL stored procedure. We are working on undocumented DB for which the structures and huge source code is a mess. I will be grateful for any recommendations. Here is a link to some research paper in this area: https://drive.google.com/open?id=0B9YNlC6uX5lZRXRwU1FWcnhoTHM Regards
The generate Db Doc option is generating empty html file. No tables are populated for e.g. when I click on tables link on left top.
CAn you please help? I have the connection tested and it shows all tables in SQL developer when I open the connection tree.
we fire off a TON of data dictionary queries, sounds like you don’t have privs for one or more of them
you can see what we’re doing when you ask for the documentation set if you open View > Log, and click on ‘Statements’
make sure you have privs on all those views
Hi Jeff!
I have been using SQL Developer option “Generate DB Doc” which is an awesome feature.
I got a one question.
Is it possible to Generate DB doc that command line so it can be automated?
Thanks
No, but it’s a standing and frequent enhancement request, so I hope it will be done sooner vs later. Stay tuned.
Hello there,
some years ago I have created an open source tool called OracDoc.
It creates JavaDoc like documentation with linking of all relevant objects.
Please feel free to try it out. You can find it on sourceforge or here is a direct link:
http://sourceforge.net/projects/oradoc/
Kind regards,
Igor
Hi Jeff,
Is it possible to select objects for DB documentation, I need to have some packages included in my PL/DOC output, how would this be done?
No, it’s all or nothing. The only way to selectively do it would be to selectively copy the objects you want DOC’d to a new schema.
Hi –
What is the cost of having such filter in the DB Doc, against having to create a new schema, assuming that you are on a VPS with only one schema. I think that SQL Developer have the right to have this feature.
Thanks
Omar
The cost is a few hours work probably. I just have to prioritize all of the few hours requests from all 4 million+ of our users. Go vote for your feature request here, search for ‘db doc’.
Yes,
The generate DB Doc functionality respects the filters applied to the connection tree (at least as of SQL Dev 4.1.2.20). As such you can filter the various nodes to just those objects you want documented. So there’s no need to create a custom documentation schema, but creating a separate documentation connection to your DB may be helpful.
Great! Thanks so much, Jeff!
Bosco
Hi Jeff,
I’ve automated integration of DB Docs with a Wiki knowledge base to capture and share information from SMEs across different business, and technical areas. Is there a command-line option that I can use to generate DB Docs? This would allow me to automate the generation of DB Docs, detect new objects in the DB, and create corresponding pages (based on template) in Wiki to allow SMEs to update as needed.
Thanks for maintaining a great blog on SQL Developer.
Regards,
Bosco
It’s on the list. I’ll add your name to it 🙂
Jeff,
I believe there is high potential with this. I am a Oracle DBA. Here are some suggestions that I feel would improve the DB Doc Generator.
1) Add the ability to exclude certain Tables, Packages, Procedure, Functions and Triggers. My Reason: If the DB Doc is to be used on a Sharepoint Site for Business Intelligence or other departments there will be objects that do not need to be in the documentation. It will be clutter and wasteful to show stage or temp tables.
2) Add the ability to change the Images that are used in the HTML of the output. My Reason: Same as reason 1 concerning an Intranet / Sharepoint site where coporate policy dictates the branding that can be on a page.
3) Question: What is the .jar name & path that contains the headerBg.jpg file?
I am a moderate JAVA coder using JBuilder, Eclipse & Netbeans. Therefore, I believe I could develope these functions as an AddIn. However, it is my opinion that it would be better to have these functions packaged internally with a future SQL Developer release.
Thanks for all that you do Jeff!
The DBDOC does a great job of documenting the stored PL/SQL code. I have noticed a couple things that could be useful.
1) Could a table comment be presented more noticably when you are looking at the table in the HTML? Perhaps right under the table name above the details.
2) Any thoughts of allowing for documentation tags added as comments in a view creation script, for example, would provide some of the same capabilities as the PL/SQL code?
Anything’s possible. We’re basically following the spec for javadoc but we could add custom bits for tables, views, etc. I would add it to the Exchange as an ER.
Hi Jeff,
I am an active user of this “Generate DBDoc” feature and I have an issue with my package documentation. I have properly documented all objects in the package spec including functions, procedures and types, but I haven’t see any types description in an output HTML, only functions and procedures. Is this a known bug or I am doing something wrong?
Thank you!
Smells like a bug to me, but I’ll have to reproduce locally to confirm. We’ve done a lot of work for this feature in the next release so it might already be ‘fixed.’ Will let you know!
Thanks Jeff,
Sounds good! Could you, please, tell me an estimated date of the next release? If it is not a “top secret” ranked information of course 🙂
It’s pretty top secret 🙂 As a rule we don’t talk about release dates.
So my own example of this feature clearly demonstrates this bug – boo on me for not catching it. Talking with the developer now to see what the story is. Thanks for bringing it to my attention Anton.
Jeff,
I am interested to get the comfortably working tool, so I’m glad to help you to make it better 🙂 Hope that the bugfix will be included in the next release. Thank you for a really fast reaction – your work is your passion for sure )
Hi Jeff,
I’m glad to see that the DB Doc tool has been improving. I was really glad to see that if you have custom editor tabs defined, they get picked up by the DB Doc tool and documented (I’ve got both a References and a Referenced By tab for viewing the referential integrity constraints).
However, I noticed that any tab that has detail information opens a new browser window to the detail when you click on the row number. A nicer approach would be to include a details pane below the parent data. None of the tabs I looked at had more than one detail section but there’s no reason they couldn’t, so the child section should also contain a tab set for each of the child detail sections. I’ve added a [request|https://apex.oracle.com/pls/apex/f?p=43135:7:::::P7_ID:34681] on the exchange.
I do have one question at this time though,
What attribute tags does the DB Doc feature currently understand? Your example showed an @headcom attribute, but that appeared to be passed through without being touched wheras the @param and @returns tags were processed just fine.
I typically use the @version and @author tags in my comments and if multiple developers work on a piece of code the @author tag may appear more than once.
Hello Jeff,
Thanks for your reply. I’ve submitted a request for both both points. 🙂
And yes, the idea would be to put comments in both the specification and body, as the goal and needs might be different. The spec doc would be more like a user guide for client developers, imagine a Java or .NET developer who wishes to call a package procedure and needs a brief explanation about what the procedure does, without having to know much about the inner workings. Whereas the body doc would be aimed at PL/SQL developers who need to make changes to the logic contained in the body or need to understand the inner workings as part of a bigger whole.
I’ve to agree with Raoul. I would like to use this feature to generate technical documentation of our code, not a user guide for end users. As such, it would be very useful to be able to document the whole package, in other words the body, and not just the specification. Note that PLDoc does allow this. Considering you can choose in the selection, which object types to document, I see no reason no to implement this. If you don’t want this and prefer keeping your package bodies undocumented, simply deselect that option.
Another feature I miss is the selection on object level. Sometimes, I just want to generate documentation for one specific package or object, not necessarily the whole schema. As such, it would be very useful to have the Generate DB Doc option at object level as well.
These are my two main issues with the feature as it exists now. Especially the first issue keeps me from making it our standard PL/SQL documentation generator.
So you need to document your private package members? Or you’re putting your comments in the package body instead of the spec, or you’re putting them in both place?
Object level docs is an interesting idea. Can you submit that to the Exchange?
PLDoc shows everything in the package specification. Is there a way to also get the code in the package body?
Raoul
You mean private objects documented in the body and not in the spec? If they’re private, why would you want them documented 😉
I guess if you put it that way. I was looking for a way to document everything for me and my team.
We could add support for the package bodies, I’m just trying to understand what information is missing by NOT including the bodies. The only thing that comes to mind is private package members.
I was looking at it the wrong way. Looking for something to document every line of code, but that is not what PLDoc and other kinds of documentations of code is supposed to be. We just have to write more comments, which will be written in the PLDoc, so we can understand the purpose of the code.
But putting the private packages in (marked as private) could help with the understanding of the code, especially the flow of the code. Let’s say a package would take an amount of days and a employee number and return his monthly salary (public). But there are 3 ways to calculate his salary so 3 private packages are made. Then that amount is added to a calculated bonus (also a private package). It would help to be able to write out this flow.
You can also place your private methods in a package specification and only expose them (via conditional compilation) when you want to test them. That way they are documented, but not exposed.
Example:
[sql]
create or replace PACKAGE "PKG_UTILITIES" AUTHID CURRENT_USER AS
/**
* Description: This package contains procedures and functions that encapsulate DDL commands for use in the ETL process. It uses "Invoker" rights.
*/
en_assertfail constant NUMBER := -20999;
exc_assertfail exception;
pragma exception_init (exc_assertfail,-20999);
/**
————————————————————
/* The following code, bounded by the $IF and $END
statements, is an example of conditional compilation. Under
normal (default) conditions, the 3 "assert" procedures are private
methods, are not exposed in the package signature, and CANNOT be invoked
by themselves. However, if the command:
"alter session set plsql_ccflags = ‘vin_debug:true’;"
is issued prior to compilation, they become exposed. This is done
in order to conditionally expose them for testing.
*/
————————————————————
$IF $$vin_debug $THEN
———————————-
/**
This procedure is part of an implementation of "Design-by-Contract" (developed by Bertrand Meyer).
This implementation for PL/SQL was developed by John Beresniewicz.
Assertions are the basic mechanism for expressing and enforcing software contract elements in the code itself.
Assertions basically encapsulate statements about system state that must be true or there is an error.
This procedure tests the truth of a condition, and raises an exception if condition is not true.
This is a Private Method, only invocable by other procedures within this package.
This procedure appears in this package specification so that it can be exposed via
conditional compilation for testing purposes.
* @param pi_condition the condition being tested
* @param pi_msg the message to be returned if the condition is not true
* @param pi_module the module for which the condition is being tested.
* @throws ASSERTFAIL if the assertion is not true, and a message indicating what assertion failed and what module it was in.
+———-++
*/
PROCEDURE assert(
pi_condition IN BOOLEAN,
pi_msg IN VARCHAR2 DEFAULT NULL,
pi_module IN VARCHAR2 DEFAULT NULL
);
———————————-
/**
This procedure calls the procedure ASSERT for PRECONDITIONS.
This is a Private Method, only invocable by other procedures within this package.
This specification appears in this package specification so that it can be exposed via
conditional compilation for testing purposes.
* @param pi_condition the condition being tested
* @param pi_msg the message to be returned if the condition is not true
* @param pi_module the module for which the condition is being tested.
*/
PROCEDURE assert_precondition(
pi_condition IN BOOLEAN,
pi_msg IN VARCHAR2 DEFAULT NULL,
pi_module IN VARCHAR2 DEFAULT NULL
);
———————————-
/**
This procedure calls the procedure ASSERT for POSTCONDITIONS.
This is a Private Method, only invocable by other procedures within this package
This procedure appears in this package specification so that it can be exposed via
conditional compilation for testing purposes.
* @param pi_condition the condition being tested
* @param pi_msg the message to be returned if the condition is not true
* @param pi_module the module for which the condition is being tested.
*/
PROCEDURE assert_postcondition(
pi_condition IN BOOLEAN,
pi_msg IN VARCHAR2 DEFAULT NULL,
pi_module IN VARCHAR2 DEFAULT NULL
);
———————————-
$END
/**
This procedure gathers statistics for a specified table. It first calls SYS.DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO, then checks for
"staleness" of the statistics for that table by checking SYS.USER_TAB_STATISTICS. If the statistics are stale or do not
exist, it calls SYS.DBMS_STATS.GATHER_TABLE_STATS. It uses the recommended default values for the input parameters to that procedure.
* @param pi_owner name of the schema that owns the table in question
* @param pi_table_name name of the table
* @param pi_workflow_name name of the workflow invoking this procedure. Used for logging.
* @param po_stats_gathered whether or not the statistics were gathered.
*/
PROCEDURE gather_table_stats(
pi_owner IN VARCHAR2,
pi_table_name IN VARCHAR2,
pi_workflow_name IN VARCHAR2,
po_stats_gathered OUT BOOLEAN
);
————————————
END pkg_utilities;
[/sql]
Tied to connection schema, yes.
Great ideas on the per object and object/schema selection. Would you mind submitting those to the Exchange?
We have most of our PL/SQL code commented with PLDoc tags and we them run through PLDoc, so this feature looks very handy. One suggestion I have is to show the “Generate DB Doc…” in the pop-menu when right-clicking on any object, not just the connection. That way, you can select which objects (or whole schemas) that you want to document. An alternative might be to allow the selection of schema and objects in the pop-up window somehow. Looks like it only works for objects owned by the connected user for now, correct?