How to Integrate Your PL/SQL Generators in SQL Developer


About three weeks ago Steven Feuerstein tweeted in his tip #501 a link to a generator for the WHEN clause in DML triggers on Oracle Live SQL. Back than I refactored the generator for oddgen – the Oracle community’s dictionary-driven code generator – and published the result on Oracle Live SQL as well. Some days ago Steven tweeted in tip #514 about generating a standardised table DDL and I thought about a short moment to refactor this generator as well, but decided against it. There are a lot of generators around which write their result to DBMS_OUTPUT or into intermediate/helper tables and I believe that it would be more helpful to show how such generators could be integrated into oddgen for SQL Developer. If you are overwhelmed by the length of this blog post (as I was) then I suggest that you scroll down to the bottom and look at the 39 seconds of audio-less video to see a generator in action.

1. Install oddgen for SQL Developer

I assume that you are already using SQL Developer 4.x. If not then it is about time that you grab the latest version from here and install it. It’s important to note that oddgen requires version 4 of SQL Developer and won’t run on older versions 3.x, 2.x and 1.x.

SQL Developer comes with a lot of “internal” extensions, but third party extensions need to be installed explicitly. To install oddgen for SQL Developer I recommend to follow the steps in installation via update center on If this is not feasible because your company’s network restricts the internet access then download the latest version and install it from file.

To enable the oddgen window, select “Generators” from the “View” menu as shown in the following picture:


You are ready for the next steps, when the Generators window appears in the lower left corner within SQL Developer.


2. Install the Original Generator

If you are going to integrate your existing generator into SQL Developer this step sounds irrelevant. However, I find it useful to install and try generators in a fresh environment to ensure I have not missed some dependencies. I’ve got Steven Feuerstein’s permission to use his generator for this post. It’s a standalone PL/SQL procedure without dependencies. We install the generator “as is” in our database. I will use a schema named oddgen, but you may use another user/schema of course. See the create user DDL on Github if you are interested to know how I’ve setup the oddgen user.

3. Understand the Input and Output of the Original Generator

Before we start writing a wrapper for the original generator we need to understand its API. The purpose of the generator is described on Oracle Live SQL by Steven Feuerstein as follows:

I follow a few standards for table definitions, including: table name is plural; four standard audit columns (created by/when, updated by/when) with associated triggers; primary key name is [entity]_id, and more. This procedure (refactored from PL/SQL Challenge, the quiz website gives me a consisting start point, from which I then add entity-specific columns, additional foreign keys, etc. Hopefully you will find it useful, too!

The procedure gen_table_ddl expects the following 5 input parameters (see highlighted lines 3 to 7 above):

Parameter NameDatatypeOptional?DefaultComments
entity_invarchar2Noused to name the primary key column
entities_invarchar2Yesentity_in || 's'used to name table, sequence, index, triggers and constraints
add_fky_inbooleanYestruetrue: generates a template for a foreign key constraint
false: does not generate a foreign key constraint template
prefix_invarchar2Yesnullprefix for all object names (named by entities_in)
in_apex_inbooleanYesfalsetrue: uses apex built-in variable APP_USER to populate created_by and changed_by. Uses pseudo column USERS only if APP_USER is empty
false: always use pseudo column USER to populate created_by and changed_by

By now we should have a decent understanding of the procedure input. But how is the output generated? It’s a procedure after all and there are no output parameters defined. Line 30 reveals the output mechanism. Every line is produced by the nested procedure pl which writes the result to the server output using the DBMS_OUTPUT package.

4. Understand the Basics of the oddgen PL/SQL Interface

When selecting a database connection, the oddgen extension searches the data dictionary for PL/SQL packages implementing the oddgen PL/SQL interface. Basically it looks for a package functions with the following signature:

The interface is designed for generators based on existing database object types such as tables. Therefore it expects the object_type and the object_name as parameter one and two. For our generator the third parameter is the most interesting one. It allows us to pass additional parameters to the generator. The data type t_param is an associative array and based on the following definition:

Through in_params we may pass an unlimited number of key-value pairs to an oddgen generator.

But the oddgen interface is also responsible to define the representation in the GUI. Let’s look at an example of another generator named “Dropall”:


The “Dropall” node is selected and in the status bar its description is displayed. Under this node you find the object types “Indexes” and “Tables” but also an artificial object type named “All”. Under the object type nodes you find the list of all associated object names. This structure supports the following features:

  1. Generate code through a simple double-click on an object name node
  2. Select multiple object name nodes of the same object type to generate code via context-menu
  3. Show a dialog via context menu for selected object name nodes to change generator parameters

When a generator is called, the selected object name and its associated object type are passed to the generator. Always, without exception. However, for artificial object types and object names it might be okay to ignore these parameters in the generator implementation.

See the oddgen PL/SQL interface documentation on if you are interested in the details.

For the next steps it’s just important to know that we have to define the default behaviour of a generator and that a generator provides some information for the GUI only.

5. Write the Wrapper

The following screenshot shows our generator in SQL Developer after selecting “Generate…” from the context menu on the node “Snippet”:


The package specification for this generator looks as follows:

I’m going to explain some parts of the of the wrapper implementation based on the package body for this oddgen wrapper:

On line 1 to 6 constants for every parameter are defined. The values are used as labels in the GUI.

The function get_name (line 10) defines the name used in the GUI for this generator.

The function get_description (line 15) returns a description of the generator. The description is shown in the status bar, as tool-tip and in the generator dialog.

The function get_object_types (line 20) defines the valid object types. We’ve chosen the object type “TABLE” because it represents the target code quite well. Using a known object types leads also to a nice icon representation.

The function get_object_names (line 25) defines the valid object names for an object type.  The parameter in_object_type is not used since our list is static and contains just one value “Snippet”.

The function get_params (line 31-35) defines the list of input parameters for the generator (beside object type and object name). Here we define our five parameters with their default values. The default values are important to generate meaningful code when double clicking on the object name node “Snippet”. So, by default a table named “employees” with a foreign key template and triggers for non-APEX usage is generated.

The function get_ordered_params (line 41) defines the order of the parameters in the generator dialog. Such a definition is necessary since the original order ist lost. That’s expected behaviour for an associative array indexed by string. The default order by name is not very intuitive in this case.

The function get_lovs  (line 47-48) defines the list-of-values per input parameter. We use “Yes” and “No” for the boolean parameters co_add_fky and co_in_apex since oddgen supports string parameters only. However, in the GUI typical boolean value pairs such as “Yes-No”, “1-0”, “true-false” are recognised  based on the list-of-values definition and are represented as checkboxes. Hence, for the user it does not matter that technically no boolean parameters are used.

The function generate (line 103-107) defines the steps to produce the generated code. Each step is represented by a nested procedure call:

  • enable output – enables the dbms_output with unlimited buffer size
  • call_generator – calls the original generator code using the parameters passed by oddgen
  • copy_dbms_output_to_result – copies the output of the original generator into the result CLOB
  • disable_output – disables dbms_output

Finally the generated code is returned as CLOB.

6. Grant Access

To ensure that the generator is available for every user connecting to the instance, we have to grant access rights on the PL/SQL wrapper package. Granting the package to public is probably the easiest way.


7. Run in SQL Developer

Now we may run the generator in SQL Developer. The following video is 39 seconds long, contains no audio signal and shows how to generate the DDLs  with default parameters and how to run the generator with amended parameters to generate the DDLs for a table to be used in an APEX application.


8. Conclusion

Every PL/SQL based code generator producing a document (CLOB, XMLTYPE, JSON), messages via DBMS_OUTPUT or records in tables can be integrated into SQL Developer using the oddgen extension. The effort depends on the number of parameters and their valid values. For simple generators this won’t take more than a few minutes, especially if you are an experienced oddgen user.

I hope you found this post useful. Your comments and feedback is very much appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *