PL/SQL Interface
Version 0.3.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.2.0 are fully functional.
PL/SQL Types
The PL/SQL types for the oddgen interface are defined in PL/SQL package specification named oddgen_types.
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.
Syntax
Description
|
1 |
SUBTYPE key_type IS VARCHAR2(4000 BYTE); |
Keys, generic string values
|
1 |
SUBTYPE value_type IS VARCHAR2(32767 BYTE); |
Values, typically short strings, but may contain larger values, e.g. for JSON content or similar.
|
1 |
TYPE t_value_type IS TABLE OF value_type; |
Value array.
|
1 |
TYPE t_param_type IS TABLE OF value_type INDEX BY key_type; |
Associative array of parameters (key-value pairs).
|
1 |
TYPE t_lov_type IS TABLE OF t_value_type INDEX BY key_type; |
Associative array of list-of-values (key-value pairs, but a value is a value array).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
TYPE r_node_type IS RECORD ( id key_type, parent_id key_type, name value_type, description value_type, icon_name key_type, icon_base64 value_type, params t_param_type, leaf BOOLEAN, generatable BOOLEAN, multiselectable BOOLEAN, relevant BOOLEAN ); |
Record type to represent a node in the SQL Developer navigator tree.
Icon is evaluated as follows:
- by icon_name, if defined
- by icon_base64, if defined
- by parent_id, if leaf node and parent_id is a known object type (normal icon)
- by id, if non-leaf node and id is a known object type (folder icon)
- UNKNOWN_ICON, if leaf node
- UNKNOWN_FOLDER_ICON, if non-leaf node
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, used in tree view.
If this function is not implemented, the package name will be used.
get_description
|
1 |
FUNCTION get_description RETURN VARCHAR2; |
Get the description of the generator.
If this function is not implemented, the owner and the package name will be used.
get_folders
|
1 |
FUNCTION get_folders RETURN oddgen_types.t_value_type; |
Get the list of folder names. The first entry in the list is the folder under ‘All Generators’, the second one is the subfolder under the first one and so on. The generator will be visible in the last folder of the list.
If this function is not implemented, the default is oddgen_types.t_value_type(‘Database Server Generators’). It is possible to override the default in the oddgen preference dialog.
get_help
|
1 |
FUNCTION get_help RETURN CLOB; |
Get the help of the generator.
If this function is not implemented, no help is available.
get_nodes
|
1 2 3 |
FUNCTION get_nodes( in_parent_node_id IN oddgen_types.key_type DEFAULT NULL ) RETURN oddgen_types.t_node_type; |
Get the list of nodes shown to be shown in the SQL Developer navigator tree. The implementation decides if nodes are returned eagerly oder lazily.
If this function is not implemented nodes for tables and views are returned lazily.
get_ordered_params
|
1 |
FUNCTION get_ordered_params RETURN oddgen_types.t_value_type; |
Get the list of parameter names in the order to be displayed in the generate dialog.
If this function is not implemented, the parameters are ordered by name. Parameter names returned by this function are taking precedence. Remaining parameters are ordered by name.
get_lov
|
1 2 3 4 |
FUNCTION get_lov( in_params IN oddgen_types.t_param_type, in_nodes IN oddgen_types.t_node_type ) RETURN oddgen_types.t_lov_type; |
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.
get_param_states
|
1 2 3 4 |
FUNCTION get_param_states( in_params IN oddgen_types.t_param_type, in_nodes IN oddgen_types.t_node_type ) RETURN oddgen_types.t_param_type; |
Get the list of 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.
generate_prolog
|
1 2 3 |
FUNCTION generate_prolog( in_nodes IN oddgen_types.t_node_type ) RETURN CLOB; |
Generates the prolog
If this function is not implemented, no prolog will be generated. Called once for all selected nodes at the very beginning of the processing.
generate_separator
|
1 |
FUNCTION generate_separator RETURN VARCHAR2; |
Generates the separator between generate calls.
If this function is not implemented, an empty line will be generated. Called once, but applied between generator calls.
generate_epilog
|
1 2 3 |
FUNCTION generate_epilog( in_nodes IN oddgen_types.t_node_type ) RETURN CLOB; |
Generates the epilog.
If this function is not implemented, no epilog will be generated. Called once for all selected nodes at the very end of the processing.
generate*
|
1 2 3 |
FUNCTION generate( in_node IN oddgen_types.r_node_type ) RETURN CLOB; |
Generates the result. Called for every selected and relevant node, including its children.
Template
Here’s the complete PL/SQL package specification, it will be used as template for new PL/SQL generators.
|
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 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 |
CREATE OR REPLACE PACKAGE oddgen_interface AUTHID CURRENT_USER IS /* * Copyright 2015-2017 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. * PL/SQL package specification only. * PL/SQL package body is not part of the interface definition. * Requires the ODDGEN_TYPES package specification to be installed in * the same schema. * * @headcom */ /** * Get the 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 folder names. The first entry in the list is the folder * under 'All Generators', the second one is the subfolder under the * first one and so on. The generator will be visible in the last folder * of the list. * If this function is not implemented, the default is * oddgen_types.t_value_type('Database Server Generators'). * It is possible to override the default in the oddgen preference dialog. * * @returns the list of folders under 'All Generators' * * @since v0.3 */ FUNCTION get_folders RETURN oddgen_types.t_value_type; /** * Get the help of the generator. * If this function is not implemented, no help is available. * * @returns help text as HTML * * @since v0.3 */ FUNCTION get_help RETURN CLOB; /** * Get the list of nodes shown to be shown in the SQL Developer navigator tree. * The implementation decides if nodes are returned eagerly oder lazily. * If this function is not implemented nodes for tables and views are returned lazily. * * @param in_parent_node_id root node to get children for * @returns a list of nodes in a hierarchical structure * * @since v0.3 */ FUNCTION get_nodes( in_parent_node_id IN oddgen_types.key_type DEFAULT NULL ) RETURN oddgen_types.t_node_type; /** * Get the list of parameter names in the order to be displayed in the generate dialog. * If this function is not implemented, the parameters are ordered by name. * Parameter names returned by this function are taking precedence. * Remaining parameters are ordered by name. * * @returns ordered parameter names * * @since v0.3 */ FUNCTION get_ordered_params RETURN oddgen_types.t_value_type; /** * 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_params parameters with active values to determine parameter state * @param in_nodes table of selected nodes to be generated with default parameter values * @returns parameters with their list-of-values * * @since v0.3 */ FUNCTION get_lov( in_params IN oddgen_types.t_param_type, in_nodes IN oddgen_types.t_node_type ) RETURN oddgen_types.t_lov_type; /** * Get the list of 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_params parameters with active values to determine parameter state * @param in_nodes table of selected nodes to be generated with default parameter values * @returns parameters with their editable state ("0"=disabled, "1"=enabled) * * @since v0.3 */ FUNCTION get_param_states( in_params IN oddgen_types.t_param_type, in_nodes IN oddgen_types.t_node_type ) RETURN oddgen_types.t_param_type; /** * Generates the prolog * If this function is not implemented, no prolog will be generated. * Called once for all selected nodes at the very beginning of the processing. * * @param in_nodes table of selected nodes to be generated * @returns generator prolog * * @since v0.3 */ FUNCTION generate_prolog( in_nodes IN oddgen_types.t_node_type ) RETURN CLOB; /** * Generates the separator between generate calls. * If this function is not implemented, an empty line will be generated. * Called once, but applied between generator calls. * * @returns generator separator * * @since v0.3 */ FUNCTION generate_separator RETURN VARCHAR2; /** * Generates the epilog. * If this function is not implemented, no epilog will be generated. * Called once for all selected nodes at the very end of the processing. * * @param in_nodes table of selected nodes to be generated * @returns generator epilog * * @since v0.3 */ FUNCTION generate_epilog( in_nodes IN oddgen_types.t_node_type ) RETURN CLOB; /** * Generates the result. * This function must be implemented. * Called for every selected and relevant node, including its children. * * @param in_node node to be generated * @returns generator output * * @since v0.3 */ FUNCTION generate( in_node IN oddgen_types.r_node_type ) RETURN CLOB; END oddgen_interface; / |