Tutorial 1 – Minimal 1:1 View Generator

In this tutorial we build a generator which takes a table or a view as input and generates a 1:1 view. Here’s the expected example output for table emp:

1. Install extension

Launch SQL Developer and install the oddgen for SQL Developer extension as described in the Download section.

2. Create user

In order to create tables, views and PL/SQL packages we need a new Oracle user ogdemo.

Connect to a Oracle database (version 9.2. or higher) and execute the following SQL statements as a DBA user:

The grant on line 9 gives ogdemo the permission to access other data via invoker rights. This grant is necessary in version 12 but will fail in older versions.

3. Create tables

Execute the following statements as user ogdemo to create the most famous tables in the Oracle world:

Load the initial data with the following statements. However, they are not required for this tutorial.

4. Create generator

A oddgen database server generator is implemented as a PL/SQL package. The function generate is mandatory, all other functions are optional (we use in this tutorial the deprecated signature, which does not depend on oddgen types) . Create the package specification as user ogdemo.

Now we have to implement this function. For complex generators it’s recommended to use a template engine such as FTLDB or tePLSQL for better readability and maintainability. But for simple generators, plain PL/SQL is good enough. Install also the package body as user ogdemo:

5. Test

Let’s run the generator in SQL*Plus or in a SQL Developer worksheet as script. The following SQL

should produce this result:

The result looks as expected. But a SELECT * to get all columns is not always wanted. We look into this issue in the next tutorial.

6. Run in SQL Developer

  • Start Oracle SQL Developer and select Generators from the view menu.
  • Select the ogdemo connection from the connection combobox.
  • Open the node OGDEMO.MINIMAL and open its child Tables.
  • Double-click on the table EMP node to generate to a new SQL Developer worksheet.
  • The result is presented in a new SQL Developer worksheet.

7. Wrap-up

Single Function

Implement the PL/SQL package function generate to register a generator in SQL Developer.

Double-click to Generate

Double-click on the object name node to generate code to a new SQL Developer worksheet.


See tutorial 2 for an example implementing the complete PL/SQL interface.