Oracle Views

Search the Reference Library pages:

Oracle Views

Version 11.1

General

NOTE: A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace.

View Related Data Dictionary Objects

view$	viewcon$
dba_contraints	all_constraints	user_constraints
dba_cons_columns	all_cons_columns	user_cons_columns
dba_tab_cols	all_tab_cols	user_tab_cols
dba_updatable_columns	all_updatable_columns	user_updatable_columns
dba_views	all_views	user_views

System Privileges

create view

create any view

drop any view

under any view

Create View

Create Single Table Basic View

CREATE OR REPLACE VIEW <view_name> AS
SELECT <column_name>
FROM <table_name>;

CREATE TABLE person (
person_id NUMBER(3),
first_name VARCHAR2(25),
last_name VARCHAR2(25),
title_1    VARCHAR2(10),
title_2    VARCHAR2(10),
socsecno   VARCHAR2(11));

INSERT INTO person
VALUES (1, 'Dan', 'Morgan', 'BS', 'PhD', '123-54-0987');

INSERT INTO person
VALUES (1, 'Helen', 'Lofstrom', 'BA', 'MA', '987-03-4793');

INSERT INTO person
VALUES (1,'Tara','Havemeyer','BA',NULL,'402-87-1005');
COMMIT;

set linesize 141

SELECT * FROM person;

CREATE OR REPLACE VIEW person_view AS
SELECT first_name AS FNAME, last_name LNAME, socsecno
FROM person;

SELECT * FROM person_view;

Create Single Table View With WHERE Clause

CREATE OR REPLACE VIEW <view_name> AS
SELECT <column_name>
FROM <table_name>
WHERE <column_name> = <value or condition>;

CREATE OR REPLACE VIEW person_two_titles_view AS
SELECT first_name, last_name, socsecno
FROM person
WHERE title_1 IS NOT NULL
AND title_2 IS NOT NULL;

SELECT * FROM person_two_titles_view;

Create Single Table View With FUNCTION in the SELECT clause and column alias

CREATE OR REPLACE VIEW function_view AS
SELECT UPPER(first_name), LOWER(last_name), socsecno
FROM person;

-- alias the columns

CREATE OR REPLACE VIEW function_view AS
SELECT UPPER(first_name) FIRST, LOWER(last_name) LAST, socsecno
FROM person;

SELECT * FROM function_view;

Create Single Table View For Security

CREATE OR REPLACE VIEW person_security_view AS
SELECT first_name || ' ' || last_name NAME,
'***-**-' || SUBSTR(socsecno,8) SSN
FROM person;

SELECT * FROM person_security_view;

Create Single Table View With FUNCTION in the WHERE clause

ALTER TABLE person
ADD (created_by VARCHAR2(30), created_dt DATE);

UPDATE person
SET created_by = 'UWCLASS', created_dt = SYSDATE
WHERE last_name = 'Morgan';

UPDATE person
SET created_by = 'IDS', created_dt = SYSDATE+2
WHERE last_name = 'Lofstrom';

UPDATE person
SET created_by = 'UWCLASS', created_dt = SYSDATE-10
WHERE last_name = 'Havemeyer';

CREATE OR REPLACE VIEW user_view AS
SELECT first_name, last_name, title_1, title_2
FROM person
WHERE created_by = USER;

SELECT * FROM user_view;

CREATE OR REPLACE VIEW date_view AS
SELECT first_name, last_name, title_1, title_2
FROM person
WHERE created_dt > SYSDATE-1;

SELECT * FROM date_view;

Create Multi-table View

CREATE OR REPLACE VIEW explan_four AS
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM explan_four;

CREATE OR REPLACE VIEW explan_six AS
SELECT srvr_id
FROM servers s
WHERE EXISTS (
SELECT srvr_id
FROM serv_inst i
WHERE s.srvr_id = i.srvr_id);

SELECT * FROM explan_six;

Force View

Force ... Forces The Creation Of A View Even When The View Will Be Invalid. NoForce Is The Default

CREATE FORCE VIEW <view_name> AS
<select statement>;

-- assuming the table xyz does not exist

CREATE OR REPLACE FORCE VIEW view_force AS
SELECT * FROM xyz;

-- ignore the error message

col object_name format a30

SELECT object_name, status
FROM user_objects
WHERE object_type = 'VIEW';

Drop View

Drop View

DROP VIEW <view_name>;

SELECT view_name
FROM user_views;

DROP VIEW date_view;

Updatable Views

Note: Updateable views can not include:

Set Operators (INTERSECT, MINUS, UNION, UNION ALL)
DISTINCT
Group Aggregate Functions (AVG, COUNT, MAX, MIN, SUM, etc.)
GROUP BY Clause
ORDER BY Clause
CONNECT BY Clause
START WITH Clause
Collection Expression In A Select List
Subquery In A Select List
Join Query

If pseudocolumns are present they can not be included in an update statement.

Updateable View

CREATE TABLE demo_tab (
person_id NUMBER(3),
first_name VARCHAR2(20),
last_name VARCHAR2(20));

CREATE OR REPLACE VIEW upd_view AS
SELECT * FROM demo_tab;

INSERT INTO demo_tab
(person_id, first_name, last_name)
VALUES
(1, 'Daniel', 'Morgan');

INSERT INTO demo_tab
(person_id, first_name, last_name)
VALUES
(2, 'Helen', 'Lofstrom');

COMMIT;

SELECT * FROM upd_view;

UPDATE upd_view
SET person_id = person_id * 10;

SELECT * FROM upd_view;

desc user_updatable_columns

SELECT table_name, column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name IN (
SELECT view_name
FROM user_views);

Non-updatable View

CREATE OR REPLACE VIEW nonupd_view AS
SELECT DISTINCT *
FROM demo_tab;

SELECT table_name, column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'NONUPD_VIEW';

SELECT * FROM nonupd_view;

UPDATE nonupd_view
SET person_id = person_id * 10;

Primary Key - Type P

Primary Key on a view

CREATE OR REPLACE VIEW <view_name>
(<column_name, column_name>
UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT <constraint_name>
PRIMARY KEY (<column_name>) RELY DISABLE NOVALIDATE) AS
<select statement>;

CREATE OR REPLACE VIEW person_pk_view
(person_id, last_name
UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT pk_person_view
PRIMARY KEY (person_id) RELY DISABLE NOVALIDATE) AS
SELECT person_id, last_name FROM person;

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'PERSON_PK_VIEW';

Read Only Constraint - Type O

Read Only on a view

CREATE OR REPLACE VIEW <view_name> AS
<select statement>
WITH READ ONLY;

CREATE OR REPLACE VIEW person_reg_view AS
SELECT first_name, last_name, ssn
FROM person;

SELECT first_name FROM person;

UPDATE person_reg_view
SET first_name = 'Dan'
WHERE first_name = 'Daniel';

SELECT first_name FROM person;

CREATE OR REPLACE VIEW person_ro_view AS
SELECT first_name, last_name, ssn
FROM person
WITH READ ONLY;

UPDATE person_ro_view
SET first_name = 'Dan'
WHERE first_name = 'Daniel';

Check Option - Type V

Check option

Specify WITH CHECK OPTION to indicate that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery.

CREATE OR REPLACE VIEW <view_name> AS
<select statement with WHERE clause>
WITH CHECK OPTION;

CREATE OR REPLACE VIEW insertable_view AS
SELECT person_id, first_name, last_name, dob, ssn
FROM person
WHERE person_id < 10000;

SELECT person_id, first_name, last_name FROM person;

INSERT INTO insertable_view
SELECT 7, 'Allen', 'Richards', dob, ssn
FROM person
WHERE ROWNUM = 1;

INSERT INTO insertable_view
SELECT 77777, 'Richard', 'Allen', dob, ssn
FROM person
WHERE ROWNUM = 1;

SELECT person_id, first_name, last_name FROM person;

CREATE OR REPLACE VIEW checkoption_view AS
SELECT person_id, first_name, last_name, dob, ssn
FROM person
WHERE person_id < 10
WITH CHECK OPTION;

INSERT INTO checkoption_view
SELECT 8, 'Natalie', 'Dressed', dob, ssn
FROM person
WHERE ROWNUM = 1;

INSERT INTO checkoption_view
SELECT 88, 'Rich', 'Poorly', dob, ssn
FROM person
WHERE ROWNUM = 1;

SELECT person_id, first_name, last_name FROM person;

View Related Queries

Retrieve View Source Code

set linesize 121
set long 1000000

SELECT owner, text
FROM all_views
WHERE owner = 'UWCLASS'
AND view_name = 'PERSON_VIEW';

SELECT dbms_metadata.get_ddl('VIEW', 'PERSON_VIEW')
FROM DUAL;

Information On columns In A View

SELECT view_name
FROM user_views;

col data_type format a30

SELECT column_name, data_type, data_length
FROM user_tab_cols
WHERE table_name = 'PERSON_VIEW';

Information About Columns In Which Deletes, Inserts, and Updates

SELECT table_name, column_name, updatable, insertable, deletable
FROM user_updatable_columns
ORDER BY 1, 2;

To Force A CHAR field In A View

CREATE TABLE t (
col1 VARCHAR2(20));

CREATE OR REPLACE VIEW char_view AS
SELECT col1, CAST(RPAD(col1, 1, ' ') AS CHAR(10)) col2
FROM t;

desc char_view

The cost of using a view

set linesize 121

CREATE TABLE t AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) < 'X';

SELECT COUNT(*) FROM all_objects
WHERE SUBSTR(object_name,1,1) < 'X';

SELECT COUNT(*) FROM t;

set autotrace traceonly

SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) < 'X';

SELECT * FROM t
WHERE SUBSTR(object_name,1,1) < 'X';

set autotrace off

Related Topics

XML View

-----