PLDOC, documenting your PL/SQL directly from the database

17

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

Share.

About Author

17 Comments

  1. 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

  2. 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.

  3. 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.

  4. 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

  5. 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)

  6. 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

  7. 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

  8. Richard Harrison on

    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.

  9. Richard Harrison on

    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.

  10. Mike Friedman on

    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.

  11. Dear Amyhay Gonen and Mike Friedman,
    We did not include the parsing of stand alone procedures or Oracle types.

    With kind regards,
    Marcos Claver

  12. Mike Friedman on

    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.

  13. amihay gonen on

    Do you have support for oracle type parsing (the orignal version , doesn’t supported it )

  14. amihay gonen on

    One of the limits of pldoc, that the code didn’t parse stand alone procedure and oracle types . Did your changes support it ?

  15. 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