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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.