EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Views
 

Oracle Views

Afshan Banu
Article byAfshan Banu
EDUCBA
Reviewed byRavi Rathore

Updated March 23, 2023

Oracle Views

 

 

Introduction to Oracle Views

View in Oracle database is actually a virtual table that is not physically stored in the database data dictionary and does not store any sort of data and is created generally by using join query with two or more tables which enables the view to hide the data complexity also providing security since actually we are not storing any data in the view physically so it actually restricts the access of various columns of a table to the user of the database. Views are created by a query joining one or more tables.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

CREATE VIEW in Oracle

Let us see creating View in Oracle with the help of some examples mentioned below:

The syntax to create the view –

CREATE VIEW view name AS
SELECT column [ , column ] FROM table
WHERE condition;

View name – It specifies Oracle VIEW name that the user wants to create.

An oracle view visual representation is represented in the below diagram, as in the below diagram the red shaded area return as the result of the Oracle select query and which is stored as a view –

diagram the red

Query Examples

Let’s take an example to create a view. Here is an example, we are creating a simple view on a single table.

Suppliers table

Query Example:

CREATE VIEW empview AS
SELECT *
FROM employees;
WHERE employees.manager_id = 100;

Output:

oracle views

View created.0.21 seconds

This above Oracle INNER JOIN example will return all rows from the employee table and department table where the employee _id value in both the employee table and department table are matched.

Let’s understand the oracle view in detail with the help of some of the examples query:

We consider here the hr schema which is the oracle database sample schemas. The hr schema contains COUNTRIES, EMPLOYEES, DEPARTMENTS, JOB_HISTORY, JOBS, LOCATIONS, REGIONS tables, in which we are interested or require EMPLOYEES, DEPARTMENTS and LOCATIONS tables.

The description of these tables are :

Table EMPLOYEES

Table Employees

Table DEPARTMENTS

Table departments

Example #1

Create a view on selected columns

Here we create a view named employee view based on the employee’s table. The employee view having the employee id, employee full name which is the concatenation of first_name and last_name and employee phone_number-

Code:

CREATE VIEW employee view AS
SELECT
employee_id,
first_name || ' ' || last_name full name,
phone_number
FROM
employees;

Output:

selected columns

We can now check the just created above Oracle VIEW by using this query –

Select * from employee view;

Output:

employee view

Example #2

Creating oracle view with columns alias

Code:

CREATE VIEW employee view AS
SELECT employee_id, first_name || '  ' || last_name "full name", FLOOR( months_between ( CURRENT_DATE, hire_date )/ 12 ) as years
FROM employees;

Output:

oracle view with columns

Select * from employee view;

Output:

create view

Example #3

Creating oracle view with columns alias

Code:

CREATE VIEW employee view (employee_id, full name, years) AS
SELECT
employee_id,first_name || '  ' || last_name,
FLOOR( months_between ( CURRENT_DATE, hire_date )/ 12 )
FROM
employees;

Output:

column alias

Select * from employee view;

Output:

view

Example #4

Access view with where clause

Code:

SELECT *
FROM employee view
WHERE years > 10;
ORDER BY full name;

Output:

view1

Example #5

Access view with an order by clause

Code:

SELECT full name
FROM employee view
WHERE years > 10
ORDER BY full name;

Output:

view2

Example #6

Update and to make the read-only view

Code:

CREATE OR REPLACE VIEW employee view (employee_id, full name, years) AS
SELECT
employee_id,first_name || '  ' || last_name,
FLOOR ( months_between ( CURRENT_DATE, hire_date ) / 12 )
FROM employees WITH reading ONLY;

Output:

view3

Example #7

Creating oracle view with multiple tables

Code:

CREATE OR REPLACE VIEW dept_manger AS
SELECT
employee_id,first_name || '  ' || last_name as name, department_name
FROM EMPLOYEES
INNER JOIN DEPARTMENTS
on EMPLOYEES. EMPLOYEE_ID = DEPARTMENTS. MANAGER_ID;,

Output:

oracle1

Example #8

Delete view by using the Drop statement

Code:

Drop view employee view;

Output:

oracle views11

Advantages and Disadvantages of Views in an oracle

The advantages and disadvantages are given below:

Advantages of Views in an oracle

Below are the advantages:

  • The view can be created on selected data or column of the table, which restrict the view of a table and can hide some of the data or column in the tables.
  • The view creates to view the data without storing the data into the table.
  • View cab is created to Join two or more tables data and store it as one table or object.
  • A view cab is created to achieve security.
  • The view gives good performance to run complex queries or join queries.

Disadvantages of Views in an oracle

Below are the disadvantages:

  • DML operations cannot perform in view.
  • The view becomes inactive if the table is dropped on which it is created.
  • View occupies a memory space as it is an object.

Conclusion

Views are virtual tables that do not physically exist. The views are created on the existing table or tables. Views are created on the frequently used queries or complex queries or join queries. The view gives good performance to run complex queries or join queries. DML operations cannot perform in view. A view cab is created to achieve security. The view becomes inactive if the table is dropped on which it is created.

Recommended Articles

This is a guide to Oracle Views. Here we discuss the Parameters of the Oracle Views, Advantages and Disadvantages along with the query examples. You can also go through our other suggested articles to learn more–

  1. Joins in Oracle
  2. Oracle Data Warehousing
  3. Oracle Queries
  4. What Is Oracle Database
  5. Guide to Java String Concatenation
  6. A Quick Glance of Oracle Versions
  7. Top 9 Operators of PostgreSQL WHERE Clause
  8. Differences of Inner Join vs Outer Join
  9. How to Use MySQL Outer Join?
  10. Top 4 Types of SQL Outer Join

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW