{"id":7050,"date":"2016-05-22T20:40:12","date_gmt":"2016-05-22T18:40:12","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=7050"},"modified":"2023-11-07T22:51:41","modified_gmt":"2023-11-07T21:51:41","slug":"how-to-integrate-your-plsql-generators-in-sql-developer","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2016\/05\/22\/how-to-integrate-your-plsql-generators-in-sql-developer\/","title":{"rendered":"How to Integrate Your PL\/SQL Generators in SQL Developer"},"content":{"rendered":"\n

About three weeks ago Steven Feuerstein tweeted in his tip #501<\/a>\u00a0a link to a generator for the WHEN clause in DML triggers on Oracle Live SQL. Back then I refactored the generator for\u00a0oddgen – the Oracle community’s dictionary-driven code generator – and published the result<\/a> on Oracle Live SQL as well. Some days ago Steven\u00a0tweeted in tip\u00a0#514<\/a>\u00a0about generating a standardised table DDL and I thought about a short moment to refactor this generator as well, but decided against it. There are a lot of generators around which write their\u00a0result to DBMS_OUTPUT or into intermediate\/helper tables and I believe\u00a0that it would be more helpful to show how such\u00a0generators could be integrated into oddgen for SQL Developer. If you are overwhelmed by the length of this blog post (as I was) then I suggest that you scroll down to the bottom and look at the 39 seconds of audio-less video to see a generator in action.<\/p>\n\n\n\n

1. Install oddgen for SQL Developer<\/h2>\n\n\n\n

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<\/a> 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.<\/p>\n\n\n\n

SQL Developer comes with a lot of “internal” extensions, but third-party extensions need\u00a0to be installed explicitly. To install oddgen for SQL Developer I recommend following the steps in\u00a0installation via update center<\/a>\u00a0on oddgen.org<\/a>.\u00a0If this is not feasible because your company’s network restricts internet access then download the latest version<\/a>\u00a0and install it from file<\/a>.<\/p>\n\n\n\n

To enable the oddgen window, select “Generators” from the “View” menu as shown in the following picture:<\/p>\n\n\n\n

\"menu_view_generators\"<\/a><\/figure>\n\n\n\n

You are ready for the next steps, when the Generators window appears in the lower left corner within SQL Developer.<\/p>\n\n\n\n

\"oddgen_generators_window\"<\/a><\/figure>\n\n\n\n

2. Install the Original Generator<\/h2>\n\n\n\n

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\u00a0generators in a fresh environment to ensure I have not missed some dependencies. I’ve got Steven Feuerstein’s permission to use his generator\u00a0for this post.\u00a0It’s\u00a0a standalone PL\/SQL procedure without dependencies.\u00a0We 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<\/a> on Github if you are interested to know how I’ve set up the oddgen user.<\/p>\n\n\n\n

Steven Feuerstein’s gen_table_ddl Generator<\/span><\/path><\/path><\/svg><\/span>
-- 1:1 from https:\/\/livesql.oracle.com\/apex\/livesql\/file\/content_DBEO1MIGH5ZQOILUVV85I1UQC.html<\/span><\/span>\nCREATE OR REPLACE<\/span> PROCEDURE gen_table_ddl ( <\/span><\/span>\n   entity_in     <\/span>IN<\/span> <\/span>VARCHAR2<\/span>, <\/span><\/span>\n   entities_in   <\/span>IN<\/span> <\/span>VARCHAR2<\/span> <\/span>DEFAULT<\/span> <\/span>NULL<\/span>, <\/span><\/span>\n   add_fky_in    <\/span>IN<\/span> <\/span>BOOLEAN<\/span> <\/span>DEFAULT<\/span> <\/span>TRUE<\/span>, <\/span><\/span>\n   prefix_in     <\/span>IN<\/span> <\/span>VARCHAR2<\/span> <\/span>DEFAULT<\/span> <\/span>NULL<\/span>, <\/span><\/span>\n   in_apex_in    <\/span>IN<\/span> <\/span>BOOLEAN<\/span> <\/span>DEFAULT<\/span> <\/span>FALSE<\/span>) <\/span><\/span>\nIS<\/span> <\/span><\/span>\n   c_table_name    <\/span>CONSTANT<\/span> <\/span>VARCHAR2<\/span> (<\/span>100<\/span>) <\/span><\/span>\n      := prefix_in || <\/span>NVL<\/span> (entities_in, entity_in || <\/span>'s'<\/span>) ; <\/span><\/span>\n <\/span><\/span>\n   c_pkycol_name   <\/span>CONSTANT<\/span> <\/span>VARCHAR2<\/span> (<\/span>100<\/span>) := entity_in || <\/span>'_ID'<\/span>; <\/span><\/span>\n <\/span><\/span>\n   c_user_code     <\/span>CONSTANT<\/span> <\/span>VARCHAR2<\/span> (<\/span>100<\/span>) <\/span><\/span>\n      := <\/span>CASE<\/span> <\/span><\/span>\n            <\/span>WHEN<\/span> in_apex_in <\/span>THEN<\/span> <\/span>'NVL (v (''APP_USER''), USER)'<\/span> <\/span><\/span>\n            <\/span>ELSE<\/span> <\/span>'USER'<\/span> <\/span><\/span>\n         <\/span>END<\/span> ; <\/span><\/span>\n <\/span><\/span>\n   <\/span>PROCEDURE<\/span> <\/span>pl<\/span> (str_in                   <\/span>IN<\/span> <\/span>VARCHAR2<\/span>, <\/span><\/span>\n                 indent_in                <\/span>IN<\/span> <\/span>INTEGER<\/span> <\/span>DEFAULT<\/span> <\/span>3<\/span>, <\/span><\/span>\n                 num_newlines_before_in   <\/span>IN<\/span> <\/span>INTEGER<\/span> <\/span>DEFAULT<\/span> <\/span>0<\/span>) <\/span><\/span>\n   <\/span>IS<\/span> <\/span><\/span>\n   <\/span>BEGIN<\/span> <\/span><\/span>\n      <\/span>FOR<\/span> indx <\/span>IN<\/span> <\/span>1<\/span> .. num_newlines_before_in <\/span><\/span>\n      <\/span>LOOP<\/span> <\/span><\/span>\n         <\/span>DBMS_OUTPUT.<\/span>put_line<\/span> (<\/span>''<\/span>); <\/span><\/span>\n      <\/span>END LOOP<\/span>; <\/span><\/span>\n <\/span><\/span>\n      <\/span>DBMS_OUTPUT.<\/span>put_line<\/span> (<\/span>LPAD<\/span> (<\/span>' '<\/span>, indent_in) || str_in); <\/span><\/span>\n   <\/span>END<\/span>; <\/span><\/span>\nBEGIN<\/span> <\/span><\/span>\n   pl (<\/span>'CREATE TABLE '<\/span> || c_table_name || <\/span>'('<\/span>, <\/span>0<\/span>); <\/span><\/span>\n   pl (c_pkycol_name || <\/span>' INTEGER NOT NULL,'<\/span>); <\/span><\/span>\n   pl (<\/span>'created_by VARCHAR2 (132 BYTE) NOT NULL,'<\/span>); <\/span><\/span>\n   pl (<\/span>'changed_by VARCHAR2 (132 BYTE) NOT NULL,'<\/span>); <\/span><\/span>\n   pl (<\/span>'created_on DATE NOT NULL,'<\/span>); <\/span><\/span>\n   pl (<\/span>'changed_on DATE NOT NULL'<\/span>); <\/span><\/span>\n   pl (<\/span>');'<\/span>); <\/span><\/span>\n <\/span><\/span>\n   pl (<\/span>'CREATE SEQUENCE '<\/span> || c_table_name || <\/span>'_SEQ;'<\/span>, <\/span>0<\/span>, <\/span>1<\/span>); <\/span><\/span>\n   pl ( <\/span><\/span>\n         <\/span>'CREATE UNIQUE INDEX '<\/span> <\/span><\/span>\n      || c_table_name <\/span><\/span>\n      || <\/span>' ON '<\/span> <\/span><\/span>\n      || c_table_name <\/span><\/span>\n      || <\/span>'('<\/span> <\/span><\/span>\n      || c_pkycol_name <\/span><\/span>\n      || <\/span>');'<\/span>, <\/span><\/span>\n      <\/span>0<\/span>, <\/span><\/span>\n      <\/span>1<\/span>); <\/span><\/span>\n   pl ( <\/span><\/span>\n         <\/span>'CREATE OR REPLACE TRIGGER '<\/span> <\/span><\/span>\n      || c_table_name <\/span><\/span>\n      || <\/span>'_bir  <\/span><\/span>\n      BEFORE INSERT ON '<\/span> <\/span><\/span>\n      || c_table_name, <\/span><\/span>\n      <\/span>0<\/span>, <\/span><\/span>\n      <\/span>1<\/span>); <\/span><\/span>\n   pl (<\/span>'FOR EACH ROW DECLARE'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n   pl (<\/span>'BEGIN'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n   pl (<\/span>'IF :new.'<\/span> || c_pkycol_name || <\/span>' IS NULL'<\/span>, <\/span>6<\/span>); <\/span><\/span>\n   pl ( <\/span><\/span>\n         <\/span>'THEN :new.'<\/span> <\/span><\/span>\n      || c_pkycol_name <\/span><\/span>\n      || <\/span>' := '<\/span> <\/span><\/span>\n      || c_table_name <\/span><\/span>\n      || <\/span>'_seq.NEXTVAL; END IF;'<\/span>, <\/span><\/span>\n      <\/span>6<\/span>); <\/span><\/span>\n <\/span><\/span>\n   pl (<\/span>':new.created_on := SYSDATE;'<\/span>, <\/span>6<\/span>); <\/span><\/span>\n   pl (<\/span>':new.created_by := '<\/span> || c_user_code || <\/span>';'<\/span>, <\/span>6<\/span>); <\/span><\/span>\n   pl (<\/span>':new.changed_on := SYSDATE;'<\/span>, <\/span>6<\/span>); <\/span><\/span>\n   pl (<\/span>':new.changed_by := '<\/span> || c_user_code || <\/span>';'<\/span>, <\/span>6<\/span>); <\/span><\/span>\n   pl (<\/span>'END '<\/span> || c_table_name || <\/span>'_bir;'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n <\/span><\/span>\n   pl (<\/span>'CREATE OR REPLACE TRIGGER '<\/span> || c_table_name || <\/span>'_bur'<\/span>, <\/span>0<\/span>, <\/span>1<\/span>); <\/span><\/span>\n   pl (<\/span>'BEFORE UPDATE ON '<\/span> || c_table_name || <\/span>' FOR EACH ROW'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n   pl (<\/span>'DECLARE'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n   pl (<\/span>'BEGIN'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n   pl (<\/span>':new.changed_on := SYSDATE;'<\/span>, <\/span>6<\/span>); <\/span><\/span>\n   pl (<\/span>':new.changed_by := '<\/span> || c_user_code || <\/span>';'<\/span>, <\/span>6<\/span>); <\/span><\/span>\n   pl (<\/span>'END '<\/span> || c_table_name || <\/span>'_bur;'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n <\/span><\/span>\n   pl (<\/span>'ALTER TABLE '<\/span> || c_table_name || <\/span>' ADD  <\/span><\/span>\n      (CONSTRAINT '<\/span> || c_table_name, <\/span><\/span>\n       <\/span>0<\/span>, <\/span><\/span>\n       <\/span>1<\/span>); <\/span><\/span>\n   pl ( <\/span><\/span>\n         <\/span>'PRIMARY KEY ('<\/span> <\/span><\/span>\n      || c_pkycol_name <\/span><\/span>\n      || <\/span>')  <\/span><\/span>\n       USING INDEX '<\/span> <\/span><\/span>\n      || c_table_name <\/span><\/span>\n      || <\/span>' ENABLE VALIDATE);'<\/span>, <\/span><\/span>\n      <\/span>3<\/span>); <\/span><\/span>\n <\/span><\/span>\n   <\/span>IF<\/span> add_fky_in <\/span><\/span>\n   <\/span>THEN<\/span> <\/span><\/span>\n      pl ( <\/span><\/span>\n            <\/span>'ALTER TABLE '<\/span> <\/span><\/span>\n         || c_table_name <\/span><\/span>\n         || <\/span>' ADD (CONSTRAINT fk_'<\/span> <\/span><\/span>\n         || c_table_name, <\/span><\/span>\n         <\/span>0<\/span>, <\/span><\/span>\n         <\/span>1<\/span>); <\/span><\/span>\n      pl (<\/span>'FOREIGN KEY (REPLACE_id)  <\/span><\/span>\n     REFERENCES qdb_REPLACE (REPLACE_id)'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n      pl (<\/span>'ON DELETE CASCADE ENABLE VALIDATE);'<\/span>, <\/span>3<\/span>); <\/span><\/span>\n   <\/span>END IF<\/span>; <\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
3. Understand the Input and Output of the Original Generator<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
\n
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!<\/p>\n<\/blockquote>\n\n\n\n
The procedure gen_table_ddl expects the following 5 input parameters (see highlighted lines 3 to 7 above):<\/p>\n\n\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t\n\t
Parameter Name<\/th>Datatype<\/th>Optional?<\/th>Default<\/th>Comments<\/th>\n<\/tr>\n<\/thead>\n
entity_in<\/td>varchar2<\/td>No<\/td><\/td>used to name the primary key column<\/td>\n<\/tr>\n
entities_in<\/td>varchar2<\/td>Yes<\/td>entity_in || ‘s’<\/td>used to name table, sequence, index, triggers and constraints<\/td>\n<\/tr>\n
add_fky_in<\/td>boolean<\/td>Yes<\/td>true<\/td>true: generates a template for a foreign key constraint 
\nfalse: does not generate a foreign key constraint template<\/td>\n<\/tr>\n
prefix_in<\/td>varchar2<\/td>Yes<\/td>null<\/td>prefix for all object names (named by entities_in)<\/td>\n<\/tr>\n
in_apex_in<\/td>boolean<\/td>Yes<\/td>false<\/td>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
\nfalse: always use pseudo column USER  to populate created_by and changed_by <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
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.<\/p>\n\n\n\n
4. Understand the Basics of the oddgen PL\/SQL Interface<\/h2>\n\n\n\n
When selecting a database connection, the oddgen extension searches\u00a0the data dictionary for PL\/SQL packages implementing\u00a0the oddgen PL\/SQL interface. Basically, it looks\u00a0for a package functions with the following signature:<\/p>\n\n\n\n
FUNCTION generate(in_object_type IN VARCHAR2,\n                  in_object_name IN VARCHAR2,\n                  in_params      IN t_param) RETURN CLOB;<\/pre>\n\n\n\n
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\u00a0parameters to the generator. The data type t_param is an associative array and\u00a0based on\u00a0the following definition:<\/p>\n\n\n\n
SUBTYPE string_type IS VARCHAR2(1000 CHAR);\nSUBTYPE param_type IS VARCHAR2(60 CHAR);\nTYPE t_param IS TABLE OF string_type INDEX BY param_type;\n<\/pre>\n\n\n\n
Through in_params we may pass an unlimited number of key-value pairs to an oddgen generator.<\/p>\n\n\n\n
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”:<\/p>\n\n\n\n
\"oddgen_dropall_generator\"<\/a><\/figure>\n\n\n\n
The “Dropall” node is selected and in the status bar, its\u00a0description is displayed. Under this node you find the object types “Indexes” and “Tables” but also an artificial object type\u00a0named “All”. Under the object type nodes, you find the list of all associated object names. This structure supports the following features:<\/p>\n\n\n\n
    \n
  1. Generate code through a simple double-click on an object name node<\/li>\n\n\n\n
  2. Select multiple object name nodes of the same object type to generate code via context-menu<\/li>\n\n\n\n
  3. Show a dialog via the context menu for selected object name nodes to change generator parameters<\/li>\n<\/ol>\n\n\n\n
    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\u00a0ignore these\u00a0parameters in the generator implementation.<\/p>\n\n\n\n
    See the oddgen PL\/SQL interface<\/a> documentation on oddgen.org<\/a> if you are interested in the details.<\/p>\n\n\n\n
    For the next steps, it’s just important to know that we have to define the default behaviour of a generator and that a\u00a0generator provides some information for the GUI only.<\/p>\n\n\n\n
    5. Write the Wrapper<\/h2>\n\n\n\n
    The following screenshot shows our generator in SQL Developer after selecting “Generate…” from the context menu on the node “Snippet”:<\/p>\n\n\n\n
    \"oddgen_table_ddl_generator\"<\/a><\/figure>\n\n\n\n
    The package specification for this generator looks as follows:<\/p>\n\n\n\n
    Package Specification<\/span><\/path><\/path><\/svg><\/span>
    CREATE OR REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>gen_table_ddl_oddgen_wrapper<\/span> <\/span>IS<\/span><\/span>\n   <\/span>SUBTYPE<\/span> string_type <\/span>IS<\/span> <\/span>VARCHAR2<\/span>(<\/span>1000<\/span> <\/span>CHAR<\/span>);<\/span><\/span>\n   <\/span>SUBTYPE<\/span> param_type <\/span>IS<\/span> <\/span>VARCHAR2<\/span>(<\/span>60<\/span> <\/span>CHAR<\/span>);<\/span><\/span>\n   <\/span>TYPE<\/span> t_string <\/span>IS<\/span> <\/span>TABLE<\/span> <\/span>OF<\/span> string_type;<\/span><\/span>\n   <\/span>TYPE<\/span> t_param <\/span>IS<\/span> <\/span>TABLE<\/span> <\/span>OF<\/span> string_type <\/span>INDEX BY<\/span> param_type;<\/span><\/span>\n   <\/span>TYPE<\/span> t_lov <\/span>IS<\/span> <\/span>TABLE<\/span> <\/span>OF<\/span> t_string <\/span>INDEX BY<\/span> param_type;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_name<\/span> <\/span>RETURN<\/span> <\/span>VARCHAR2<\/span>;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_description<\/span> <\/span>RETURN<\/span> <\/span>VARCHAR2<\/span>;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_object_types<\/span> <\/span>RETURN<\/span> t_string;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_object_names<\/span>(in_object_type <\/span>IN<\/span> <\/span>VARCHAR2<\/span>) <\/span>RETURN<\/span> t_string;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_params<\/span> <\/span>RETURN<\/span> t_param;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_ordered_params<\/span> <\/span>RETURN<\/span> t_string;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_lov<\/span> <\/span>RETURN<\/span> t_lov;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>generate<\/span>(in_object_type <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                     in_object_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                     in_params      <\/span>IN<\/span> t_param) <\/span>RETURN<\/span> <\/span>CLOB<\/span>;<\/span><\/span>\nEND<\/span> gen_table_ddl_oddgen_wrapper;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    I’m going to explain\u00a0some\u00a0parts of the wrapper implementation based on the package body for this oddgen wrapper:<\/p>\n\n\n\n
    Package Body<\/span><\/path><\/path><\/svg><\/span>
    CREATE OR REPLACE<\/span> <\/span>PACKAGE BODY<\/span> <\/span>gen_table_ddl_oddgen_wrapper<\/span> <\/span>IS<\/span><\/span>\n   co_entity   <\/span>CONSTANT<\/span> param_type := <\/span>'Entity name (singular, for PK column)'<\/span>;<\/span><\/span>\n   co_entities <\/span>CONSTANT<\/span> param_type := <\/span>'Entity name (plural, for object names)'<\/span>;<\/span><\/span>\n   co_add_fky  <\/span>CONSTANT<\/span> param_type := <\/span>'Add foreign key?'<\/span>;<\/span><\/span>\n   co_prefix   <\/span>CONSTANT<\/span> param_type := <\/span>'Object prefix'<\/span>;<\/span><\/span>\n   co_in_apex  <\/span>CONSTANT<\/span> param_type := <\/span>'Data populated through APEX?'<\/span>;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_name<\/span> <\/span>RETURN<\/span> <\/span>VARCHAR2<\/span> <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>RETURN<\/span> <\/span>'Table DDL snippet'<\/span>;<\/span><\/span>\n   <\/span>END<\/span> get_name;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_description<\/span> <\/span>RETURN<\/span> <\/span>VARCHAR2<\/span> <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>RETURN<\/span> <\/span>'Steven Feuerstein''s starting point, from which he adds entity-specific columns, additional foreign keys, etc.'<\/span>;<\/span><\/span>\n   <\/span>END<\/span> get_description;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_object_types<\/span> <\/span>RETURN<\/span> t_string <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>RETURN<\/span> NEW t_string(<\/span>'TABLE'<\/span>);<\/span><\/span>\n   <\/span>END<\/span> get_object_types;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_object_names<\/span>(in_object_type <\/span>IN<\/span> <\/span>VARCHAR2<\/span>) <\/span>RETURN<\/span> t_string <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>RETURN<\/span> NEW t_string(<\/span>'Snippet'<\/span>);<\/span><\/span>\n   <\/span>END<\/span> get_object_names;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_params<\/span> <\/span>RETURN<\/span> t_param <\/span>IS<\/span><\/span>\n      <\/span>l_params<\/span> t_param;<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>l_params<\/span>(co_entity) := <\/span>'employee'<\/span>;<\/span><\/span>\n      <\/span>l_params<\/span>(co_entities) := <\/span>NULL<\/span>;<\/span><\/span>\n      <\/span>l_params<\/span>(co_add_fky) := <\/span>'Yes'<\/span>;<\/span><\/span>\n      <\/span>l_params<\/span>(co_prefix) := <\/span>NULL<\/span>;<\/span><\/span>\n      <\/span>l_params<\/span>(co_in_apex) := <\/span>'No'<\/span>;<\/span><\/span>\n      <\/span>RETURN<\/span> <\/span>l_params<\/span>;<\/span><\/span>\n   <\/span>END<\/span> get_params;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_ordered_params<\/span> <\/span>RETURN<\/span> t_string <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>RETURN<\/span> NEW t_string(co_entity, co_entities, co_add_fky, co_prefix);<\/span><\/span>\n   <\/span>END<\/span> get_ordered_params;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_lov<\/span> <\/span>RETURN<\/span> t_lov <\/span>IS<\/span><\/span>\n      <\/span>l_lov<\/span> t_lov;<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>l_lov<\/span>(co_add_fky) := NEW t_string(<\/span>'Yes'<\/span>, <\/span>'No'<\/span>);<\/span><\/span>\n      <\/span>l_lov<\/span>(co_in_apex) := NEW t_string(<\/span>'Yes'<\/span>, <\/span>'No'<\/span>);<\/span><\/span>\n      <\/span>RETURN<\/span> <\/span>l_lov<\/span>;<\/span><\/span>\n   <\/span>END<\/span> get_lov;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>generate<\/span>(in_object_type <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                     in_object_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                     in_params      <\/span>IN<\/span> t_param) <\/span>RETURN<\/span> <\/span>CLOB<\/span> <\/span>IS<\/span><\/span>\n      <\/span>l_lines<\/span>    sys.<\/span>dbms_output.<\/span>chararr<\/span>;<\/span><\/span>\n      <\/span>l_numlines<\/span> <\/span>INTEGER<\/span> := <\/span>10<\/span>; <\/span>-- buffer size<\/span><\/span>\n      <\/span>l_result<\/span>   <\/span>CLOB<\/span>;<\/span><\/span>\n   <\/span><\/span>\n      <\/span>PROCEDURE<\/span> <\/span>enable_output<\/span> <\/span>IS<\/span><\/span>\n      <\/span>BEGIN<\/span><\/span>\n         sys.<\/span>dbms_output.<\/span>enable<\/span>(buffer_size => <\/span>NULL<\/span>); <\/span>-- unlimited size<\/span><\/span>\n      <\/span>END<\/span> enable_output;<\/span><\/span>\n   <\/span><\/span>\n      <\/span>PROCEDURE<\/span> <\/span>disable_output<\/span> <\/span>IS<\/span><\/span>\n      <\/span>BEGIN<\/span><\/span>\n         sys.<\/span>dbms_output.<\/span>disable<\/span>;<\/span><\/span>\n      <\/span>END<\/span> disable_output;<\/span><\/span>\n   <\/span><\/span>\n      <\/span>PROCEDURE<\/span> <\/span>call_generator<\/span> <\/span>IS<\/span><\/span>\n      <\/span>BEGIN<\/span><\/span>\n         gen_table_ddl(entity_in   => in_params(co_entity),<\/span><\/span>\n                       entities_in => in_params(co_entities),<\/span><\/span>\n                       add_fky_in  => <\/span>CASE<\/span><\/span>\n                                         <\/span>WHEN<\/span> in_params(co_add_fky) = <\/span>'Yes'<\/span> <\/span>THEN<\/span><\/span>\n                                          <\/span>TRUE<\/span><\/span>\n                                         <\/span>ELSE<\/span><\/span>\n                                          <\/span>FALSE<\/span><\/span>\n                                      <\/span>END<\/span>,<\/span><\/span>\n                       prefix_in   => in_params(co_prefix),<\/span><\/span>\n                       in_apex_in  => <\/span>CASE<\/span><\/span>\n                                         <\/span>WHEN<\/span> in_params(co_in_apex) = <\/span>'Yes'<\/span> <\/span>THEN<\/span><\/span>\n                                          <\/span>TRUE<\/span><\/span>\n                                         <\/span>ELSE<\/span><\/span>\n                                          <\/span>FALSE<\/span><\/span>\n                                      <\/span>END<\/span>);<\/span><\/span>\n      <\/span>END<\/span> call_generator;<\/span><\/span>\n   <\/span><\/span>\n      <\/span>PROCEDURE<\/span> <\/span>copy_dbms_output_to_result<\/span> <\/span>IS<\/span><\/span>\n      <\/span>BEGIN<\/span><\/span>\n         sys.<\/span>dbms_lob.<\/span>createtemporary<\/span>(<\/span>l_result<\/span>, <\/span>TRUE<\/span>);<\/span><\/span>\n         <<read_dbms_output_into_buffer>><\/span><\/span>\n         <\/span>WHILE<\/span> <\/span>l_numlines<\/span> > <\/span>0<\/span><\/span>\n         <\/span>LOOP<\/span><\/span>\n            sys.<\/span>dbms_output.<\/span>get_lines<\/span>(<\/span>l_lines<\/span>, <\/span>l_numlines<\/span>);<\/span><\/span>\n            <<copy_buffer_to_clob>><\/span><\/span>\n            <\/span>FOR<\/span> i <\/span>IN<\/span> <\/span>1<\/span> .. <\/span>l_numlines<\/span><\/span>\n            <\/span>LOOP<\/span><\/span>\n               sys.<\/span>dbms_lob.<\/span>append<\/span>(<\/span>l_result<\/span>, <\/span>l_lines<\/span>(i) || <\/span>chr<\/span>(<\/span>10<\/span>));<\/span><\/span>\n            <\/span>END LOOP<\/span> copy_buffer_to_clob;<\/span><\/span>\n         <\/span>END LOOP<\/span> read_dbms_output_into_buffer;<\/span><\/span>\n      <\/span>END<\/span> copy_dbms_output_to_result;<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      enable_output;<\/span><\/span>\n      call_generator;<\/span><\/span>\n      copy_dbms_output_to_result;<\/span><\/span>\n      disable_output;<\/span><\/span>\n      <\/span>RETURN<\/span> <\/span>l_result<\/span>;<\/span><\/span>\n   <\/span>END<\/span> generate;<\/span><\/span>\nEND<\/span> gen_table_ddl_oddgen_wrapper;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    On lines 1 to 6 constants for every parameter are defined. The values are used as labels in the GUI.<\/p>\n\n\n\n
    The function get_name (line 10) defines the name used in the GUI for this generator.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    The function get_object_types (line 20) defines the valid object types.\u00a0We’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.<\/p>\n\n\n\n
    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”.<\/p>\n\n\n\n
    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\u00a0node “Snippet”. So, by default, a table named “employees” with a foreign key template and triggers for non-APEX usage is generated.<\/p>\n\n\n\n
    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\u00a0associative array indexed by a string. The default order by name is not very intuitive in this case.<\/p>\n\n\n\n
    The function get_lovs \u00a0(lines 47-48) defines the list of values per input parameter. We use “Yes” and “No” for the boolean parameters\u00a0co_add_fky and\u00a0co_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\u00a0the user, it does not matter that technically no boolean parameters are used.<\/p>\n\n\n\n
    The function generates (lines 103-107) defines the steps to produce the generated code. Each step is represented by a nested\u00a0procedure call:<\/p>\n\n\n\n
      \n
    • enable output<\/strong> – enables the dbms_output with unlimited buffer size<\/li>\n\n\n\n
    • call_generator<\/strong> – calls the original generator code using the parameters passed by oddgen<\/li>\n\n\n\n
    • copy_dbms_output_to_result<\/strong> – copies the output of the original generator into the result CLOB<\/li>\n\n\n\n
    • disable_output<\/strong> – disables dbms_output<\/li>\n<\/ul>\n\n\n\n
      Finally, the generated code is returned as CLOB.<\/p>\n\n\n\n
      6. Grant Access<\/h2>\n\n\n\n
      To ensure that the generator is available for every user connecting to the\u00a0instance, we have to grant execute rights on\u00a0the PL\/SQL wrapper package. Granting the package to the public is probably the easiest way.<\/p>\n\n\n\n
      Grant<\/span><\/path><\/path><\/svg><\/span>
      GRANT<\/span> <\/span>EXECUTE<\/span> <\/span>ON<\/span> gen_table_ddl_oddgen_wrapper <\/span>TO<\/span> PUBLIC;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      7. Run in SQL Developer<\/h2>\n\n\n\n
      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\u00a0to be used in an APEX application.<\/p>\n\n\n\n