Oracle views
A View is an oracle database schema object .It is a logical table, which is created from tables, views, or both. We can say that a view is a select statement of tables or other views.
Some of the advantages of views include but not limited to:
- enhance performance of an application.
- enforce security.
- It simplifies user SQL and hides the complexity query from an application.
On this topic, we will going to see how to manage oracle views such as create ,modify and drop views using SQL*PLUS command-line tool.
To start with, let us create a view named ‘vw_customer‘ using SQL*PLUS.
D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 27 13:07:19 2022 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> conn mereba/mereba@orcl Connected. SQL> create or replace view vw_customer as 2 select * 3 from tbl_customer; View created. SQL>
And to retrieve data from the view created above, run the following SQL statement:
col cust_no format a30; col customer_type format a10; col cust_name format a30; col nationality format a20; SQL> select * from vw_customer; CUST_NO CUSTOMER_T CUST_NAME Nationality ---------- ---------- ------------------------------ -------------------- ########## I Messi John Dave USA ########## C Mereba Technology UK ########## B Swiss Bank Switzerland SQL>
Modifying view definition
If a table where an oracle view is based-on is modified, we have to modify the query either by re-creating or dropping the view, or using the create or replace view syntax.
Modifying vw_customer view definition:
Example: ---Adding new column named swift_code to tbl_customer table SQL> Alter table tbl_customer add (swift_code varchar2(20)); Table altered. --- Renaming column named nationality to country SQL> alter table tbl_customer rename column nationality to country; Table altered.
Now, let us try to select from the vw_customer view.
SQL> select * from vw_customer; select * from vw_customer * ERROR at line 1: ORA-04063: view "MEREBA.VW_CUSTOMER" has errors SQL>
So, as you can see the above error, when we make table structure modifications, we have to also re-create the corresponding views.
And in this example, we have to re-create the vw_customer using the create or replace view syntax to fix the above error.
Example: SQL> create or replace view vw_customer as select * from tbl_customer; View created. SQL>
And then to confirm whether the oracle view is now working or not again run the following select statement:
col cust_no format a30; col customer_type format a10; col cust_name format a25; col country format a15; col swift_code format a12; SQL> select * from vw_customer; CUST_NO CUSTOMER_T CUST_NAME COUNTRY SWIFT_CODE ---------- ---------- ------------------------- --------------- ------------ ########## I Messi John Dave USA ########## C Mereba Technology UK ########## B Swiss Bank Switzerland
Now as you can see it, it is working after we re-create it again.
Renaming views
Re-naming vw_customer view to vwcustomer :
Example: SQL> rename vw_customer to vwcustomer; Table renamed. SQL>
Viewing the SQL of a view where the view is based-on
Viewing the SQL of ‘vw_customer’ oracle view:
Example: SQL> col view_name format a11; SQL> col text format a66; SQL> select view_name, text 2 from dba_views 3 where view_name='VW_CUSTOMER'; VIEW_NAME TEXT ----------- ------------------------------------------------------------------ VW_CUSTOMER select "CUST_NO","CUSTOMER_TYPE","CUST_NAME","COUNTRY","SWIFT_CODE " from tbl_customer
Dropping oracle views
SQL> drop view vwcustomer; View dropped.
SHOWPIN 122 in 1 Precision Computer Screwdriver Kit, Laptop Screwdriver Sets with 101 Magnetic Drill Bits, Electronics Tool Kit Compatible for Tablet, PC, iPhone, PS4 Repair
30% OffPowerful Functions: 122 in 1 precision screwdriver set contains 98 screwdriver bits, 3 Torx TR screwdriver bits and 21 auxiliary tools, such as screwdriver handle, flexible shaft, extension rod, magnetizer, magnetic pad, cleaning brush etc. Own this ... read more
Compressed Air Duster, 3 Gear to 51000RPM Cordless Electric Air Duster, 6000mAh Rechargeable Air Blower with LED Light for Laptop Computer Keyboard Cleaning Pet Hair Crumb Replaces Compressed Air Can
$29.98 (as of 25/06/2024 15:32 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)TRUE 51000RPM: Our air duster adopters the most advanced motor, the maximum speed can reach TRUE 51000RPM, low energy consumption and longer lifespan. Great to help you deep cleaning, computers, Camera, electronic products, or for inflation. The cool... read more
Baseus USB C to HDMI Adapter, 4K@60Hz USB C Docking Station, 7 in 1 USB C Hub with 3 USB-A, PD 100W, TF/SD Card Reader, USB C Dock Compatible for iPhone 15/Mac/Dell/Acer/HP/ASUS/Steam Deck/Rog Ally
50% Off【7-in-1 Design】USB hub for laptop with 1 HDMI port, 3 USB3.0 ports, 1 SD/TF card slot, and 1 USB-C PD port, excellent connectivity to satisfy all your expansion needs at once, much higher efficiency at work. 【4K@60Hz Ultra Clarity】USB c to hdmi adapt... read more
ASUS Vivobook 15.6” FHD Laptop, AMD Ryzen 3 3250U, 8GB RAM, 128GB SSD, Windows 11 Home in S Mode, Transparent Silver, M515DA-WS33
$319.33 (as of 25/06/2024 15:28 GMT -04:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)【Day-to-day Productivity】: This Windows laptop equipped with Windows 11 Home in S Mode, an AMD Ryzen 3 Processor, and AMD Radeon graphics, is designed to deliver a fast and smooth experience. 【Enhanced Protection】: Crafted with exceptional durability... read more