1 2 Previous Next 16 Replies Latest reply: Feb 22, 2013 8:48 PM by 972355 RSS

    FORCE option in VIEWS

    972355
      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
          Hi,
          Where is the FORCE option written on your creation of your view ?
          • 2. Re: FORCE option in VIEWS
            972355
            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
              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
                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
                  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
                    >
                    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
                      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
                        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
                          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
                            *" 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
                              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
                                >
                                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
                                  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
                                    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