PL/SQL Interface (deprecated)

Version 0.2.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. See Tutorial 1 – Minimal 1:1 View Generator or Tutorial 2 – Extended 1:1 View Generator for example PL/SQL package body implementations.

oddgen for SQL Developer is backward compatible, hence the deprecated functions of the PL/SQL interface 0.1.0 are fully functional.

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(60 CHAR). It’s recommended to define a constant for each parameter in the package body and use it 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. Every parameter has a default value. However, NULL is a valid default value. The goal is to support code generation with a simple double-click on one ore more object name nodes.

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.

The values TABLE and VIEW 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 all parameter names in the order to be displayed in the generate dialog.

If this function is not implemented, the parameters are ordered implicitly by name. Parameter names returned by this function are taking precedence. Remaining parameters are ordered by name.


Get the list of valid values per parameter.

This function is called before showing the generate dialog and after a parameter change, hence you may amend the list-of-values dynamically. A checkbox is used in the generate dialog for boolean value pairs (0-1, true-false, yes-no). A combo box is used for other list-of-values. It is not possible to choose an invalid value from the combo box. Data entry is disabled for parameters with just one valid value.

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


Get parameter states (enabled/disabled). This function is called when showing the generate dialog and after updating a parameter. Use this function to dynamically enable or disable parameters.

If this function is not implemented, then all parameters with more than one valid value are enabled.

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.