Using the TABLE Operator with Locally Defined Types in PL/SQL

In Oracle 12c, the TABLE operator can now be used in PL/SQL with locally defined types. In previous releases, the TABLE operator would only work with locally defined types if they were used within pipelined table functions. The removal of this restriction means this functionality is available for use with associative arrays, as well as nested tables and varrays in PL/SQL.

◉ Setup

The examples in this article follow the same pattern. They populate a collection, then instantly select from the collection using the TABLE operator, and print out the contents. The code is merely to demonstrate the fact the local collection is accessible using the TABLE operator. It is not suggesting it is sensible to populate the collection then instantly select from it to process the data.

All examples use the EMP table from the SCOTT schema. If it is not present you can install the schema using the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script on the server, or build the tables in your local schema using the script below.

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

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,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

◉ Associative Array Example

This example uses an associative array based on a %ROWTYPE defined in the package specification.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER

PL/SQL procedure successfully completed.

SQL>

◉ Nested Table Example

This example uses a nested table based on a %ROWTYPE defined in the package specification.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF emp%ROWTYPE;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER

PL/SQL procedure successfully completed.

SQL>

◉ Varray Example

This example uses a varray based on a %ROWTYPE defined in the package specification.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS VARRAY(10) OF emp%ROWTYPE;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER

PL/SQL procedure successfully completed.

SQL>

◉ Supported Types

The previous examples all use a %ROWTYPE of a schema table for the collection type. It is also possible to use %TYPE, a scalar and a local record type.

The following example uses an associated array based on a %TYPE. Notice the column is referenced as COLUMN_VALUE.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF emp.empno%TYPE
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.column_value);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782
7839
7934

PL/SQL procedure successfully completed.

SQL>

The following example uses an associated array based on a scalar. Notice the column is referenced as COLUMN_VALUE.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.column_value);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782
7839
7934

PL/SQL procedure successfully completed.

SQL>

The following example uses an associated array based on a local record type.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_row IS RECORD (
    empno NUMBER(4),
    ename VARCHAR2(10)
  );

  TYPE t_tab IS TABLE OF t_row
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno, ename
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER

PL/SQL procedure successfully completed.

SQL>

◉ Location of Type Definition

For this new functionality to work, the type must be defined in package specification. The examples below show how alternate locations fail.

The following example moves the type definition to the package body, which results in a compilation failure.

CREATE OR REPLACE PACKAGE test_api AS
  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  TYPE t_tab IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/
Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY TEST_API:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/21    PL/SQL: SQL Statement ignored
16/28    PL/SQL: ORA-22905: cannot access rows from a non-nested table
              item

16/34    PLS-00382: expression is of wrong type
18/7     PL/SQL: Statement ignored
18/28    PLS-00364: loop index variable 'CUR_REC' use is invalid
SQL>

The following example attempts to use the functionality in an anonymous block, which results in an error.

DECLARE
  TYPE t_tab IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;

  l_tab1 t_tab;
BEGIN
  SELECT *
  BULK COLLECT INTO l_tab1
  FROM   emp
  WHERE  deptno = 10;

  DBMS_OUTPUT.put_line('Loop Through Collection');
  FOR cur_rec IN (SELECT *
                  FROM   TABLE(l_tab1))
  LOOP
    DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
  END LOOP;
END;
/
ERROR at line 14:
ORA-06550: line 14, column 32:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 26:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 13, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 26:
PLS-00364: loop index variable 'CUR_REC' use is invalid
ORA-06550: line 16, column 5:
PL/SQL: Statement ignored

SQL>

Continue reading

PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c

The PL/SQL language is a combination of two distinct parts:

◉ PL : Procedural Logic.
◉ SQL : Structured Query Language

The two are meshed together so well it is easy to forget this separation exists. One area where the separation does show is in the differing support data types. In previous database versions, binding of PL/SQL-only data types from JDBC, OCI, static and native dynamic SQL was a problem. Oracle 12c now supports the binding of additional PL/SQL-only data types to anonymous blocks, PL/SQL function calls in SQL, the TABLE operator in SQL and CALL statements.

There are some restrictions associated with this functionality.

◉ The PL/SQL-only data type must be a built-in type, or defined in a package specification.

◉ For associative arrays, only the INDEX BY PLS_INTEGER for us currently supported.

◉ PL/SQL functions called in SQL can have PL/SQL-only data types bound as input parameters, but they can not return PL/SQL-only data types as SQL does not understand them. Even if they are defined in the package specification.

◉ SQL still does not understand BOOLEAN types, so a BOOLEAN variable can be bound into an input parameter to a PL/SQL function call in a SQL statement, but BOOLEAN literals (TRUE, FALSE, or NULL) cannot be used directly.

The examples in the documentation are very good, but I will repeat similar tests here to make sure they work as described.

For most PL/SQL programmers, the biggest impact of this change will be be seen in dynamic SQL.

◉ BOOLEAN Types

The following code shows an example of binding a BOOLEAN type as an in parameter to a PL/SQL function, called in a dynamic SQL statement.

CREATE OR REPLACE FUNCTION boolean_test (p_boolean  IN  BOOLEAN)
  RETURN VARCHAR2 AUTHID DEFINER AS
BEGIN
  IF p_boolean THEN
    RETURN 'TRUE';
  ELSE
    RETURN 'FALSE';
  END IF;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_sql      VARCHAR2(32767);
  l_boolean  BOOLEAN := TRUE;
  l_result   VARCHAR2(10);
BEGIN
  l_sql := 'SELECT boolean_test(:l_boolean) INTO :l_result FROM dual';
  EXECUTE IMMEDIATE l_sql INTO l_result USING l_boolean;
  DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
l_result=TRUE

PL/SQL procedure successfully completed.

SQL>

Notice what happens if we try to substitute the bound BOOLEAN type for a BOOLEAN literal.

DECLARE
  l_sql      VARCHAR2(32767);
  l_result   VARCHAR2(10);
BEGIN
  l_sql := 'SELECT boolean_test(TRUE) INTO :l_result FROM dual';
  EXECUTE IMMEDIATE l_sql INTO l_result;
  DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
*
ERROR at line 1:
ORA-00904: "TRUE": invalid identifier
ORA-06512: at line 6

SQL>

◉ Record Types

The following code shows an example of binding record types in an anonymous blocked called from dynamic SQL.

CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS

  TYPE rec_type IS RECORD (id NUMBER, description VARCHAR2(50));

  PROCEDURE record_test (p_rec OUT rec_type);

END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS

  PROCEDURE record_test (p_rec OUT rec_type) AS
  BEGIN
    p_rec.id := 1;
    p_rec.description := 'ONE';
  END;

END;
/

SET SERVEROUTPUT ON
DECLARE
  l_sql     VARCHAR2(32767);
  l_record  test_pkg.rec_type;
BEGIN
  l_sql := 'BEGIN test_pkg.record_test(:l_record); END;';

  EXECUTE IMMEDIATE l_sql USING OUT l_record;

  DBMS_OUTPUT.PUT_LINE('l_record.id = ' || l_record.id);
  DBMS_OUTPUT.PUT_LINE('l_record.description = ' || l_record.description);
END;
/
l_record.id = 1
l_record.description = ONE

PL/SQL procedure successfully completed.

SQL>

◉ Collections

The following code shows an asociative array being bound. Currently, only associative arrays using INDEX BY PLS_INTEGER can be bound in this way, not those using INDEX BY VARCHAR2.

CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS

TYPE collection_type IS TABLE OF VARCHAR2(10)
  INDEX BY PLS_INTEGER;

FUNCTION display_collection_contents (p_collection IN collection_type)
  RETURN NUMBER;

END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS
  FUNCTION display_collection_contents (p_collection IN collection_type)
    RETURN NUMBER IS
  BEGIN
    FOR i IN p_collection.FIRST .. p_collection.LAST LOOP
      DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i));
    END LOOP;
 
    RETURN p_collection.COUNT;
  END;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_sql         VARCHAR2(32767);
  l_collection  test_pkg.collection_type;
  l_result      NUMBER;
BEGIN
  l_collection(1) := 'ONE';
  l_collection(2) := 'TWO';
  l_collection(3) := 'THREE';

  l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual';
  EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection;
  DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
1 : ONE
2 : TWO
3 : THREE
l_result=3

PL/SQL procedure successfully completed.

SQL>

The following example is similar to the previous associative array example, but it uses a nested table type.

CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS

TYPE collection_type IS TABLE OF VARCHAR2(10);

FUNCTION display_collection_contents (p_collection IN collection_type)
  RETURN NUMBER;

END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS
  FUNCTION display_collection_contents (p_collection IN collection_type)
    RETURN NUMBER IS
  BEGIN
    FOR i IN p_collection.FIRST .. p_collection.LAST LOOP
      DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i));
    END LOOP;
 
    RETURN p_collection.COUNT;
  END;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_sql         VARCHAR2(32767);
  l_collection  test_pkg.collection_type;
  l_result      NUMBER;
BEGIN
  l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE');

  l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual';
  EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection;
  DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
1 : ONE
2 : TWO
3 : THREE
l_result=3

PL/SQL procedure successfully completed.

SQL>

The VARRAY example below is very similar to that for nested tables.

CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS

TYPE collection_type IS VARRAY(10) OF VARCHAR2(10);

FUNCTION display_collection_contents (p_collection IN collection_type)
  RETURN NUMBER;

END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS
  FUNCTION display_collection_contents (p_collection IN collection_type)
  RETURN NUMBER IS
  BEGIN
    FOR i IN p_collection.FIRST .. p_collection.LAST LOOP
      DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i));
    END LOOP;
 
    RETURN p_collection.COUNT;
  END;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_sql         VARCHAR2(32767);
  l_collection  test_pkg.collection_type;
  l_result      NUMBER;
BEGIN
  l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE');

  l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual';
  EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection;
  DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
1 : ONE
2 : TWO
3 : THREE
l_result=3

PL/SQL procedure successfully completed.

SQL>

◉ TABLE Operator

The following code defines a pipelined table function that accepts a PL/SQL collection and pipes its contents out a rows, so it can be queried using the TABLE operator.

CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS

TYPE collection_type IS TABLE OF VARCHAR2(10);

FUNCTION display_collection_contents (p_collection IN collection_type)
  RETURN collection_type PIPELINED;

END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS
  FUNCTION display_collection_contents (p_collection IN collection_type)
    RETURN collection_type PIPELINED IS
  BEGIN
    FOR i IN p_collection.FIRST .. p_collection.LAST LOOP
      PIPE ROW (p_collection(i));
    END LOOP;
 
    RETURN;
  END;
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_sql         VARCHAR2(32767);
  l_collection  test_pkg.collection_type;
  l_result      NUMBER;
BEGIN
  l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE');

  l_sql := 'SELECT COUNT(*) INTO :l_result FROM TABLE(test_pkg.display_collection_contents(:l_collection))';
  EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection;
  DBMS_OUTPUT.put_line('l_result=' || l_result);
END;
/
l_result=3

PL/SQL procedure successfully completed.

SQL>

Continue reading

Extremely convenient way to run free Oracle Database 18c on your laptop – or anywhere else – using Vagrant & Virtual Box

Oracle Database 18c XE (eXpress Edition) is the free community edition of the enterprise edition of Oracle Database. Oracle Database XE has almost all features of Oracle Database; however, it is limited in non-functional terms (2 CPUs, 2 GB memory, 12 GB data volume). Oracle is generous in the terms under which XE can be used:

"We grant you a nonexclusive, nontransferable limited license to use the programs for: (a) purposes of developing, prototyping and running your applications for your own internal data processing operations; (b) you may also distribute the programs with your applications; (c) you may use the programs to provide third party demonstrations and training; and d) you may copy and distribute the programs to your licensees provided that each such licensee agrees to the terms of this Agreement.”

You can include Oracle Database XE in your production systems and in your own products that you sell to third parties.

It is not difficult to get going with Oracle Database 18c XE. You can simply download the installer for your platform and run it, to install the database natively on your platform. If however you prefer to manage all software in VMs and containers – like I do – there are other options. One such option is to make use of Vagrant and VirtualBox – to install and run Oracle Database XE in a Virtual Machine. The resulting Virtual Machine is very easily managed with vagrant commands: vagrant up, vagrant ssh (to open a terminal into the VM to perform admin actions) and vagrant halt (to stop the VM).

The instructions and scripts for automated creation of the VM and installation & configuration of the database can be downloaded from the Oracle GitHub Repository: https://github.com/oracle/vagrant-boxes/tree/master/OracleDatabase/18.4.0-XE .

The steps we have to go through:

1. Prepare: download and install Vagrant and VirtualBox

2. Download the Oracle Database XE 18c installer for Linux from OTN: https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

The download will take a few minutes – depending on your network connection.

3. Git clone the GitHub repository

4. Move the downloaded Database Installer (rpm file) to the OracleDatabase\18.4.0-XE directory in the cloned Git repo

5. Run the command: vagrant up

and wait for 10-20 minutes for the preparation of the database. No other manual intervention is required.

This will run for quite some time – during which the Oracle Linux 7 base box is downloaded, the database software is installed and the database is created and started.

Some remarkable log messages:

See how port forwarding is configured, to allow us to access the database later at localhost:1521.

After downloading the base box, the database software is installed into the VM:

And the final step:

At this point the VM is fully prepared. It is running. We can connect to the database – as if it was running on our laptop (thanks to port forwarding).

Create database connection to SYS account in SQL Developer:

and check the contents of our database:

and create a connection to the PDB:

Or connect to the database from within the VM using SQL*Plus:

Note:

◉ If you need to, you can connect to the machine via  vagrant ssh

◉ You can  sudo su - oracle to switch to the oracle user.

◉ The Oracle installation path is /opt/oracle/.

With vagrant halt I can stop the database (and the VM) and with vagrant up I can start it up again.

Run VM with APEX 19.1 and ORDS with XE

Early May 2019, a more expanded vagrant script was published to not only produce a VM with Oracle Database XE (18c) but also add APEX 19.1 and ORDS into the mix.

Source: technology.amis.nl

Continue reading

PL/SQL White Lists Using the ACCESSIBLE BY Clause in Oracle Database 12c

The ACCESSIBLE BY clause can be added to packages, procedures, functions and types to specify which objects are able to reference the PL/SQL object directly. This ability to create so called white lists is a new feature introduced in Oracle Database 12c Release 1 (12.1) to allow you to add an extra layer of security to your PL/SQL objects.

◉ Setup

The following examples will use the two database users defined below.

CONN sys/password@pdb1 AS SYSDBA

CREATE USER test1 IDENTIFIED BY test1;
GRANT CREATE SESSION, CREATE PROCEDURE, CREATE TABLE TO test1;

CREATE USER test2 IDENTIFIED BY test2;
GRANT CREATE SESSION, CREATE PROCEDURE TO test2;

◉ Basic Usage

The procedure below includes an ACCESSIBLE BY clause, indicating it can only be called by an object called calling_proc.

CONN test1/test1@pdb1

CREATE OR REPLACE PROCEDURE protected_proc
  ACCESSIBLE BY (calling_proc)
AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/

Procedure created.

SQL>

Notice we have not created the calling_proc procedure yet, but no error is produced. This is because the objects referenced by the ACCESSIBLE BY clause are not checked at compile time. Only the syntax of the clause is checked.

We can create the calling_proc procedure and use it to call the protected_proc procedure.

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : calling_proc');
  protected_proc;
END;
/

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST1 : calling_proc
TEST1 : protected_proc

PL/SQL procedure successfully completed.

SQL>

If we attempt to call the protected_proc procedure directly, we get an error.

SQL> EXEC protected_proc;
BEGIN protected_proc; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object PROTECTED_PROC
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

If we try to create a new object that references the protected_proc procedure and the new object is not in the white list, we get a compilation error.

CREATE OR REPLACE PROCEDURE another_calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : another_calling_proc');
  protected_proc;
END;
/
Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE ANOTHER_CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: Statement ignored
4/3 PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>

◉ Cross-Schema Usage

If no schema is explicitly mentioned in the white list, it is assumed the object listed is in the same schema as the object with the ACCESSIBLE BY clause. For example, if we switch to another user and create a procedure called calling_proc that accesses test1.protected_proc, it will not work.

CONN test1/test1@pdb1
GRANT EXECUTE ON protected_proc TO test2;

Grant succeeded.

SQL>

CONN test2/test2@pdb1

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST2 : calling_proc');
  test1.protected_proc;
END;
/

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE CALLING_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: Statement ignored
4/3 PLS-00904: insufficient privilege to access object PROTECTED_PROC
SQL>

For this to be successful, we must add the reference into the white list using the fully qualified object name. The example below includes an object reference without a schema prefix, signifying current schema (TEST1) and one with an explicit schema reference.

CONN test1/test1@pdb1

CREATE OR REPLACE PROCEDURE protected_proc
  ACCESSIBLE BY (calling_proc, test2.calling_proc)
AS
BEGIN
  DBMS_OUTPUT.put_line('TEST1 : protected_proc');
END;
/

Procedure created.

SQL>

Now, we can reference and execute the protected_proc procedure from the other user.

CONN test2/test2@pdb1

CREATE OR REPLACE PROCEDURE calling_proc AS
BEGIN
  DBMS_OUTPUT.put_line('TEST2 : calling_proc');
  test1.protected_proc;
END;
/

Procedure created.

SQL>


SQL> SET SERVEROUTPUT ON
SQL> EXEC calling_proc;
TEST2 : calling_proc
TEST1 : protected_proc

PL/SQL procedure successfully completed.

SQL>

◉ Unit Type

The examples so far have not specified the unit type (object type) when defining the white list references, which means any compatible object type with the correct name in the correct schema will pass the white list test. If we want to make the test more stringent, we can specify not just the name, but the unit type also. Some examples are shown below.

ACCESSIBLE BY (PACKAGE calling_pkg)
ACCESSIBLE BY (PROCEDURE calling_proc)
ACCESSIBLE BY (FUNCTION calling_func)
ACCESSIBLE BY (TYPE calling_type)
ACCESSIBLE BY (TRIGGER calling_trg)

There seem to be some discrepancies about which unit types can access PL/SQL objects that use the ACCESSIBLE BY clause. The New Features Guide suggests tables, indexes and views can also be referenced in the white list, but the unit types TABLE, INDEX and VIEW are not allowed. The following example shows that function-based indexes and views do not work against a function using the ACCESSIBLE BY clause.

CONN test1/test1@pdb1

CREATE OR REPLACE FUNCTION protected_func (id IN NUMBER)
  RETURN NUMBER
  ACCESSIBLE BY (t1_fbi, t1_vw)
AS
BEGIN
  RETURN id;
END;
/

CREATE TABLE t1 (
  id NUMBER
);


SQL> CREATE INDEX t1_fbi ON t1(protected_func(id));
CREATE INDEX t1_fbi ON t1(protected_func(id))
                          *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC

SQL>

-- Views are created normally, but fail when run.
CREATE OR REPLACE VIEW t1_vw AS
SELECT protected_func(id) AS id_vw
FROM   t1;

View created.

SQL> SELECT * FROM t1_vw;
SELECT * FROM t1_vw
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PROTECTED_FUNC

SQL>

◉ Package White Lists

In Oracle 12.1 the ACCESSIBLE BY clause is only valid at the top-level of the package specification. It can not be applied to individual packaged procedures, functions or types within the package. The following example shows a white list applied to a package specification.

CREATE OR REPLACE PACKAGE protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc;
END;
/

Package created.

SQL>

Trying to apply the white list to the packaged procedure, rather than the top-level package, results in an error.

CREATE OR REPLACE PACKAGE protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc);
END;
/

Warning: Package created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE PROTECTED_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLS-00157: Only schema-level programs allow ACCESSIBLE BY
SQL>

The ACCESSIBLE CLAUSE is not valid in the package body. It can only be defined in the package specification.

CREATE OR REPLACE PACKAGE protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc;
END;
/

Package created.

SQL>

CREATE OR REPLACE PACKAGE BODY protected_pkg
  ACCESSIBLE BY (PROCEDURE calling_proc)
AS
  PROCEDURE protected_proc AS
  BEGIN
    NULL;
  END;
END;
/

Warning: Package Body created with compilation errors.

SQL> SHOW ERRORS
Errors for PACKAGE BODY PROTECTED_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PLS-00103: Encountered the symbol "ACCESSIBLE" when expecting one
of the following:
is as compress compiled wrapped

SQL>

◉ Package White Lists (12.2 Update)

In Oracle database 12.1 it was only possible to use a white list for a whole package. In Oracle 12.2 this limitation is no longer present, making the concept of white lists for packages much more granular. It is now possible to white list individual subprograms or a package.

CREATE OR REPLACE PACKAGE protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc);

  FUNCTION protected_func RETURN NUMBER
    ACCESSIBLE BY (PROCEDURE calling_func);
END;
/

CREATE OR REPLACE PACKAGE BODY protected_pkg
AS
  PROCEDURE protected_proc
    ACCESSIBLE BY (PROCEDURE calling_proc)
  AS
  BEGIN
    NULL;
  END;

  FUNCTION protected_func RETURN NUMBER
    ACCESSIBLE BY (PROCEDURE calling_func)
  AS
  BEGIN
    RETURN NULL;
  END;
END;
/

Continue reading

WITH Clause Enhancements in Oracle Database 12c

In addition to the Subquery Factoring Clause, Oracle 12c includes a PL/SQL declaration section in the WITH clause.

◉ Setup

The examples in this article require the following test table.

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
FROM   dual
CONNECT BY level <= 1000000;

-- Gathering stats on a CTAS is no longer necessary in 12c,
-- provided the statement is issued by a non-SYS user.
-- EXEC DBMS_STATS.gather_table_stats(USER, 't1');

◉ Functions in the WITH Clause

The declaration section of the WITH clause can be used to define PL/SQL functions, as shown below.

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1
/

WITH_FUNCTION(ID)
-----------------
                1

SQL>

Interestingly, the ";" does not seem to work as a terminator to the SQL statement when the PL/SQL declaration is included in the WITH clause. If we attempt to use it on its own, SQL*Plus waits for more text to be entered.

From a name resolution perspective, functions defined in the PL/SQL declaration section of the WITH clause take precedence over objects with the same name defined at the schema level.

◉ Procedures in the WITH Clause

We can also define procedures in the declaration section, even if they are not used.

SET SERVEROUTPUT ON

WITH
  PROCEDURE with_procedure(p_id IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('p_id=' || p_id);
  END;
SELECT id
FROM   t1
WHERE  rownum = 1
/

        ID
----------
         1

SQL>

In reality, you would only put a procedure into a WITH clause if you planned to call the procedure from a function in the declaration section.

WITH
  PROCEDURE with_procedure(p_id IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('p_id=' || p_id);
  END;

  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    with_procedure(p_id);
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1
/

WITH_FUNCTION(ID)
-----------------
                1

p_id=1
SQL>

◉ PL/SQL Support

There does not appear to be any PL/SQL support for this feature. Any attempt to use it results in compilation errors, as shown below.

BEGIN
  FOR cur_rec IN (WITH
                    FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
                    BEGIN
                      RETURN p_id;
                    END;
                  SELECT with_function(id)
                  FROM   t1
                  WHERE  rownum = 1)
  LOOP
    NULL;
  END LOOP;
END;
/
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
                             *
ERROR at line 3:
ORA-06550: line 3, column 30:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop

SQL>

Using dynamic SQL allows you to work around this restriction.

SET SERVEROUTPUT ON
DECLARE
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  l_value   NUMBER;
BEGIN
  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1
            WHERE  rownum = 1';
 
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor INTO l_value;
  DBMS_OUTPUT.put_line('l_value=' || l_value);
  CLOSE l_cursor;
END;
/
l_value=1

PL/SQL procedure successfully completed.

SQL>

Support for this feature using static SQL inside PL/SQL is due in a future release.

◉ Performance Advantages

The whole reason for defining the PL/SQL code inline is to improve performance. Create a regular function to use as a comparison.

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
  RETURN p_id;
END;
/

Run the following test, which measures the elapsed time and CPU usage of the query using the inline function definition.

SET SERVEROUTPUT ON
DECLARE
  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;
 
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
 
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab t_tab;
BEGIN
  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1';
           
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
 
  DBMS_OUTPUT.put_line('WITH_FUNCTION  : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'SELECT normal_function(id)
            FROM   t1';
           
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
 
  DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

END;
/
WITH_FUNCTION  : Time=45 hsecs CPU Time=39 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs

PL/SQL procedure successfully completed.

SQL>

From this we can see the inline function definition takes approximately one third the elapsed time and CPU time to complete.

◉ PRAGMA UDF

In a number of presentations prior to the official 12c release, speakers mentioned PRAGMA UDF (User Defined Function), which supposedly gives you the performance advantages of inline PL/SQL, whilst allowing you to define the PL/SQL object outside the SQL statement. The following code redefines the previous normal function to use this pragma.

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
  PRAGMA UDF;
BEGIN
  RETURN p_id;
END;
/

Once the function is compiled, running the test from the previous section against this function produces rather interesting results.

SET SERVEROUTPUT ON
DECLARE
  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;
 
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
 
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab t_tab;
BEGIN
  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1';
           
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
 
  DBMS_OUTPUT.put_line('WITH_FUNCTION  : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'SELECT normal_function(id)
            FROM   t1';
           
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
 
  DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

END;
/
WITH_FUNCTION  : Time=44 hsecs CPU Time=40 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs

PL/SQL procedure successfully completed.

SQL>

The standalone function using PRAGMA UDF seems to consistently out perform the inline function.

I was under the impression calling a function defined with PRAGMA UDF directly from PL/SQL would fail. This does not seem to be the case.

DECLARE
  l_number NUMBER;
BEGIN
  l_number := normal_function(1);
END;
/

PL/SQL procedure successfully completed.

SQL>

◉ WITH_PLSQL Hint

If the query containing the PL/SQL declaration section is not the top level query, the top-level query must include the WITH_PLSQL hint. Without this hint, the statement will fail to compile, as shown by the following update statement.

UPDATE t1 a
SET a.id = (WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(a.id)
            FROM   dual);
/
SET a.id = (WITH
            *
ERROR at line 2:
ORA-32034: unsupported use of WITH claus

SQL>

Adding the WITH_PLSQL hint allows the statement to compile and run as expected.

UPDATE /*+ WITH_PLSQL */ t1 a
SET a.id = (WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(a.id)
            FROM   dual);
/

1000000 rows updated.

SQL>

Remember, this is true of queries and DML.

◉ DETERMINISTIC Hint

The use of functions in the WITH clause prevent the DETERMINISTIC optimizations from taking place in the initial release.

SET TIMING ON ARRAYSIZE 15

WITH
  FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
  BEGIN
    DBMS_LOCK.sleep(1);
    RETURN p_id;
  END;
SELECT slow_function(id)
FROM   t1
WHERE  ROWNUM <= 10;
/

SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
1

10 rows selected.

Elapsed: 00:00:10.07
SQL>

The behaviour was fixed in 18c onward. Notice below the elapsed time is down to approximately 1 second. Thanks to Mikhail L for pointing that out.

SET TIMING ON ARRAYSIZE 15

WITH
  FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
  BEGIN
    DBMS_LOCK.sleep(1);
    RETURN p_id;
  END;
SELECT slow_function(id)
FROM   t1
WHERE  ROWNUM <= 10;
/

SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
1

10 rows selected.

Elapsed: 00:00:01.071
SQL>

◉ Scalar Subquery Caching

In the previous section we saw the negative impact of inline function definitions on the DETERMINISTIC hint optimizations. Fortunately, scalar subquery caching is not adversely affected in the same way.

SET TIMING ON

WITH
  FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
  BEGIN
    DBMS_LOCK.sleep(1);
    RETURN p_id;
  END;
SELECT (SELECT slow_function(id) FROM dual)
FROM   t1
WHERE  ROWNUM <= 10;
/

(SELECTSLOW_FUNCTION(ID)FROMDUAL)
---------------------------------
1
1
1
1
1
1
1
1
1
1

10 rows selected.

Elapsed: 00:00:01.04
SQL>

Continue reading

Understanding Views in SQL

Tables in a SQL database may contain enormous amount of data, but they are not always in a useful format to be readily used. The volume of data must be filtered based upon some specified criteria for efficient use. Due to security reason, we might want to make public only a certain amount of data while rest might be accessible to the privileged users. The SQL DML operations are extensible and used to filter through one or more tables using complex query expressions. Leveraging the idea, we can create virtual tables from persistent base tables using SQL that would contain the exact data we need. This is the reason the SQL:2006 standard introduced the use of view tables, or views. The definition of a view or a virtual table exists as a schema object. This article introduces the concept of views in SQL, how it works and shows how it is implemented with some examples.

Introduction to SQL Views

SQL views are nothing but virtual tables reside in memory derived from one or more base tables. Virtual tables mean the tuples in views do not have physical existence and are not stored into the database. The tuples are like temporary data created as an outcome of the SQL query which typically draws filtered data from one or more base tables. As a result, there is a limitation on the type of operation that can be applied to a view table. For example, the update operation cannot be applied to all types views, but it has no limitation on applying SQL query on it.

The examples below are tested with the MySQL database. Start by creating a few tables:

my_company database:

CREATE DATABASE my_company;

CREATE TABLE Employee(
    empId INT(11) UNSIGNED CHECK (empId > 0),
    empName VARCHAR(20),
    birthDate DATE,
    address TEXT(128),
    gender VARCHAR(1),
    salary DECIMAL(15,2),
    managerId INT(11) UNSIGNED,
    deptId INT(11) UNSIGNED,
    PRIMARY KEY(empId)
);

CREATE TABLE Department(
    deptId INT(11) UNSIGNED CHECK (empId > 0),
    deptName VARCHAR(20),
    deptMgrId INT(11) UNSIGNED,
    mgrStartDate DATE,
    PRIMARY KEY(deptId)
);

CREATE TABLE Project(
    projId INT(11) UNSIGNED CHECK (empId > 0),
    projName VARCHAR(20),
    projLocation TEXT(128),
    deptId INT(11) UNSIGNED,
    PRIMARY KEY(projId)
);

CREATE TABLE EmpWorksOnProj(
    empId INT(11) UNSIGNED,
    projId INT(11) UNSIGNED,
    hoursWorked DECIMAL(4,2)
);

ALTER TABLE Employee ADD CONSTRAINT fk_emp_mgr FOREIGN KEY(managerId) REFERENCES Employee(empId);
ALTER TABLE Employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES Department(deptId);
ALTER TABLE Department ADD CONSTRAINT fk_dept_mgr FOREIGN KEY(deptMgrId) REFERENCES Employee(empId);
ALTER TABLE Project ADD CONSTRAINT fk_proj_dept FOREIGN KEY(deptId) REFERENCES Department(deptId);

The views can be thought of as a reference table, and we can use it as frequently as we want although it may not exist physically. For example, we may frequently need to refer to the my_company database and find Employee and Project information. Note that there is many to many relationship between Employee and Project as one person can work on many project and also one project has many employees. Therefore, instead of specifying the join of three tables: Employee, EmpWorksOnProj, and Project every time we need a collaborative information and issue a query, we define a view that is specified as the outcome of the join among these tables. The view forms the virtual table created from the result of the query. The advantage is that the query now can retrieve from single resultant table rather than having to retrieve from three joined tables. The collection of tables: Employee, Project, Department etc. thus form the base tables or defining table of the view.

Let's create some views based upon the schema given above.

CREATE VIEW V1
AS
SELECT
   empName, projName, hoursWorked
FROM
   Employee, Project, EmpWorksOnProj
WHERE
   Employee.empId=EmpWorksOnProj.empId
AND
   Project.projId=EmpWorksOnProj.projId;

The way to specify SQL queries on view or virtual table is same as specifying queries involving base tables. You can use SQL SELECT on views to get the data as follows:

SELECT * FROM V1;

EmpName	ProjName	HoursWorked
Mickey Mouse	ClubHouse	6.50
...	...	...
Donald Duck	Farming	7.0

The following creates a second view:

CREATE VIEW V2 AS SELECT deptName, COUNT(*), SUM(salary) FROM Department, Employee WHERE Employee.deptId=Department.deptId GROUP BY deptName;

The SQL SELECT results in

SELECT * FROM V1;

EmpName	COUNT(*)	SUM(salary)
Music	5	56000.00
...	...	...
Drama	2	25400.00

Note that in view V1 the attribute names are derived from the base table. In V2 new attribute names are explicitly specified using one to one correspondence between the specified attributes of CREATE VIEW clause and those specified in the SELECT clause. The SELECT clause with the view is decisive of the definition of the view.

The information on view is always supposed to be up to date. That means it must always reflect the changes made on the base tables on which it is defined. This is interesting, because it means that the view is not actually materialized at the time of defining it, but later when a query is specified on it. The database management system at the background is responsible for keeping the view up to date.

UPDATE, INSERT and DELETE on views

In SQL, it is possible to create updatable views that can be used to change existing data or insert new rows into the view which in turn inserts or modifies the record in the base table. A view is updatable or not is determined by SELECT statement defined within the view definition. There is no special clause to designate a view to be updatable. Typically, the view definition must be simple and must not contain any aggregate functions such as SUM, AVG, MAX, MIN, COUNT. Any sort of grouping or DISTINCT or JOIN clause also makes view not updatable. Refer to the relevant database manual of the specific RDBMS for what makes a view non-updatable.

Let’s create a view that is updatable:

CREATE VIEW v3_ch_dept_name

AS

SELECT

   deptId, deptName, deptMgrId, mgrStartDate

FROM

   Department;

The SELECT query on view:

SELECT * FROM v3_ch_dept_name;

DeptId	DeptNama	DeptMgrId	MgrStartDate
Music	5	56000.00	2020-01-01
...	...	...	...
Drama	2	25400.00	2018-03-05

Now update the view by changing the department name (deptName).

UPDATE

   v3_ch_dept_name

SET

   deptName = 'Security'

WHERE

   deptId = 5;

A row can be inserted in the view as follows:

INSERT

   INTO v3_ch_dept_name

VALUES (7,'Logistics',666884444,'1982-07-07');

Also we can DELETE a row from the view as follows:

DELETE FROM v3_ch_dept_name WHERE deptId = 7;

In MySQL, you can easily find the views in a database that are updatable or not using following SELECT command.

SELECT

   table_name

FROM

   information_schema.views

WHERE

   is_updatable like 'YES'

AND

   table_schema like 'my_company';

DROP views from the database

A view can always be disposed of with DROP VIEW <view_name> command.

DROP VIEW V1;

Note that when we execute the drop view command it removes the view definition. The underlying data stored in the base tables from which this view is derived remains unchanged. A view once dropped can be recreated with the same name.

The ALTER VIEW statement

Views are generally unalterable according to SQL:2006 standard, that means the ALTER VIEW statement does not work with views. However, there are RDBMS's like MySQL or SQL Server that supports this kind of statement. The Oracle believes in dropping the view first then recreating it rather than altering it. Therefore, the functionalities supported on views by RDBMS's vary from product to product.

Continue reading