How to Integrate Your PL/SQL Generators in SQL Developer

2016-05-22

1. Install oddgen for SQL Developer

I assume that you are already using SQL Developer 4.x. If not then it is about time that you grab the latest version from here and install it. It’s important to note that oddgen requires version 4 of SQL Developer and won’t run on older versions 3.x, 2.x and 1.x.

SQL Developer comes with a lot of “internal” extensions, but third-party extensions need to be installed explicitly. To install oddgen for SQL Developer I recommend following the steps in installation via update center on oddgen.org. If this is not feasible because your company’s network restricts internet access then download the latest version and install it from file.

To enable the oddgen window, select “Generators” from the “View” menu as shown in the following picture:

You are ready for the next steps, when the Generators window appears in the lower left corner within SQL Developer.

2. Install the Original Generator

If you are going to integrate your existing generator into SQL Developer this step sounds irrelevant. However, I find it useful to install and try generators in a fresh environment to ensure I have not missed some dependencies. I’ve got Steven Feuerstein’s permission to use his generator for this post. It’s a standalone PL/SQL procedure without dependencies. We install the generator “as is” in our database. I will use a schema named oddgen, but you may use another user/schema of course. See the create user DDL on Github if you are interested to know how I’ve set up the oddgen user.

Steven Feuerstein’s gen_table_ddl Generator

-- 1:1 from https://livesql.oracle.com/apex/livesql/file/content_DBEO1MIGH5ZQOILUVV85I1UQC.html
CREATE OR REPLACE PROCEDURE gen_table_ddl ( 
   entity_in     IN VARCHAR2, 
   entities_in   IN VARCHAR2 DEFAULT NULL, 
   add_fky_in    IN BOOLEAN DEFAULT TRUE, 
   prefix_in     IN VARCHAR2 DEFAULT NULL, 
   in_apex_in    IN BOOLEAN DEFAULT FALSE) 
IS 
   c_table_name    CONSTANT VARCHAR2 (100) 
      := prefix_in || NVL (entities_in, entity_in || 's') ; 
 
   c_pkycol_name   CONSTANT VARCHAR2 (100) := entity_in || '_ID'; 
 
   c_user_code     CONSTANT VARCHAR2 (100) 
      := CASE 
            WHEN in_apex_in THEN 'NVL (v (''APP_USER''), USER)' 
            ELSE 'USER' 
         END ; 
 
   PROCEDURE pl (str_in                   IN VARCHAR2, 
                 indent_in                IN INTEGER DEFAULT 3, 
                 num_newlines_before_in   IN INTEGER DEFAULT 0) 
   IS 
   BEGIN 
      FOR indx IN 1 .. num_newlines_before_in 
      LOOP 
         DBMS_OUTPUT.put_line (''); 
      END LOOP; 
 
      DBMS_OUTPUT.put_line (LPAD (' ', indent_in) || str_in); 
   END; 
BEGIN 
   pl ('CREATE TABLE ' || c_table_name || '(', 0); 
   pl (c_pkycol_name || ' INTEGER NOT NULL,'); 
   pl ('created_by VARCHAR2 (132 BYTE) NOT NULL,'); 
   pl ('changed_by VARCHAR2 (132 BYTE) NOT NULL,'); 
   pl ('created_on DATE NOT NULL,'); 
   pl ('changed_on DATE NOT NULL'); 
   pl (');'); 
 
   pl ('CREATE SEQUENCE ' || c_table_name || '_SEQ;', 0, 1); 
   pl ( 
         'CREATE UNIQUE INDEX ' 
      || c_table_name 
      || ' ON ' 
      || c_table_name 
      || '(' 
      || c_pkycol_name 
      || ');', 
      0, 
      1); 
   pl ( 
         'CREATE OR REPLACE TRIGGER ' 
      || c_table_name 
      || '_bir  
      BEFORE INSERT ON ' 
      || c_table_name, 
      0, 
      1); 
   pl ('FOR EACH ROW DECLARE', 3); 
   pl ('BEGIN', 3); 
   pl ('IF :new.' || c_pkycol_name || ' IS NULL', 6); 
   pl ( 
         'THEN :new.' 
      || c_pkycol_name 
      || ' := ' 
      || c_table_name 
      || '_seq.NEXTVAL; END IF;', 
      6); 
 
   pl (':new.created_on := SYSDATE;', 6); 
   pl (':new.created_by := ' || c_user_code || ';', 6); 
   pl (':new.changed_on := SYSDATE;', 6); 
   pl (':new.changed_by := ' || c_user_code || ';', 6); 
   pl ('END ' || c_table_name || '_bir;', 3); 
 
   pl ('CREATE OR REPLACE TRIGGER ' || c_table_name || '_bur', 0, 1); 
   pl ('BEFORE UPDATE ON ' || c_table_name || ' FOR EACH ROW', 3); 
   pl ('DECLARE', 3); 
   pl ('BEGIN', 3); 
   pl (':new.changed_on := SYSDATE;', 6); 
   pl (':new.changed_by := ' || c_user_code || ';', 6); 
   pl ('END ' || c_table_name || '_bur;', 3); 
 
   pl ('ALTER TABLE ' || c_table_name || ' ADD  
      (CONSTRAINT ' || c_table_name, 
       0, 
       1); 
   pl ( 
         'PRIMARY KEY (' 
      || c_pkycol_name 
      || ')  
       USING INDEX ' 
      || c_table_name 
      || ' ENABLE VALIDATE);', 
      3); 
 
   IF add_fky_in 
   THEN 
      pl ( 
            'ALTER TABLE ' 
         || c_table_name 
         || ' ADD (CONSTRAINT fk_' 
         || c_table_name, 
         0, 
         1); 
      pl ('FOREIGN KEY (REPLACE_id)  
     REFERENCES qdb_REPLACE (REPLACE_id)', 3); 
      pl ('ON DELETE CASCADE ENABLE VALIDATE);', 3); 
   END IF; 
END;
/

3. Understand the Input and Output of the Original Generator

Before we start writing a wrapper for the original generator we need to understand its API. The purpose of the generator is described on Oracle Live SQL by Steven Feuerstein as follows:

I follow a few standards for table definitions, including: table name is plural; four standard audit columns (created by/when, updated by/when) with associated triggers; primary key name is [entity]_id, and more. This procedure (refactored from PL/SQL Challenge, the quiz website plsqlchallenge.oracle.com) gives me a consisting start point, from which I then add entity-specific columns, additional foreign keys, etc. Hopefully you will find it useful, too!

The procedure gen_table_ddl expects the following 5 input parameters (see highlighted lines 3 to 7 above):

Parameter Name	Datatype	Optional?	Default	Comments
entity_in	varchar2	No		used to name the primary key column
entities_in	varchar2	Yes	entity_in \|\| ‘s’	used to name table, sequence, index, triggers and constraints
add_fky_in	boolean	Yes	true	true: generates a template for a foreign key constraint false: does not generate a foreign key constraint template
prefix_in	varchar2	Yes	null	prefix for all object names (named by entities_in)
in_apex_in	boolean	Yes	false	true: uses apex built-in variable APP_USER to populate created_by and changed_by. Uses pseudo column USERS only if APP_USER is empty false: always use pseudo column USER to populate created_by and changed_by

By now we should have a decent understanding of the procedure input. But how is the output generated? It’s a procedure after all and there are no output parameters defined. Line 30 reveals the output mechanism. Every line is produced by the nested procedure pl which writes the result to the server output using the DBMS_OUTPUT package.

4. Understand the Basics of the oddgen PL/SQL Interface

When selecting a database connection, the oddgen extension searches the data dictionary for PL/SQL packages implementing the oddgen PL/SQL interface. Basically, it looks for a package functions with the following signature:

FUNCTION generate(in_object_type IN VARCHAR2,
                  in_object_name IN VARCHAR2,
                  in_params      IN t_param) RETURN CLOB;

The interface is designed for generators based on existing database object types such as tables. Therefore it expects the object_type and the object_name as parameters one and two. For our generator, the third parameter is the most interesting one. It allows us to pass additional parameters to the generator. The data type t_param is an associative array and based on the following definition:

SUBTYPE string_type IS VARCHAR2(1000 CHAR);
SUBTYPE param_type IS VARCHAR2(60 CHAR);
TYPE t_param IS TABLE OF string_type INDEX BY param_type;

Through in_params we may pass an unlimited number of key-value pairs to an oddgen generator.

But the oddgen interface is also responsible for defining the representation in the GUI. Let’s look at an example of another generator named “Dropall”:

The “Dropall” node is selected and in the status bar, its description is displayed. Under this node you find the object types “Indexes” and “Tables” but also an artificial object type named “All”. Under the object type nodes, you find the list of all associated object names. This structure supports the following features:

Generate code through a simple double-click on an object name node
Select multiple object name nodes of the same object type to generate code via context-menu
Show a dialog via the context menu for selected object name nodes to change generator parameters

When a generator is called, the selected object name and its associated object type are passed to the generator. Always, without exception. However, for artificial object types and object names, it might be okay to ignore these parameters in the generator implementation.

See the oddgen PL/SQL interface documentation on oddgen.org if you are interested in the details.

For the next steps, it’s just important to know that we have to define the default behaviour of a generator and that a generator provides some information for the GUI only.

5. Write the Wrapper

The following screenshot shows our generator in SQL Developer after selecting “Generate…” from the context menu on the node “Snippet”:

The package specification for this generator looks as follows:

Package Specification

CREATE OR REPLACE PACKAGE gen_table_ddl_oddgen_wrapper IS
   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;

   FUNCTION get_name RETURN VARCHAR2;

   FUNCTION get_description RETURN VARCHAR2;

   FUNCTION get_object_types RETURN t_string;

   FUNCTION get_object_names(in_object_type IN VARCHAR2) RETURN t_string;

   FUNCTION get_params RETURN t_param;

   FUNCTION get_ordered_params RETURN t_string;

   FUNCTION get_lov RETURN t_lov;

   FUNCTION generate(in_object_type IN VARCHAR2,
                     in_object_name IN VARCHAR2,
                     in_params      IN t_param) RETURN CLOB;
END gen_table_ddl_oddgen_wrapper;
/

I’m going to explain some parts of the wrapper implementation based on the package body for this oddgen wrapper:

Package Body

CREATE OR REPLACE PACKAGE BODY gen_table_ddl_oddgen_wrapper IS
   co_entity   CONSTANT param_type := 'Entity name (singular, for PK column)';
   co_entities CONSTANT param_type := 'Entity name (plural, for object names)';
   co_add_fky  CONSTANT param_type := 'Add foreign key?';
   co_prefix   CONSTANT param_type := 'Object prefix';
   co_in_apex  CONSTANT param_type := 'Data populated through APEX?';

   FUNCTION get_name RETURN VARCHAR2 IS
   BEGIN
      RETURN 'Table DDL snippet';
   END get_name;

   FUNCTION get_description RETURN VARCHAR2 IS
   BEGIN
      RETURN 'Steven Feuerstein''s starting point, from which he adds entity-specific columns, additional foreign keys, etc.';
   END get_description;

   FUNCTION get_object_types RETURN t_string IS
   BEGIN
      RETURN NEW t_string('TABLE');
   END get_object_types;

   FUNCTION get_object_names(in_object_type IN VARCHAR2) RETURN t_string IS
   BEGIN
      RETURN NEW t_string('Snippet');
   END get_object_names;

   FUNCTION get_params RETURN t_param IS
      l_params t_param;
   BEGIN
      l_params(co_entity) := 'employee';
      l_params(co_entities) := NULL;
      l_params(co_add_fky) := 'Yes';
      l_params(co_prefix) := NULL;
      l_params(co_in_apex) := 'No';
      RETURN l_params;
   END get_params;

   FUNCTION get_ordered_params RETURN t_string IS
   BEGIN
      RETURN NEW t_string(co_entity, co_entities, co_add_fky, co_prefix);
   END get_ordered_params;

   FUNCTION get_lov RETURN t_lov IS
      l_lov t_lov;
   BEGIN
      l_lov(co_add_fky) := NEW t_string('Yes', 'No');
      l_lov(co_in_apex) := NEW t_string('Yes', 'No');
      RETURN l_lov;
   END get_lov;

   FUNCTION generate(in_object_type IN VARCHAR2,
                     in_object_name IN VARCHAR2,
                     in_params      IN t_param) RETURN CLOB IS
      l_lines    sys.dbms_output.chararr;
      l_numlines INTEGER := 10; -- buffer size
      l_result   CLOB;
   
      PROCEDURE enable_output IS
      BEGIN
         sys.dbms_output.enable(buffer_size => NULL); -- unlimited size
      END enable_output;
   
      PROCEDURE disable_output IS
      BEGIN
         sys.dbms_output.disable;
      END disable_output;
   
      PROCEDURE call_generator IS
      BEGIN
         gen_table_ddl(entity_in   => in_params(co_entity),
                       entities_in => in_params(co_entities),
                       add_fky_in  => CASE
                                         WHEN in_params(co_add_fky) = 'Yes' THEN
                                          TRUE
                                         ELSE
                                          FALSE
                                      END,
                       prefix_in   => in_params(co_prefix),
                       in_apex_in  => CASE
                                         WHEN in_params(co_in_apex) = 'Yes' THEN
                                          TRUE
                                         ELSE
                                          FALSE
                                      END);
      END call_generator;
   
      PROCEDURE copy_dbms_output_to_result IS
      BEGIN
         sys.dbms_lob.createtemporary(l_result, TRUE);
         <<read_dbms_output_into_buffer>>
         WHILE l_numlines > 0
         LOOP
            sys.dbms_output.get_lines(l_lines, l_numlines);
            <<copy_buffer_to_clob>>
            FOR i IN 1 .. l_numlines
            LOOP
               sys.dbms_lob.append(l_result, l_lines(i) || chr(10));
            END LOOP copy_buffer_to_clob;
         END LOOP read_dbms_output_into_buffer;
      END copy_dbms_output_to_result;
   BEGIN
      enable_output;
      call_generator;
      copy_dbms_output_to_result;
      disable_output;
      RETURN l_result;
   END generate;
END gen_table_ddl_oddgen_wrapper;
/

On lines 1 to 6 constants for every parameter are defined. The values are used as labels in the GUI.

The function get_name (line 10) defines the name used in the GUI for this generator.

The function get_description (line 15) returns a description of the generator. The description is shown in the status bar, as a tooltip and in the generator dialog.

The function get_object_types (line 20) defines the valid object types. We’ve chosen the object type “TABLE” because it represents the target code quite well. Using known object types leads also to a nice icon representation.

The function get_object_names (line 25) defines the valid object names for an object type. The parameter in_object_type is not used since our list is static and contains just one value “Snippet”.

The function get_params (lines 31-35) defines the list of input parameters for the generator (besides object type and object name). Here we define our five parameters with their default values. The default values are important to generate meaningful code when double-clicking on the object name node “Snippet”. So, by default, a table named “employees” with a foreign key template and triggers for non-APEX usage is generated.

The function get_ordered_params (line 41) defines the order of the parameters in the generator dialog. Such a definition is necessary since the original order is lost. That’s the expected behaviour for an associative array indexed by a string. The default order by name is not very intuitive in this case.

The function get_lovs (lines 47-48) defines the list of values per input parameter. We use “Yes” and “No” for the boolean parameters co_add_fky and co_in_apex since oddgen supports string parameters only. However, in the GUI typical boolean value pairs such as “Yes-No”, “1-0”, “true-false” are recognised based on the list-of-values definition and are represented as checkboxes. Hence, for the user, it does not matter that technically no boolean parameters are used.

The function generates (lines 103-107) defines the steps to produce the generated code. Each step is represented by a nested procedure call:

enable output – enables the dbms_output with unlimited buffer size
call_generator – calls the original generator code using the parameters passed by oddgen
copy_dbms_output_to_result – copies the output of the original generator into the result CLOB
disable_output – disables dbms_output

Finally, the generated code is returned as CLOB.

6. Grant Access

To ensure that the generator is available for every user connecting to the instance, we have to grant execute rights on the PL/SQL wrapper package. Granting the package to the public is probably the easiest way.

Grant

GRANT EXECUTE ON gen_table_ddl_oddgen_wrapper TO PUBLIC;

7. Run in SQL Developer

Now we may run the generator in SQL Developer. The following video is 39 seconds long, contains no audio signal and shows how to generate the DDLs with default parameters and how to run the generator with amended parameters to generate the DDLs for a table to be used in an APEX application.

8. Conclusion

Every PL/SQL-based code generator producing a document (CLOB, XMLTYPE, JSON), messages via DBMS_OUTPUT or records in tables can be integrated into SQL Developer using the oddgen extension. The effort depends on the number of parameters and their valid values. For simple generators, this won’t take more than a few minutes, especially if you are an experienced oddgen user.

I hope you found this post useful. Your comments and feedback are very much appreciated.