Tutorial 2 – Extended 1:1 View Generator (PL/SQL)
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. New PL/SQL generator
To generate an initial PL/SQL package, select PL/SQL generator from the New… context menu of an oddgen node.
In the generator dialog enter extended_view for package name, check the Generate files? option and choose an output directory for the generated scripts and press the Generate to worksheet button.
Highlight the line containing “install.sql” and run it as script after connecting to user ogdemo.
Now you have installed a database server generator implementing the full PL/SQL interface. It will be visible in the Generators window after a pressing refresh.
2. Replace generate function
Open the file extended_view.pkb generated in the previous step in SQL developer. Replace the generate function with the highlighted code. Install the amended generator. It will produce the same result as in tutorial 1, beside some additional whitespace.
|
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 |
-- -- generate -- FUNCTION generate( in_node IN oddgen_types.r_node_type ) 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 oddgen_types.key_type; l_column_names oddgen_types.key_type; l_table_name oddgen_types.key_type; BEGIN -- prepare placeholders l_column_names := '*'; l_table_name := lower(regexp_substr(in_node.id, '[^\.]+', 1, 2)); 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 189 to 192 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 203 to 205.
3. Change name and description
After step 2 the navigator tree look as follows:
To change the name and description of the generator, you just have to change the implementation of the get_name and get_description functions.
|
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
-- -- get_name -- FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; -- -- get_description -- 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; |
After installing the amended package body the navigator tree looks better:
4. 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 change the implementation of get_nodes function to override the behaviour generated by default.
Remove the highlighted 4 lines from the code to suppress views to appear in the navigator tree.
|
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 |
-- -- get_nodes -- FUNCTION get_nodes( in_parent_node_id IN oddgen_types.key_type DEFAULT NULL ) RETURN oddgen_types.t_node_type IS t_nodes oddgen_types.t_node_type; -- PROCEDURE add_node ( in_id IN oddgen_types.key_type, in_parent_id IN oddgen_types.key_type, in_leaf IN BOOLEAN ) IS l_node oddgen_types.r_node_type; BEGIN l_node.id := in_id; l_node.parent_id := in_parent_id; l_node.leaf := in_leaf; l_node.generatable := TRUE; l_node.multiselectable := TRUE; l_node.params(co_p1) := 'Yes'; l_node.params(co_p2) := 'Value 1'; l_node.params(co_p3) := 'Some value'; t_nodes.extend; t_nodes(t_nodes.count) := l_node; END add_node; BEGIN t_nodes := oddgen_types.t_node_type(); IF in_parent_node_id IS NULL THEN -- object types add_node( in_id => 'TABLE', in_parent_id => NULL, in_leaf => FALSE ); add_node( in_id => 'VIEW', in_parent_id => NULL, in_leaf => FALSE ); ELSE -- object names <<nodes>> FOR r IN ( SELECT object_name FROM user_objects WHERE object_type = in_parent_node_id AND generated = 'N' ) LOOP add_node( in_id => in_parent_node_id || '.' || r.object_name, in_parent_id => in_parent_node_id, in_leaf => TRUE ); END LOOP nodes; END IF; RETURN t_nodes; END get_nodes; |
After applying the changes, 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.
5. Add parameters with list-of-values (LOVs)
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 provide the parameters in the get_nodes function. The parameters in the associative array are sorted by name. We override this sort order in the get_ordered_params function. In the get_lov function we ensure that we cannot select “Select *?” and “Order Columns” simultaneously.
The changes lines are highlighted below.
|
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 |
-- -- parameter names used also as labels in the GUI -- co_select_star CONSTANT oddgen_types.key_type := 'Select * ?'; co_view_suffix CONSTANT oddgen_types.key_type := 'View suffix'; co_order_columns CONSTANT oddgen_types.key_type := 'Order columns?'; -- -- get_name -- FUNCTION get_name RETURN VARCHAR2 IS BEGIN RETURN 'Extended 1:1 View Generator'; END get_name; -- -- get_description -- 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; -- -- get_folders -- FUNCTION get_folders RETURN oddgen_types.t_value_type IS BEGIN RETURN NEW oddgen_types.t_value_type('Database Server Generators'); END get_folders; -- -- get_help -- FUNCTION get_help RETURN CLOB IS BEGIN RETURN '<p>Not yet available.</p>'; END get_help; -- -- get_nodes -- FUNCTION get_nodes( in_parent_node_id IN oddgen_types.key_type DEFAULT NULL ) RETURN oddgen_types.t_node_type IS t_nodes oddgen_types.t_node_type; -- PROCEDURE add_node ( in_id IN oddgen_types.key_type, in_parent_id IN oddgen_types.key_type, in_leaf IN BOOLEAN ) IS l_node oddgen_types.r_node_type; BEGIN l_node.id := in_id; l_node.parent_id := in_parent_id; l_node.leaf := in_leaf; l_node.generatable := TRUE; l_node.multiselectable := TRUE; l_node.params(co_select_star) := 'No'; l_node.params(co_view_suffix) := '_v'; l_node.params(co_order_columns) := 'No'; t_nodes.extend; t_nodes(t_nodes.count) := l_node; END add_node; BEGIN t_nodes := oddgen_types.t_node_type(); IF in_parent_node_id IS NULL THEN -- object types add_node( in_id => 'TABLE', in_parent_id => NULL, in_leaf => FALSE ); ELSE -- object names <<nodes>> FOR r IN ( SELECT object_name FROM user_objects WHERE object_type = in_parent_node_id AND generated = 'N' ) LOOP add_node( in_id => in_parent_node_id || '.' || r.object_name, in_parent_id => in_parent_node_id, in_leaf => TRUE ); END LOOP nodes; END IF; RETURN t_nodes; END get_nodes; -- -- get_ordered_params -- FUNCTION get_ordered_params RETURN oddgen_types.t_value_type IS BEGIN RETURN NEW oddgen_types.t_value_type( co_select_star, co_view_suffix, co_order_columns ); END get_ordered_params; -- -- get_lov -- FUNCTION get_lov( in_params IN oddgen_types.t_param_type, in_nodes IN oddgen_types.t_node_type ) RETURN oddgen_types.t_lov_type IS l_lov oddgen_types.t_lov_type; BEGIN IF in_params(co_select_star) = 'Yes' THEN l_lov(co_order_columns) := NEW oddgen_types.t_value_type('No'); ELSE l_lov(co_order_columns) := NEW oddgen_types.t_value_type('Yes', 'No'); END IF; IF in_params(co_order_columns) = 'Yes' THEN l_lov(co_select_star) := NEW oddgen_types.t_value_type('No'); ELSE l_lov(co_select_star) := NEW oddgen_types.t_value_type('Yes', 'No'); END IF; RETURN l_lov; END get_lov; -- -- get_param_states -- FUNCTION get_param_states( in_params IN oddgen_types.t_param_type, in_nodes IN oddgen_types.t_node_type ) RETURN oddgen_types.t_param_type IS l_param_states oddgen_types.t_param_type; BEGIN RETURN l_param_states; END get_param_states; |
We use the constants for each index of the associative array containing parameter values. The constants are not necessary, but they simplify the maintenance and the access from various places in the generator code.
After installing the amended code, the generate dialog shows the parameters in the right oder and behaves as intended.
6. Use Parameters
We added three parameters in the previous step. To use them we have to revise the generate function. On the lines 204, 206 and 214 the parameter settings are used to produce the desired result.
|
186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 |
-- -- generate -- FUNCTION generate( in_node IN oddgen_types.r_node_type ) 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 oddgen_types.key_type; l_column_names oddgen_types.key_type; l_table_name oddgen_types.key_type; BEGIN -- prepare placeholder table_name l_table_name := lower(regexp_substr(in_node.id, '[^\.]+', 1, 2)); -- prepare place_holder view_name l_view_name := l_table_name || in_node.params(co_view_suffix); -- prepare placeholder column_names IF in_node.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(l_table_name) ORDER BY CASE WHEN in_node.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; -- 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; / |
7. Add separator
When generating more than one view you might want to separate the result by a new line or a comment. The following change in the generate_separator function produces a single line comment between the generator results.
|
168 169 170 171 172 173 174 |
-- -- generate_separator -- FUNCTION generate_separator RETURN VARCHAR2 IS BEGIN RETURN '--' || chr(10); END generate_separator; |
8. Generate from SQL
To run the extended_view generator from SQL you need to write a wrapper function to deal with the PL/SQL types. The wrapper can be incorporated into the extended_view package or in the SQL producing the generator results. Here’s an example of the latter (requires Oracle 12.1 or higher):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SET LONG 500 WITH FUNCTION gen( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2, in_select_star IN VARCHAR2, in_view_suffix IN VARCHAR2, in_order_columns IN VARCHAR2 ) RETURN CLOB IS r_node oddgen_types.r_node_type; BEGIN r_node.id := in_object_type || '.' || in_object_name; r_node.parent_id := in_object_type; r_node.params('Select * ?') := in_select_star; r_node.params('View suffix') := in_view_suffix; r_node.params('Order columns?') := in_order_columns; RETURN extended_view.generate(in_node => r_node); END; SELECT gen(object_type, object_name, 'No', '_view', 'Yes') AS result FROM user_objects WHERE object_type = 'TABLE' AND generated = 'N' ORDER BY object_name / |
Run this SQL in worksheet to produce 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; |
9. Generate from user HR
To run the generator from any user in the database instance we have to grant execute rights as follows:
|
1 2 |
GRANT EXECUTE ON oddgen_types TO PUBLIC; 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.
Please note that in this example just the Tables node has been selected. oddgen for SQL Developer traverses the tree and produces a result for every relevant node. However, in that case the tables are sorted according the get_nodes result and the query in this function does not sort the result.