Tutorial 2 – Extended 1:1 View Generator (PL/SQL, v0.2.0, deprecated)
In this tutorial we extend the 1:1 view generator built in tutorial 1. The goal is to use most optional functions of the oddgen PL/SQL interface. We will change the default name and description and define the following parameters to configure the generation process:
- Select * ?
Control the select list in the view. Use star (*) if the parameter is set to “Yes” otherwise list all columns of the table explicitely. Default is “No”. - View Suffix
Define the suffix of the view to distinguish the name from the table. Default is “_v”. - Order Columns?
If the columns are to be listed explicitly, then the columns may be ordered. If the parameter is set to “Yes” then the columns are ordered by name, otherwise by the column id (the order according a describe table). Default is “No”.
The expected generator output for table emp with the parameters “Select * ?” = “Yes” and the “View Suffix” = “_view” is:
|
1 2 3 |
CREATE OR REPLACE VIEW emp_view AS SELECT * FROM emp; |
The expected generator output for table emp with the parameters “Select * ?” = “No” and the “View Suffix” = “_v” and “Ordered Columns?” = “No” is:
|
1 2 3 |
CREATE OR REPLACE VIEW emp_v AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp; |
The expected generator output for table emp with the parameters “Select * ?” = “No” and the “View Suffix” = “_v” and “Ordered Columns?” = “Yes” is:
|
1 2 3 |
CREATE OR REPLACE VIEW emp_v AS SELECT comm, deptno, empno, ename, hiredate, job, mgr, sal FROM emp; |
1. Create initial package
Let’s use the minimal_view example created in the tutorial 1 as starting point in this tutorial. Here’s the package specification with a small extension:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB; END extended_view; / |
We renamed the package from minimal_view to extended_view and added string_type as you see on the highlighted line 3.
The generate function in the package body produces the same result as in tutorial 1, beside some additional whitespace. The amended code is now ready for some extensions in this tutorial.
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB IS l_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholders l_column_names := '*'; l_table_name := lower(in_object_name); l_view_name := l_table_name || '_v'; -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; END extended_view; / |
In line 6 to 9 we added a generator template. In this tutorial a home grown template engine is used, basically it’s based on a series of replace statement, as you can see on line 20 to 22.
Connect to user ogdemo and install the package specification and package body.
2. Add name and description
After step 1 the generate dialog and the navigator tree look as follows:
To change the default name and description of the generator, you just have to implement the get_name and get_description functions in the package specification
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); FUNCTION get_name RETURN VARCHAR2; FUNCTION get_description RETURN VARCHAR2; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB; END extended_view; / |
and in the package body
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; FUNCTION get_description RETURN VARCHAR2 IS BEGIN RETURN 'Generates a 1:1 view based on an existing ' || 'table and various generator parameters.'; END get_description; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB IS l_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholders l_column_names := '*'; l_table_name := lower(in_object_name); l_view_name := l_table_name || '_v'; -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; END extended_view; / |
After installing the amended package specification and body the generate dialog and the navigator tree look as follows:
3. Use tables only
Let’s assume that views should be generated for tables only. So we do not want to see the Views node in the navigator tree. To achieve this we have to implement the get_object_types function to override the default behaviour.
The changes in the package specification and package body are highlighted.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); TYPE t_string IS TABLE OF string_type; FUNCTION get_name RETURN VARCHAR2; FUNCTION get_description RETURN VARCHAR2; FUNCTION get_object_types RETURN t_string; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB; END extended_view; / |
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; FUNCTION get_description RETURN VARCHAR2 IS BEGIN RETURN 'Generates a 1:1 view based on an existing ' || 'table and various generator parameters.'; END get_description; FUNCTION get_object_types RETURN t_string IS BEGIN RETURN NEW t_string('TABLE'); END get_object_types; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB IS l_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholders l_column_names := '*'; l_table_name := lower(in_object_name); l_view_name := l_table_name || '_v'; -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; END extended_view; / |
After installing the package specification and package body, the navigator tree looks as follows:
You probably wonder why the node is displayed as Tables instead of TABLE as defined in the return value of get_object_types. The reason is that oddgen for SQL Developer knows about the default object types and displays a nicer representation while keeping the original type name for internal operations.
4. Initcap object names
If we want to change the object names displayed under the Tables node, we have to implement the get_object_names function. This function is called by oddgen for SQL Developer when a object type node is opened.
Here are the updated version of the package specification and body. The changes are highlighted.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); TYPE t_string IS TABLE OF string_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 generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB; END extended_view; / |
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; FUNCTION get_description RETURN VARCHAR2 IS BEGIN RETURN 'Generates a 1:1 view based on an existing ' || 'table and various generator parameters.'; 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 l_object_names t_string; BEGIN SELECT initcap(object_name) AS object_name BULK COLLECT INTO l_object_names FROM user_objects WHERE object_type = in_object_type AND generated = 'N' ORDER BY object_name; RETURN l_object_names; END get_object_names; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB IS l_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholders l_column_names := '*'; l_table_name := lower(in_object_name); l_view_name := l_table_name || '_v'; -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; END extended_view; |
After installing the new package specification and body the navigator tree looks as follows:
5. Add parameters
Now we want to add the following three parameters to configure the behaviour of the generator:
- Select * ?
Control the select list in the view. Use star (*) if the parameter is set to “Yes” otherwise list all columns of the table explicitely. Default is “No”. - View Suffix
Define the suffix of the view to distinguish the name from the table. Default is “_v”. - Order Columns?
If the columns are to be listed explicitly, then the columns may be ordered. If the parameter is set to “Yes” then the columns are ordered by name, otherwise by the column id (the order according a describe table). Default is “No”.
To accomplish that we have to implement the get_params function. Here is the updated package specification. The changes are highlighted.
|
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 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); TYPE t_string IS TABLE OF string_type; SUBTYPE param_type IS VARCHAR2(30 CHAR); TYPE t_param IS TABLE OF string_type 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 ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB; END extended_view; / |
The function get_params returns the list of parameters as an associative array. The structure supports an unbound number of parameters with default values. The default values are important to generate code without enforcing parameter entries.
The functionality becomes clearer if we look at the new version of the package body. The changes are highlighted.
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS -- parameter constants, used as labels in the generate dialog co_select_star CONSTANT string_type := 'Select * ?'; co_view_suffix CONSTANT string_type := 'View suffix'; co_order_columns CONSTANT string_type := 'Order columns?'; FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; FUNCTION get_description RETURN VARCHAR2 IS BEGIN RETURN 'Generates a 1:1 view based on an existing ' || 'table and various generator parameters.'; 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 l_object_names t_string; BEGIN SELECT initcap(object_name) AS object_name BULK COLLECT INTO l_object_names FROM user_objects WHERE object_type = in_object_type AND generated = 'N' ORDER BY object_name; RETURN l_object_names; END get_object_names; FUNCTION get_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param IS l_params t_param; BEGIN l_params(co_select_star) := 'No'; l_params(co_view_suffix) := '_v'; l_params(co_order_columns) := 'No'; RETURN l_params; END get_params; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB IS l_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholders l_column_names := '*'; l_table_name := lower(in_object_name); l_view_name := l_table_name || '_v'; -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; END extended_view; / |
We use the constants defined at the top of the package body to access the index of the associative array. The constants are not necessary, but they simplify the maintenance and the access from various places in the generator code.
in_object_type and in_object_name could be used to define the list of parameters dynamically. In this case we do not need this functionality.
After installing the new package specification and body the generate dialog looks as follows:
6. Order parameters
By default, parameters are ordered by name. We implement the function get_ordered_params to override the default sort order.
Here are the amended package specification and body. The changes are highlighted.
|
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 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); TYPE t_string IS TABLE OF string_type; SUBTYPE param_type IS VARCHAR2(30 CHAR); TYPE t_param IS TABLE OF string_type 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 ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param; FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB; END extended_view; / |
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS -- parameter constants, used as labels in the generate dialog co_select_star CONSTANT string_type := 'Select * ?'; co_view_suffix CONSTANT string_type := 'View suffix'; co_order_columns CONSTANT string_type := 'Order columns?'; FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; FUNCTION get_description RETURN VARCHAR2 IS BEGIN RETURN 'Generates a 1:1 view based on an existing ' || 'table and various generator parameters.'; 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 l_object_names t_string; BEGIN SELECT initcap(object_name) AS object_name BULK COLLECT INTO l_object_names FROM user_objects WHERE object_type = in_object_type AND generated = 'N' ORDER BY object_name; RETURN l_object_names; END get_object_names; FUNCTION get_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param IS l_params t_param; BEGIN l_params(co_select_star) := 'No'; l_params(co_view_suffix) := '_v'; l_params(co_order_columns) := 'No'; RETURN l_params; END get_params; FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string IS BEGIN RETURN NEW t_string(co_select_star, co_view_suffix, co_order_columns); END get_ordered_params; FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB IS l_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholders l_column_names := '*'; l_table_name := lower(in_object_name); l_view_name := l_table_name || '_v'; -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; END extended_view; / |
After installing the new package specification and body the generate dialog looks as follows:
The generate dialog deals now with the dependencies between Select * ? and Order Columns?.
7. Use Parameters
We added three additional parameters in the previous step. To use them we have to pass the parameters to the generate function.
Here are the amended package specification and body. The changes are highlighted.
|
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 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); TYPE t_string IS TABLE OF string_type; SUBTYPE param_type IS VARCHAR2(30 CHAR); TYPE t_param IS TABLE OF string_type 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 ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param; FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string; FUNCTION generate( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN CLOB; END extended_view; / |
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS -- parameter constants, used as labels in the generate dialog co_select_star CONSTANT string_type := 'Select * ?'; co_view_suffix CONSTANT string_type := 'View suffix'; co_order_columns CONSTANT string_type := 'Order columns?'; FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; FUNCTION get_description RETURN VARCHAR2 IS BEGIN RETURN 'Generates a 1:1 view based on an existing ' || 'table and various generator parameters.'; 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 l_object_names t_string; BEGIN SELECT initcap(object_name) AS object_name BULK COLLECT INTO l_object_names FROM user_objects WHERE object_type = in_object_type AND generated = 'N' ORDER BY object_name; RETURN l_object_names; END get_object_names; FUNCTION get_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param IS l_params t_param; BEGIN l_params(co_select_star) := 'No'; l_params(co_view_suffix) := '_v'; l_params(co_order_columns) := 'No'; RETURN l_params; END get_params; FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string IS BEGIN RETURN NEW t_string(co_select_star, co_view_suffix, co_order_columns); END get_ordered_params; FUNCTION generate( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN CLOB IS l_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholder column_names IF in_params(co_select_star) = 'Yes' THEN l_column_names := '*'; ELSE FOR l_rec IN ( SELECT column_name FROM user_tab_columns WHERE table_name = upper(in_object_name) ORDER BY CASE WHEN in_params(co_order_columns) = 'Yes' THEN column_name ELSE to_char(column_id, '99999') END) LOOP IF l_column_names IS NOT NULL THEN l_column_names := l_column_names || ', '; END IF; l_column_names := l_column_names || lower(l_rec.column_name); END LOOP; END IF; -- prepare other placeholders l_table_name := lower(in_object_name); l_view_name := l_table_name || lower(in_params(co_view_suffix)); -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; END extended_view; / |
After installing the new package specification and body the generate function produces the results according the parameter settings.
8. Add list-of-values
Right now it possible to enter any value for the parameters Select * ? and Order Columns? even if Yes and No are valid only. Let’s use the get_lov function to define the list of valid values for these parameters.
Here are the amended package specification and body. The changes are highlighted.
|
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 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); TYPE t_string IS TABLE OF string_type; SUBTYPE param_type IS VARCHAR2(30 CHAR); 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 ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param; FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string; FUNCTION get_lov ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN t_lov; FUNCTION generate( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN CLOB; END extended_view; / |
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS -- parameter constants, used as labels in the generate dialog co_select_star CONSTANT string_type := 'Select * ?'; co_view_suffix CONSTANT string_type := 'View suffix'; co_order_columns CONSTANT string_type := 'Order columns?'; FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; FUNCTION get_description RETURN VARCHAR2 IS BEGIN RETURN 'Generates a 1:1 view based on an existing ' || 'table and various generator parameters.'; 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 l_object_names t_string; BEGIN SELECT initcap(object_name) AS object_name BULK COLLECT INTO l_object_names FROM user_objects WHERE object_type = in_object_type AND generated = 'N' ORDER BY object_name; RETURN l_object_names; END get_object_names; FUNCTION get_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param IS l_params t_param; BEGIN l_params(co_select_star) := 'No'; l_params(co_view_suffix) := '_v'; l_params(co_order_columns) := 'No'; RETURN l_params; END get_params; FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string IS BEGIN RETURN NEW t_string(co_select_star, co_view_suffix, co_order_columns); END get_ordered_params; FUNCTION get_lov ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN t_lov IS l_lov t_lov; BEGIN IF in_params(co_select_star) = 'Yes' THEN l_lov(co_order_columns) := NEW t_string('No'); ELSE l_lov(co_order_columns) := NEW t_string('Yes', 'No'); END IF; IF in_params(co_order_columns) = 'Yes' THEN l_lov(co_select_star) := NEW t_string('No'); ELSE l_lov(co_select_star) := NEW t_string('Yes', 'No'); END IF; 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_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholder column_names IF in_params(co_select_star) = 'Yes' THEN l_column_names := '*'; ELSE FOR l_rec IN ( SELECT column_name FROM user_tab_columns WHERE table_name = upper(in_object_name) ORDER BY CASE WHEN in_params(co_order_columns) = 'Yes' THEN column_name ELSE to_char(column_id, '99999') END) LOOP IF l_column_names IS NOT NULL THEN l_column_names := l_column_names || ', '; END IF; l_column_names := l_column_names || lower(l_rec.column_name); END LOOP; END IF; -- prepare other placeholders l_table_name := lower(in_object_name); l_view_name := l_table_name || lower(in_params(co_view_suffix)); -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; END extended_view; / |
The Parameters Select * ? and Order Columns? are not independent. If you select Yes for Select * ? it is irrelevant what you set for Order Columns? since no columns are listed. The function get_lov is called after a parameter change, hence we are able to set the list-of-values based on the current parameter values in the generate dialog.
After installing the new package specification and body the generate dialog looks as follows:
List-of-values with a boolean value pair are represented as a check box in the generate dialog.
9. Generate from SQL
To run the generator from plain SQL you need to write a wrapper function to deal with the PL/SQL types which are not known from SQL.
Here are the amended package specification and body. The changes are highlighted.
|
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 |
CREATE OR REPLACE PACKAGE extended_view AUTHID CURRENT_USER AS -- oddgen PL/SQL data types SUBTYPE string_type IS VARCHAR2(1000 CHAR); TYPE t_string IS TABLE OF string_type; SUBTYPE param_type IS VARCHAR2(30 CHAR); 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 ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param; FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string; FUNCTION get_lov ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN t_lov; FUNCTION generate( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN CLOB; FUNCTION generate( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_select_star IN VARCHAR2 DEFAULT 'No', in_view_suffix IN VARCHAR2 DEFAULT '_v', in_order_columns IN VARCHAR2 DEFAULT 'No' ) RETURN CLOB; END extended_view; / |
|
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 |
CREATE OR REPLACE PACKAGE BODY extended_view AS -- parameter constants, used as labels in the generate dialog co_select_star CONSTANT string_type := 'Select * ?'; co_view_suffix CONSTANT string_type := 'View suffix'; co_order_columns CONSTANT string_type := 'Order columns?'; FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; FUNCTION get_description RETURN VARCHAR2 IS BEGIN RETURN 'Generates a 1:1 view based on an existing ' || 'table and various generator parameters.'; 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 l_object_names t_string; BEGIN SELECT initcap(object_name) AS object_name BULK COLLECT INTO l_object_names FROM user_objects WHERE object_type = in_object_type AND generated = 'N' ORDER BY object_name; RETURN l_object_names; END get_object_names; FUNCTION get_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_param IS l_params t_param; BEGIN l_params(co_select_star) := 'No'; l_params(co_view_suffix) := '_v'; l_params(co_order_columns) := 'No'; RETURN l_params; END get_params; FUNCTION get_ordered_params ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN t_string IS BEGIN RETURN NEW t_string(co_select_star, co_view_suffix, co_order_columns); END get_ordered_params; FUNCTION get_lov ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_params IN t_param ) RETURN t_lov IS l_lov t_lov; BEGIN IF in_params(co_select_star) = 'Yes' THEN l_lov(co_order_columns) := NEW t_string('No'); ELSE l_lov(co_order_columns) := NEW t_string('Yes', 'No'); END IF; IF in_params(co_order_columns) = 'Yes' THEN l_lov(co_select_star) := NEW t_string('No'); ELSE l_lov(co_select_star) := NEW t_string('Yes', 'No'); END IF; 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_templ CLOB := 'CREATE OR REPLACE VIEW {view_name} AS SELECT $${column_names} FROM ${table_name};'; l_clob CLOB; l_view_name string_type; l_column_names string_type; l_table_name string_type; BEGIN -- prepare placeholder column_names IF in_params(co_select_star) = 'Yes' THEN l_column_names := '*'; ELSE FOR l_rec IN ( SELECT column_name FROM user_tab_columns WHERE table_name = upper(in_object_name) ORDER BY CASE WHEN in_params(co_order_columns) = 'Yes' THEN column_name ELSE to_char(column_id, '99999') END) LOOP IF l_column_names IS NOT NULL THEN l_column_names := l_column_names || ', '; END IF; l_column_names := l_column_names || lower(l_rec.column_name); END LOOP; END IF; -- prepare other placeholders l_table_name := lower(in_object_name); l_view_name := l_table_name || lower(in_params(co_view_suffix)); -- produce final clob, replace placeholder in template l_clob := REPLACE(l_templ, '${column_names}', l_column_names); l_clob := REPLACE(l_clob, '${view_name}', l_view_name); l_clob := REPLACE(l_clob, '${table_name}', l_table_name); RETURN l_clob; END generate; FUNCTION generate( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_select_star IN VARCHAR2 DEFAULT 'No', in_view_suffix IN VARCHAR2 DEFAULT '_v', in_order_columns IN VARCHAR2 DEFAULT 'No' ) RETURN CLOB IS l_params t_param; BEGIN l_params(co_select_star) := in_select_star; l_params(co_view_suffix) := in_view_suffix; l_params(co_order_columns) := in_order_columns; RETURN generate(in_object_type, in_object_name, l_params); END generate; END extended_view; / |
After installing the new package specification and body you may run the following in the SQL worksheet:
|
1 2 3 4 5 6 |
SET LONG 500 SELECT extended_view.generate(object_type, object_name, 'No', '_view', 'Yes') AS result FROM user_objects WHERE object_type = 'TABLE' AND generated = 'N' ORDER BY object_name; |
and you get this result:
|
1 2 3 4 5 6 7 8 9 |
RESULT -------------------------------------------------------------------------------- CREATE OR REPLACE VIEW dept_view AS SELECT deptno, dname, loc FROM dept; CREATE OR REPLACE VIEW emp_view AS SELECT comm, deptno, empno, ename, hiredate, job, mgr, sal FROM emp; |
10. Generate from user HR
To run the generator from any user in the database instance we have to grant execute rights as follows:
|
1 |
GRANT EXECUTE ON extended_view TO PUBLIC; |
Afterwards you may establish a connection to any user in the database such as the demo user HR and generate the views as shown in the next image.
11. Wrap-up
Override Default Behaviour
Implement optional functions of the oddgen PL/SQL interface to change the default behaviour.
Unlimited Parameters
You may define an unbound number of parameters via the get_params function. For each parameter a default value is defined to allow code generation without parameter entry.
List-of-Values
Use the get_lov function to define the list of valid values for parameters and to handle parameter interdependencies.
Share Generators
Grant execute rights to public to share your Database Server Generators within an Oracle database instance.