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.
Syntax
Description
|
1 |
SUBTYPE string_type IS VARCHAR2(1000 CHAR); |
All kind of string values. The name string_type must not be changed. But you may adapt the size for VARCHAR2(1000 CHAR).
|
1 |
SUBTYPE param_type IS VARCHAR2(60 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.
|
1 |
TYPE t_string IS TABLE OF string_type; |
Table of strings. E.g. used to represent the list of valid values for a parameter.
|
1 |
TYPE t_param IS TABLE OF string_type INDEX BY param_type; |
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.
|
1 |
TYPE t_lov IS TABLE OF t_string INDEX BY param_type; |
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_name
|
1 |
FUNCTION get_name RETURN VARCHAR2; |
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_description
|
1 |
FUNCTION get_description RETURN VARCHAR2; |
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_object_types
|
1 |
FUNCTION get_object_types RETURN t_string; |
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_object_names
|
1 |
FUNCTION get_object_names(in_object_type IN VARCHAR2) RETURN t_string; |
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:
|
1 2 3 4 5 |
SELECT object_name FROM user_objects WHERE object_type = in_object_type AND generated = 'N' ORDER BY object_name |
get_params
|
1 2 3 4 |
FUNCTION get_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param; |
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_ordered_params
|
1 2 3 4 |
FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string; |
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_lov
|
1 2 3 4 5 |
FUNCTION get_lov ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN t_lov; |
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_param_states
|
1 2 3 4 5 |
FUNCTION get_param_states ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN t_param; |
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)
|
1 2 3 4 5 |
FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN CLOB; |
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)
|
1 2 3 4 |
FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB; |
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.
Example
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 |
CREATE OR REPLACE PACKAGE oddgen_interface_example AUTHID CURRENT_USER IS /* * Copyright 2015-2016 Philipp Salvisberg <philipp.salvisberg@trivadis.com> * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ /** * oddgen PL/SQL database server generator. * complete interface example. * PL/SQL package specification only. * PL/SQL package body is not part of the interface definition. * * @headcom */ /** * oddgen PL/SQL data types */ SUBTYPE string_type IS VARCHAR2(1000 CHAR); SUBTYPE param_type IS VARCHAR2(60 CHAR); TYPE t_string IS TABLE OF string_type; TYPE t_param IS TABLE OF string_type INDEX BY param_type; TYPE t_lov IS TABLE OF t_string INDEX BY param_type; /** * Get name of the generator, used in tree view * If this function is not implemented, the package name will be used. * * @returns name of the generator * * @since v0.1 */ FUNCTION get_name RETURN VARCHAR2; /** * Get the description of the generator. * If this function is not implemented, the owner and the package name will be used. * * @returns description of the generator * * @since v0.1 */ FUNCTION get_description RETURN VARCHAR2; /** * Get the list of supported object types. * If this function is not implemented, [TABLE, VIEW] will be used. * * @returns a list of supported object types * * @since v0.1 */ FUNCTION get_object_types RETURN t_string; /** * Get the list of objects for a object type. * If this function is not implemented, the result of the following query will be used: * "SELECT object_name FROM user_objects WHERE object_type = in_object_type" * * @param in_object_type object type to filter objects * @returns a list of objects * * @since v0.1 */ FUNCTION get_object_names(in_object_type IN VARCHAR2) RETURN t_string; /** * Get all parameters supported by the generator including default values. * If this function is not implemented, no parameters will be used. * * @param in_object_type bject type to determine default parameter values * @param in_object_name object name to determine default parameter values * @returns parameters supported by the generator * * @since v0.2 */ FUNCTION get_params(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2) RETURN t_param; /** * 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. * * @param in_object_type object type to determine parameter order * @param in_object_name object name to determine parameter order * @returns ordered parameter names * * @since v0.2 */ FUNCTION get_ordered_params(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2) RETURN t_string; /** * Get the list of values per parameter, if such a LOV is applicable. * If this function is not implemented, then the parameters cannot be validated in the GUI. * This function is called when showing the generate dialog and after updating a parameter. * * @param in_object_type object type to determine list of values * @param in_object_name object_name to determine list of values * @param in_params parameters to configure the behavior of the generator * @returns parameters with their list-of-values * * @since v0.2 */ FUNCTION get_lov(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param) RETURN t_lov; /** * Get parameter states (enabled/disabled) * If this function is not implemented, then the parameters are enabled, if more than one value is valid. * This function is called when showing the generate dialog and after updating a parameter. * * @param in_object_type object type to determine parameter state * @param in_object_name object_name to determine parameter state * @param in_params parameters to configure the behavior of the generator * @returns parameters with their editable state ("0"=disabled, "1"=enabled) * * @since v0.2 */ FUNCTION get_param_states(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param) RETURN t_param; /** * Generates the result. * Complete signature. * Either this signature or the simplified signature or both must be implemented. * * @param in_object_type object type to generate code for * @param in_object_name object_name of in_object_type to generate code for * @param in_params parameters to customize the code generation * @returns generator output * * @since v0.1 */ FUNCTION generate(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param) RETURN CLOB; /** * Generate the result. * Simplified signature, which is applicable in SQL. * Either this signature or the complete signature or both must be implemented. * * @param in_object_type object type to process * @param in_object_name object_name of in_object_type to process * @returns generator output * * @since v0.1 */ FUNCTION generate(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2) RETURN CLOB; END oddgen_interface_example; / |