Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Saturday, 2 April 2016

SQL Views

A view is a database object that is a logical representation of a table. It is delivered from a table but has no storage of its own and often may be used in the same manner as a table.
A view takes the output of the query and treats it as a table, therefore a view can be thought of as a stored query or a virtual table.


Simple view
Complex view

Simple view can be created from one table where as complex view can be created from multiple tables.


Provides additional level of security by restricting access to a predetermined set of rows and/or columns of a table.
Ø Hide the data complexity.
Ø Simplify commands for the user.


Read only view
View with group by
View with aggregate functions
View with rownum
Partition view
View with distinct
      SQL> Create view dept_v as select *from dept with read only;
      SQL> Create view dept_v as select deptno, sum(sal) t_sal from emp group by deptno;
      SQL> Create view stud as select rownum no, name, marks from student;
      SQL> Create view student as select *from student1 union select *from student2;
      SQL> Create view stud as select distinct no,name from student;

View with not null column  --  insert with out not null column not possible
                                                        --  update not null column to null is not possible
                                                             --  delete possible
View with out not null column which was in base table -- insert not possible                                                                                                                                      

                                         -- update, delete possible
View with expression -- insert , update not possible
                                               -- delete possible
View with  functions (except aggregate) -- insert, update not possible
                                                                              -- delete possible
View was created but the underlying table was dropped then we will get the message like “ view has errors ”.
View was created but the base table has been altered but still the view was with the               initial definition, we have to replace the view to affect the changes.
Complex view (view with more than one table) -- insert not possible
                                                                                                  -- update, delete possible (not always)


SQL> Create force view stud as select *From student;
-- Once the base table was created then the view is validated.


SQL> Create view stud as select *from student where marks = 500 with check option constraint
       - Insert possible with marks value as 500
       - Update possible excluding marks column
       - Delete possible


SQL> drop view dept_v;
Share this article :


  1. Hello There,

    In total awe…. So much respect and gratitude to you folks for pulling off such amazing blogs without missing any points on the SQL Views Kudos!

    I am facing an issue with DBMS_CHAIN execution.

    Issue :Even one of the step got Failed, remaining steps were processed without waiting for depended step SUCCESS.

    Just FYI: I created 2 chains i.e. Chain1 and Chain2 . Calling Chain2 as sub chain in Chain1.
    I attached the total script for your reference and below the run details.
    In the below run details , you can see STEP2 is in failed state but further steps(i.e. STEP3 of CHAIN1 and STEP1 , STEP2 of CHAIN2 got processed)
    Please suggest me on this(what i need to do to hold at FAILED step and how can i RERUN that FAILED step only (or) from Failed Steps).
    I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple.

    Many Thanks,

  2. Olà,

    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around #topic! I agree with you on lot of points!

    how to get the first non space character from a string in Oracle. Could you help me on this?

    Anyways great write up, your efforts are much appreciated.

    Many Thanks,


  3. Hi There,

    This is indeed great! But I think perhaps you are generally referring SQL Views which is getting unsustainable.

    I need some help from you experts on this stored procedure..

    There are 4-5 batch procedures that will call this stored procedure. The batch will call the stored procedure with 'I' parameter in the beginning of the batch process. The batch will call the stored procedure with 'O' parameter at the end of the batch process. The stored procedure should write the details into the auditing table.

    But great job man, do keep posted with the new updates.


  4. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  5. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.

  6. I am thankful for the blog post. Really looking forward to read more. Great.

  7. Awesome post. Really looking forward to read more. Much obliged.
    post photos on instagram from pc

  8. Social media contacts basically every aspect of these understudies' lives. It has turned into the essential way that the present understudies keep in contact with one another and the world. best free article spinner