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:

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. New PL/SQL generator

To generate an initial PL/SQL package, select PL/SQL generator from the New… context menu of an oddgen node.

blank

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.

blank

Highlight the line containing “install.sql” and run it as script after connecting to user ogdemo.

blank

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.

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:

blank

To change the name and description of the generator, you just have to change the implementation of the get_name and get_description functions.

After installing the amended package body the navigator tree looks better:

blank

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.

After applying the changes, 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.

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.

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.

blank

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.

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.

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):

Run this SQL in worksheet to produce this result:

9. 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

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.

10. Wrap-up

Generator Template


Create a PL/SQL generator based on the “New…” template. This template implements the oddgen PL/SQL interface fully and makes it easier to change the behaviour according your needs.

Unlimited Parameters


You may define an unbound number of parameters in the get_nodes 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.