PL/SQL Interface (deprecated)

Version 0.1.0

Implement the oddgen PL/SQL interface to make a generator visible within SQL Developer. The interface consists of a PL/SQL package with definer or invoker rights to describes the behaviour of generator through a set of PL/SQL types and PL/SQL package functions.

You are free to choose any name you like for the PL/SQL package, but you have to use the types and functions according this specification. You just have to implement a generate function. All other functions are optional.

oddgen for SQL Developer has a default behaviour for non-implemented functions. Implement optional functions only if you need to override the default behaviour. This  simplifies the creation and the maintenance of the interface.

The interface is compatible with Oracle Database Server 9.2 and higher. However, the final compatibility depends on the functionality used in the package body of the oddgen interface and its related database objects. This interface documentation focusses on the PL/SQL package specification only.

This interface version is deprecated. Please see PL/SQL Interface for the current definitions.

PL/SQL Types

The PL/SQL types for the oddgen interface are defined for every generator. These seems wrong on the first glance. However, this way a generator has the chance to stay independent of other custom database objects and may be distributed as a single PL/SQL package. This simplifies sharing database server generators.

Strongly typed PL/SQL types are favoured over weakly types (e.g. associative arrays instead of schemaless XMLTYPE). This might complicate things, especially when generators are invoked from SQL. But running a generator from SQL is not the goal of this interface. More important is a clear and concise interface definition for the integration into SQL Developer.

You have to define these PL/SQL types only if you implement functions requiring them.



All kind of string values.  The name string_type must not be changed. But you may adapt the size for VARCHAR2(1000 CHAR).

Parameter as used in the GUI and within the generator. May contain spaces and special characters such as question mark.  The name param_type must not be changed. But you may adapt the size for VARCHAR2(30 CHAR). It’s recommended to define a constant for each parameter in the package specification and use the constant throughout the PL/SQL package to reference a parameter.

Table of strings. E.g. used to represent the list of valid values for a parameter.

Associative array to define a value per parameter. Used to define default values.

Associative array to define the list of valid values per parameter.

PL/SQL Package Functions

Mandatory package functions are marked with a star (*) must be implemented. All other package functions are optional. Implementation is required only to override the default behaviour.


Get the name of the generator. The name is displayed in the navigator view as node name and in the generate dialog window as window title.

«OWNER». «PACKAGE_NAME» is used, if this function is not implemented.


Get the comprehensive description of the generator. The description is displayed in the navigator view on mouse-over event, in the SQL Developer status bar for a selected generator and in the generate dialog.

«OWNER». «PACKAGE_NAME» is used, if this function is not implemented.


Get the list of object types to be shown as nodes under the generator in the navigator tree.

Tables, Views are used, if this function is not implemented.


Get the list of object names for a chosen object type. This function is called when opening an object type node in the navigator tree. For each returning object name a node is created in the navigator tree.

If this function is not implemented the result of the following query is used:


Get all parameters supported by the generator including default values. The parameters are shown with their default values in the generate dialog. The default values are used if the generator is launched without the generate dialog.

If this function is not implemented then no additional parameters beside object_type and object_name are used.


Get the list of valid values per parameter. A combo box is used in the generate dialog for each parameter with a list-of-values. It is not possible to choose an invalid value from the combobox.

If this function is not implemented, then the parameters cannot be validated in the GUI.


Updates the list of values per parameter. This function is called after a parameter change in generator dialog. While this allows to amend the list-of-values based on user entry, this function call makes the GUI less responsive and affects multiple selection.

Do not implement this function, unless you really need it.

List-of-values are static if this function is not implemented.

generate* (1)

Generates the result.

This function is mandatory. Either this complete signature or the simpler signature without in_params must be implemented. Implementing both generate signatures is valid as well.

generate* (2)

Generates the result. Simplified signature, which is applicable in SQL. However, this signature does not allow to pass additional parameters to the generator beside in_object_type and in_object_name.

This function is mandatory. Either this simpler signature or the complete signature with in_params must be implemented. Implementing both generate signatures is valid as well.


Here’s an example of a complete PL/SQL interface definition.

Use it as template, remove all unwanted types, functions, choose an appropriate package name and implement the package body as well.