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.
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(30 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.
|
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.
|
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.
Tables, Views 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 |
FUNCTION get_params 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_lov
|
1 |
FUNCTION get_lov RETURN t_lov; |
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.
refresh_lov
|
1 2 3 4 5 |
FUNCTION refresh_lov ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN t_lov; |
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)
|
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 |
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(30 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 */ 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 */ 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 */ 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 */ 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. * * @returns parameters supported by the generator */ FUNCTION get_params RETURN t_param; /** * 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. * * @returns parameters with their list-of-values */ FUNCTION get_lov RETURN t_lov; /** * Updates the list of values per parameter. * This function is called after a parameter change in the GUI. * 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. * * @param in_object_type object type to process * @param in_object_name object_name of in_object_type to process * @param in_params parameters to configure the behavior of the generator * @returns parameters with their list-of-values */ FUNCTION refresh_lov(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param) RETURN t_lov; /** * Generates the result. * Complete signature. * Either this signature or the simplified 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 * @param in_params parameters to configure the behavior of the generator * @returns generator output */ 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 */ FUNCTION generate(in_object_type IN VARCHAR2, in_object_name IN VARCHAR2) RETURN CLOB; END oddgen_interface_example; / |