![PLDOC, documenting your PL/SQL directly from the database 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "PLDOC, documenting your PL/SQL directly from the database 1")
Ever asked a PL/SQL code developer to create documentation for his code, the way Javadoc is able to create for Java code? It is possible using the open-source tool PLDoc. And now I hear: “PLDoc what is that?”.
PLDoc is an open-source utility for generating HTML documentation of code written in Oracle PL/SQL. This open source project has as goal to provide a tool analogous to JavaDoc and after testing it I was pleasantly surprised. The generated documentation looks great when the code is commented in the proper way, which is merely a thing of getting used to. The only drawback I noticed was the fact that you need to create file(s) for your packages. The good thing is that PLDoc is open-source so you can make a contribution to the project, which we did. We extended the functionality so it can directly create the documentation with the use of a database connection. Adding the optional parameters: -user -password -url – sql will allow PLDoc to create the documentation directly from the database using the SYS.DBMS_METADATA.GET_DDL package function. This function, available from Oracle RDBMS version 9 and above, can extract the ddl for most database entities. In this case we used it to extracte the ddl for package specifications. The -sql option accepts the standard Oracle wildcards and allows you to specify several packages separated by a comma. PLDoc can be downloaded from http://pldoc.sourceforge.net/ this version did, as we published this Blog, not yet contain the modifications we made. If you do not want to wait for the added functionality to be posted on the sourceforge site you can download our modified version here .
PLDoc makes documenting your PL/SQL code much easier.
Gert-Jan Paulissen and Marcos Claver
Hi Elsabe
Are you trying to run it against an Oracle database version 9 and above?
Do you have access to the dbms_metadata package?
I checked the version by downloading it from the link and am able to run it without problems against my version 9 database.
Marcos
hi there,
thanks for the db functionality! i hope this turns out to be what we need to make our packages
available.
i also ran into a problem retrieving the pl/sql packages from the database.
the connection seems fine though, as i first had a typo on the password and then it said
there was a problem with the un/passwd.
the error message i get is (note: connection string and un/passwd changed for security reasons):
C:\Integrator\PLDoc\PLDoc3\pldoc>call pldoc_its.bat -doctitle “ITS PLSql” -url
jdbc:oracle:thin:@cobra.its.co.za:1522:dev -user xxx -password yyy -d testITSfin -sql F%
PLDoc version: (experimental)
Exception in thread “main” java.lang.NoClassDefFoundError: sqlj/runtime/ref/Conn
ectionContextImpl
at net.sourceforge.pldoc.PLDoc.run(PLDoc.java:174)
at net.sourceforge.pldoc.PLDoc.main(PLDoc.java:86)
at line 174 in PLDoc.java is :
1: DbmsMetadata dbmsMetadata = new DbmsMetadata(conn);
this is after the setup of the statement:
1: pstmt = conn.prepareStatement(“SELECT object_name “+
2: “FROM all_objects “+
3: “WHERE owner = ? “+
4: “AND object_name LIKE ?”+
5: “AND object_type = ‘PACKAGE'”+
6: “ORDER BY “+
7: ” object_name”);
should there not be a space at the end of line 4 and 5 before the double-quote? e.g.:
1: pstmt = conn.prepareStatement(“SELECT object_name “+
2: “FROM all_objects “+
3: “WHERE owner = ? “+
4: “AND object_name LIKE ? “+
5: “AND object_type = ‘PACKAGE’ “+
6: “ORDER BY “+
7: ” object_name”);
is there not perhaps another version of this software, which is not available from this link above?
regards and thanks.
Hi there,
Thanks for the added functionality..
I am also experiencing a problem getting the PL/SQL from the database. (note: in message below, I have
changed the connection string and username + password for security reasons).
C:\Integrator\PLDoc\PLDoc3\pldoc>call pldoc_its.bat -doctitle “ITS PLSql” -url
jdbc:oracle:thin:@cobra.its.co.za:1523:dev -user xxx -password yyy -d testITSfin -sql F
PLDoc version: (experimental)
Exception in thread “main” java.lang.NoClassDefFoundError: sqlj/runtime/ref/ConnectionContextImpl
at net.sourceforge.pldoc.PLDoc.run(PLDoc.java:174)
at net.sourceforge.pldoc.PLDoc.main(PLDoc.java:86)
It seems like it does connect, as I typed the password wrong at first and then it complained about
the username/password.
The code at line 174 in PLDoc.java is:
1: pstmt = conn.prepareStatement(“SELECT object_name “+
2: “FROM all_objects “+
3: “WHERE owner = ? “+
4: “AND object_name LIKE ?”+
5: “AND object_type = ‘PACKAGE'”+
6: “ORDER BY “+
7: ” object_name”);
Are there not spaces missing at the end of line 4 and 5? Must it not be:
1: pstmt = conn.prepareStatement(“SELECT object_name “+
2: “FROM all_objects “+
3: “WHERE owner = ? “+
4: “AND object_name LIKE ? “+
5: “AND object_type = ‘PACKAGE’ “+
6: “ORDER BY “+
7: ” object_name”);
Is there perhaps another version of this code which was not published here?
thanks.
Sorry for the inconvenience.
The error occures the moment the oracle jdbc driver gets registerd. Do you have a valid Oracle driver present in your classpath?
What you could do is the following:
Download JDeveloper 10G and unzip it in the direcory c:\oracle\jdev10
You will now have a directory c:\oracle\jdev10
Place the following syntax in a bat file to start PLDOC. The batfile should reside in the directory PLDoc is located.
@echo off
REM pldoc.bat
REM Resolve the directory of pldoc
set pldir=%~d0%~p0
REM Set your Oracle JDEV home here
set ORACLE_HOME=C:\Oracle\jdev10
REM Set bootclasspath.
REM NB: this is needed to make JDK1.4 use our Xerces version instead of internal parser
set bcp=%pldir%xalan\bin\xalan.jar;%pldir%xalan\bin\xml-apis.jar;%pldir%xalan\bin\xercesImpl.jar
REM Set classpath
REM NB: classpath must contain “.” to be able to locate .xsl files
set cp=%pldir%;%pldir%\pldoc.jar;%ORACLE_HOME%\jdbc\lib\classes12.jar;%ORACLE_HOME%\sqlj\lib\runtime12.jar;%ORACLE_HOME%\sqlj\lib\translator.jar
REM Call PLDoc
java -Xbootclasspath/p:”%bcp%” -cp “%cp%” net.sourceforge.pldoc.PLDoc %*
Start pldoc from the commandline using your command.
Note that the username and the name of the packages are case sensitive.
Hope this helps
Cheers Marcos
Doesn’t seem to work.. please advise
C:\pldoc>call pldoc -url jdbc:oracle:thin:@novadev02:1521:DESIGNER -user DES -pa
ssword des -d testdb -sql I%
PLDoc version: (experimental)
Exception in thread “main” java.lang.NoClassDefFoundError: oracle/jdbc/OracleDri
ver
at net.sourceforge.pldoc.PLDoc.run(PLDoc.java:159)
at net.sourceforge.pldoc.PLDoc.main(PLDoc.java:86)
PLDoc only works if the directory paths it is working with have no spaces in them.
Well, in fact, I have the same problem… Whereas index.html, application.xml,… are well generated in the “destdir”, it seems that each html for each package is generated in the current directory.
A solution ?
Tks
Hello Richard,
sorry I was not able to answer your question earlier.
It is good to hear you were able to solve your issues.
Marcos
Hello, well good news I sorted the problem. I was playing around on XP using the area under my documents which caused the generation of the html files to end up in another area of my disk. 🙁 So I simply made sure I was working close to the top of the tree on C:\ and it works fine 🙂
Nice utility.
Richard.
Hi, I have been trying this out but it is not working correctly for me and I need some help. I have dl your version and the one of sourceforge and they both have the same problem on my setup. Mainly they parse the pk header and makes the basic frame up, with the pk name but the link does not go to the next html page, the detail page is not generated. Any ideas whats wrong? It says generating.html which probably supposed to be the detail pages. Oh and of course the test samples do not work either. the list of packages link to missing files. Any help glady appreciated.
Thanks.
Richard.
Wouldn’t expect you to.
Actually, this is the kind of thing Oracle should do… pay a developer to spend 3 to 6 months really enhancing the tool.
Dear Amyhay Gonen and Mike Friedman,
We did not include the parsing of stand alone procedures or Oracle types.
With kind regards,
Marcos Claver
My biggest frustration is the lack of support for Object Types.
BTW, what’s your e-mail? There are some problems with your blog UI at least on my screen… want to send you a screen shot in case you don’t know about them.
Do you have support for oracle type parsing (the orignal version , doesn’t supported it )
One of the limits of pldoc, that the code didn’t parse stand alone procedure and oracle types . Did your changes support it ?
Hi Stephan, PLDoc only documents the information present in the package header. The changes we made to PLDoc enables you to create the documentation through a database connection. You do not need to create a file containing the package header anymore.
Cheers,
Marcos
The last time we checked PLDoc it would only document package header files no other stuff. Did that improve?
😉 stw