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:

The expected generator output for table emp with the parameters “Select * ?” = “No” and the “View Suffix” = “_v” and “Ordered Columns?” = “No”  is:

The expected generator output for table emp with the parameters “Select * ?” = “No” and the “View Suffix” = “_v” and “Ordered Columns?” = “Yes”  is:

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:

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.

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:

tut2-s02-before

tut2-s02-before-2

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

and in the package body

After installing the amended package specification and body the generate dialog and the navigator tree look as follows:

tut2-s02-after

tut2-s02-after-2

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.

After installing the package specification and package body, the navigator tree looks as follows:

tut2-s03-after

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.

After installing the new package specification and body the navigator tree looks as follows:

tut2-s04-after

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.

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.

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:

blank

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.

After installing the new package specification and body the generate dialog looks as follows:

blank

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.

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.

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:

blank

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.

After installing the new package specification and body you may run the following in the SQL worksheet:

and you get this result:

10. Generate from user HR

To run the generator from any user in the database instance we have to grant execute rights as follows:

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.

blank

 

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.