Category Archives: SQL

Manage View object syntax in SQL

Manage the VIEW objects in SQL

VIEW is a virtual table, through which a selective portion of the data from one or more tables.
It is used to make the complex query in easy format. You make the view for complex query fetching data for reports function in Oracle.

Syntax:

CREATE VIEW view_name
AS
SELECT column_list FROM table_name [WHERE condition];

Example:

CREATE VIEW view_product
AS SELECT product_id, product_name FROM product;

DROP View

DROP View schema.view_name;

Example:
DROP View view_product;

Check view information present in the database

set line 200 pages 200
set long 5000
col view_name for a30
col owner for a10
select owner,view_name,text_length,text from dba_views where owner='HR';

Note: Text length should be smaller than long parameter specify in SET LONG clause otherwise you see trim view definition.