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 2 |
CREATE OR REPLACE VIEW emp_v AS SELECT * FROM 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:
|
1 2 3 4 5 6 7 8 9 |
CREATE USER ogdemo IDENTIFIED BY ogdemo DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, RESOURCE to ogdemo; GRANT CREATE VIEW to ogdemo; GRANT SELECT_CATALOG_ROLE to ogdemo; GRANT SELECT ANY DICTIONARY to ogdemo; GRANT INHERIT PRIVILEGES ON USER ogdemo TO PUBLIC; -- 12c only |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE dept ( deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2(14), loc VARCHAR2(13) ); CREATE TABLE emp ( empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept ); |
Load the initial data with the following statements. However, they are not required for this tutorial.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON'); INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, DATE '1980-12-17', 800, NULL, 20); INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, DATE '1981-02-20', 1600, 300, 30); INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, DATE '1981-02-22', 1250, 500, 30); INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, DATE '1981-04-02', 2975, NULL, 20); INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, DATE '1981-09-28', 1250, 1400, 30); INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, DATE '1981-05-01', 2850, NULL, 30); INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, DATE '1981-06-09', 2450, NULL, 10); INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, DATE '1987-04-19', 3000, NULL, 20); INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, DATE '1981-11-17', 5000, NULL, 10); INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, DATE '1981-09-08', 1500, 0, 30); INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, DATE '1987-05-23', 1100, NULL, 20); INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, DATE '1981-12-03', 950, NULL, 30); INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, DATE '1981-12-03', 3000, NULL, 20); INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, DATE '1982-01-23', 1300, NULL, 10); COMMIT; |
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.
|
1 2 3 4 5 6 7 |
CREATE OR REPLACE PACKAGE minimal_view AS FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB; END minimal_view; / |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE OR REPLACE PACKAGE BODY minimal_view AS FUNCTION generate ( in_object_type IN VARCHAR2, in_object_name IN VARCHAR2 ) RETURN CLOB IS l_clob CLOB; BEGIN l_clob := 'CREATE OR REPLACE VIEW ' || LOWER(in_object_name) || '_v AS ' || CHR(10) || ' SELECT * FROM ' || LOWER(in_object_name) || ';'; RETURN l_clob; END generate; END minimal_view; / |
5. Test
Let’s run the generator in SQL*Plus or in a SQL Developer worksheet as script. The following SQL
|
1 2 |
SELECT minimal_view.generate('TABLE', table_name) AS result FROM User_tables; |
should produce this result:
|
1 2 3 4 5 6 7 |
RESULT -------------------------------------------------------------------------------- CREATE OR REPLACE VIEW dept_v AS SELECT * FROM dept; CREATE OR REPLACE VIEW emp_v AS SELECT * FROM emp; |
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.
