Oracle Views
Version 23c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose A view is a logical entity: 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.

For information on Editioning Views, Object View, XML Views and other subtypes use the link at page bottom.
View constraint information can be found using the Constraints link.
Dependencies
ALL_CONSTRAINTS CDB_UPDATABLE_COLUMNS USER_CONSTRAINTS
ALL_CONS_COLUMNS CDB_VIEWS USER_CONS_COLUMNS
ALL_TAB_COLS DBA_CONSTRAINTS USER_TAB_COLS
ALL_UPDATABLE_COLUMNS DBA_CONS_COLUMNS USER_UPDATABLE_COLUMNS
ALL_VIEWS DBA_TAB_COLS USER_VIEWS
CDB_CONSTRAINTS DBA_UPDATABLE_COLUMNS VIEW$
CDB_CONS_COLUMNS DBA_VIEWS VIEWCON$
CDB_TAB_COLS    
System Privileges
CREATE ANY VIEW DROP ANY VIEW UNDER ANY VIEW
CREATE VIEW    
 
Create View
Create Single Table Basic View CREATE OR REPLACE VIEW [<EDITIONING> | <EDITIONABLE [EDITIONING]> | <NONEDITIONABLE>]
[IF NOT EXISTS] VIEW  [<schema_name>.]<view_name>
[SHARING = <METADATA> | <DATA> | <EXTENDED DATA> | <NONE>]
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 requirement conn scott/tiger@pdbdev

CREATE OR REPLACE VIEW function_view AS
SELECT UPPER(ename), LOWER(job), deptno
FROM scott.emp;
       *
ERROR at line 2:
ORA-00998: must name this expression with a column alias

-- alias the columns

CREATE OR REPLACE VIEW scott.function_view AS
SELECT UPPER(ename) AS ENAME, LOWER(job) AS JOB, deptno
FROM scott.emp;

SELECT * FROM scott.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 IS
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';
 
Parameterized View
Create a parameterized view with a filter CREATE FORCE VIEW <view_name> AS
<select statement>
WHERE <function_or_filter>;
CREATE OR REPLACE VIEW nofilter_view AS
SELECT owner, object_type, COUNT(*) AS TOTAL
FROM all_objects
GROUP BY owner, object_type
ORDER BY 1,2;

SELECT * FROM nofilter_view;

CREATE OR REPLACE VIEW filter_view AS
SELECT owner, object_type, COUNT(*) AS TOTAL
FROM all_objects
WHERE owner = USER
GROUP BY owner, object_type
ORDER BY 1,2;

SELECT * FROM filter_view;
Create a parameterized view with a function conn sys@pdbdev as sysdba

GRANT select ON v_$mystat TO uwclass;
GRANT select ON v_$session TO uwclass;

conn uwclass/uwclass

CREATE OR REPLACE FUNCTION filter_function RETURN NUMBER AUTHID CURRENT_USER IS
 l_retval NUMBER;
BEGIN
  SELECT sid
  INTO l_retval
  FROM v$mystat
  WHERE rownum = 1;

  RETURN l_retval;
END filter_function;
/

CREATE OR REPLACE VIEW function_view AS
SELECT COUNT(*) AS ROWCOUNT
FROM sys.v_$session
WHERE sid = filter_function;

SELECT * FROM function_view;
 
Alter View
Recompile an invalid view ALTER VIEW <view_name> COMPILE;
ALTER VIEW person_view COMPILE;
 
Commenting Views and View Columns
Comment a view COMMENT ON TABLE <table_name> IS '<comment_string>';
COMMENT ON TABLE airplanes_view IS 'This is a parameterized view';

col comments format a30

SELECT table_name, comments
FROM user_tab_comments;
Comment a view column COMMENT ON COLUMN <table_name.column_name> IS '<comment>';
COMMENT ON COLUMN airplanes_view.order_date IS 'Order accepted date';

SELECT table_name, column_name, comments
FROM user_col_comments
WHERE table_name = 'AIRPLANES_VIEW';
 
Drop View
Remove a view and its source code from the data dictionary DROP VIEW <view_name>;
SELECT view_name
FROM user_views;

DROP VIEW date_view;
 
Updatable Views
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 conn uwclass/uwclass@pdbdev

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 conn uwclass/uwclass@pdbdev

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
View with a Primary Key Constraint 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>;
conn uwclass/uwclass@pdbdev

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 View CREATE OR REPLACE VIEW <view_name> AS
<select statement>
WITH READ ONLY;
conn uwclass/uwclass@pdbdev

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
View with 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;
conn uwclass/uwclass@pdbdev

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 Code and Demos
Dependency Tracking. This demo should guide the code you write. CREATE TABLE t(
col1 NUMBER);

CREATE OR REPLACE VIEW v1_t AS
SELECT COUNT(*) ROWCOUNT FROM t;

CREATE OR REPLACE VIEW v2_t AS
SELECT COUNT(1) ROWCOUNT FROM t;

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

ALTER TABLE t ADD (col2 NUMBER);

SELECT object_name, status
FROM user_objects
WHERE object_type = 'VIEW';
Retrieve View source code from the data dictionary set linesize 121

SELECT owner, text
FROM all_views
WHERE owner = 'UWCLASS'
AND view_name = 'PERSON_VIEW';
Another way to retrieve View source code from the data dictionary set linesize 121
set long 1000000

SELECT dbms_metadata.get_ddl('VIEW', 'PERSON_VIEW', 'UWCLASS')
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 are allowed SELECT table_name, column_name, updatable, insertable, deletable
FROM user_updatable_columns
ORDER BY 1, 2;
Force a CHAR field in a View (not that I can think of a good reason why you would want to do so) 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
To determine the cost of using a view set linesize 121

CREATE TABLE t AS
SELECT * FROM all_objects;

SELECT COUNT(*) FROM all_objects;

SELECT COUNT(*) FROM t;

set autotrace traceonly

SELECT * FROM all_objects;

SELECT * FROM t;

set autotrace off

Related Topics
DBMS_APPLICATION_INFO
Constraints
Editioning Views
In-Line View
Instead Of Trigger
Materialized View
Object Views
Pipelined Table Functions
Select Statement
Snapshot
XML Views
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx