This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 22, 2013 6:48 PM by 972355 RSS

FORCE option in VIEWS

972355 Newbie
Currently Being Moderated
Good Morning to Everyone ;

Once again i am starting new thread about [ VIEWS in Oracle].

MY REF_LINK :  https://forums.oracle.com/forums/thread.jspa?threadID=2501984&start=0&tstart=0

I am trying to create view with FORCE option

SQL>create view force_view as
*2 select empid ,email from emps;*

select empid ,email from emps
*+
ERROR at line 2:
ORA-00942: table or view does not exist

NOTE : There is no table in the name of  "emps"

What i learnt about  FORCE  OPTION :

*" Creating a view regardless (in any case) of whether or not the base tables exist "*

Oracle docs  saying little different ( what i learnt )

Really not understandable  from this link : "http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm"

Could anyone make little demo for force option ?

Thanks to Everyone !
  • 1. Re: FORCE option in VIEWS
    BSalesRashid Explorer
    Currently Being Moderated
    Hi,
    Where is the FORCE option written on your creation of your view ?
  • 2. Re: FORCE option in VIEWS
    972355 Newbie
    Currently Being Moderated
    I am very sorry , I found the error .

    SQL> create force view frorce_view
    *2 as*
    *3 select * from emps;*

    Warning: View created with compilation errors.

    Thanks !
  • 3. Re: FORCE option in VIEWS
    JohnWatson Guru
    Currently Being Moderated
    It works for me:
    SQL>
    SQL> create view v1 as select * from garbage;
    create view v1 as select * from garbage
                                    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> create force view v1 as select * from garbage;
    
    Warning: View created with compilation errors.
    
    SQL> 
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com

    Edited by: JohnWatson on Feb 22, 2013 7:34 PM
    I see it also worked for you! Sussed.
  • 4. Re: FORCE option in VIEWS
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    Please review:
    http://www.java2s.com/Code/Oracle/View/createforceview.htm
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8004.htm

    Regard
    Helios
  • 5. Re: FORCE option in VIEWS
    972355 Newbie
    Currently Being Moderated
    I have three tables (emp , dept , payroll)

    Instead of giving a long  join query again and again i created following view with read only option.

    SQL>create  view  v2  as
    select  e.eid, e.ename, e.edob, e.egender, e.equal ,
    d.dname,  d.dmanager ,
    p.esalary, p.egrade , p.ebonus
    from   emp  e ,  dept  d ,  payroll  p
    where  e.eid=d.eid  and  d.eid=p.eid  with READ ONLY;

    View created.

    can i rewrite above query with check option ?

    How can i rewrite this query effectively with check option.
  • 6. Re: FORCE option in VIEWS
    rp0428 Guru
    Currently Being Moderated
    >
    where e.eid=d.eid and d.eid=p.eid with READ ONLY;

    View created.

    can i rewrite above query with check option ?

    How can i rewrite this query effectively with check option.
    >
    Hmmm. . . Have you tried replacing the 'with READ ONLY' and using 'WITH CHECK OPTION' instead like the SQL Language doc says?
    >
    subquery_restriction_clause
    Use the subquery_restriction_clause to restrict the defining query of the view in one of the following ways:

    WITH READ ONLY Specify WITH READ ONLY to indicate that the table or view cannot be updated.

    WITH CHECK OPTION Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.
  • 7. Re: FORCE option in VIEWS
    972355 Newbie
    Currently Being Moderated
    I tried *"with Check option"*

    IS this Right ?

    SQL>create or replace view v3
    2 as
    3 select * from v2
    4 with check option;

    View created.



    SQL> insert into v3 values(1016 ,'chaya','150687','female','mba'
    *2 ,'hr','sandy','2000','e','2000');*

    insert into v3 values(1016 ,'chaya','150687','female','mba'
    *+
    ERROR at line 1:
    ORA-01779: cannot modify a column which maps to a non key-preserved table

    Thanks  rp for your information !
  • 8. Re: FORCE option in VIEWS
    972355 Newbie
    Currently Being Moderated
    I am checking some web links related to my error :-

    Here , they discussed some thing differently ..

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:548422757486

    * MAY I KNOW AM I RIGHT ? or ( SOMETHING GOES WRONG )*

    But i got this error when updating a view ( View  restricted with CHECK OPTION )

    ORA-01779: cannot modify a column which maps to a non key-preserved table

    Thanks ..
  • 9. Re: FORCE option in VIEWS
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    I am checking some web links related to my error :-

    Here , they discussed some thing differently ..

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:548422757486

    * MAY I KNOW AM I RIGHT ? or ( SOMETHING GOES WRONG )*

    But i got this error when updating a view ( View  restricted with CHECK OPTION )

    ORA-01779: cannot modify a column which maps to a non key-preserved table

    Thanks ..
    any error indicates something is wrong.
  • 10. Re: FORCE option in VIEWS
    972355 Newbie
    Currently Being Moderated
    *" any error indicates something is wrong."*

    So that i am asking help here to trouble shoot ..

    I don't know or i can't guess what's was the problem ?

    Could you please help me ?

    Thanks
  • 11. Re: FORCE option in VIEWS
    sb92075 Guru
    Currently Being Moderated
    969352 wrote:
    *" any error indicates something is wrong."*

    So that i am asking help here to trouble shoot ..

    I don't know or i can't guess what's was the problem ?

    Could you please help me ?

    Thanks
    I do not understand why you are wasting time investigating SQL that no professional would ever deploy.
    You are only asking for future problems try to establish a VIEW that is guaranteed to throw error.
  • 12. Re: FORCE option in VIEWS
    rp0428 Guru
    Currently Being Moderated
    >
    I don't know or i can't guess what's was the problem ?
    >
    But you're asking us to 'guess' what your view definitions and table definitions are.

    How can we troubleshoot code that we can't see?
  • 13. Re: FORCE option in VIEWS
    972355 Newbie
    Currently Being Moderated
    MY VIEW SOURCE

    SQL>create view v4 as
    select e.eid, e.ename, e.edob, e.egender, e.equal ,
    d.dname, d.dmanager ,
    p.esalary, p.egrade , p.ebonus
    from emp e , dept d , payroll p
    where e.eid=d.eid and d.eid=p.eid with CHECK OPTION;

    View created.

    SQL> insert into v4 values(1016 ,'chaya','150687','female','mba'
    +,'hr','sandy','2000','e','2000');+
    +2 insert into v4 values(1016 ,'chaya','150687','female','mba'+
    *+
    ERROR at line 1:
    ORA-01779: cannot modify a column which maps to a non key-preserved table
  • 14. Re: FORCE option in VIEWS
    sb92075 Guru
    Currently Being Moderated
    01779, 00000, "cannot modify a column which maps to a non key-preserved table"
    // *Cause: An attempt was made to insert or update columns of a join view which
    //         map to a non-key-preserved table.
    // *Action: Modify the underlying base tables directly.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points