This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 22, 2013 11:17 AM by 972355 RSS

ORA-01731: circular view definition encountered

972355 Newbie
Currently Being Moderated
Good Morning To all ;

I created a view for emp1 table.

SQL> select   view_name , text   from  dba_views  where VIEW_NAME='EMP1_VIEW';          

VIEW_NAME      TEXT

EMP1_VEW       select  empid , ename , qual,  dept  from  emp1
where  empid  between  1000 and  1010

Now  i tried to  use create or replace option

SQL>create or replace  view emp1_view as
*2 select * from emp1_view with read only;*
create or replace  view emp1_view as
*+
ERROR at line 1:
ORA-01731: circular view definition encountered


Actually i want to make read only  view (emp1_view) ;  without  doping  ;
  • 1. Re: ORA-01731: circular view definition encountered
    Aman.... Oracle ACE
    Currently Being Moderated
    What's the db version? The error is not being listed in 11.2.0.1 for me and neither any of it's details are there in the documentation of 11.2.
    [oracle@edmtr2p0-orcl ~]$ oerr ORA 01731
    01731, 00000, "circular view definition encountered"
    // *Cause:
    // *Action:
    [oracle@edmtr2p0-orcl ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 22 08:31:43 2013
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options
    
    SQL> create or replace view v1 as select * from scott.emp;
    
    View created.
    
    
    SQL>  create or replace view v1  as select * from scott.emp with read only;
    
    View created.
    
    SQL> create or replace view v as select * from v2;
    create or replace view v as select * from v2
                                              *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> create or replace view v as select * from v1;
    
    View created.
    
    SQL> create or replace view v3 as select * from v1 with read only;
    
    View created.
    
    SQL> 
    Aman....
  • 2. Re: ORA-01731: circular view definition encountered
    rp0428 Guru
    Currently Being Moderated
    >
    SQL>create or replace view emp1_view as
    *2 select * from emp1_view with read only;*
    >
    You are trying to create view 'emp1_view' with a query that selects from 'emp1_view'; you can't create an object that is based on the object you are creating.
    SQL> create view emp_view as select * from emp;
    
    View created.
    
    SQL>create or replace view emp_view as select * from emp_view
    
    ORA-01731: circular view definition encountered
  • 3. Re: ORA-01731: circular view definition encountered
    972355 Newbie
    Currently Being Moderated
    To Aman :

    SQLPlus: Release 10.2.0.1.0 - Production on Fri Feb 22 06:13:00 2013*

    * rp please see here ..*

    SQL> create force view emp1_views as
    *2 select * from emp1_view with read only;*

    View created.

    SQL> select view_name , text from dba_views
    *2 where view_name='EMP1_VIEWS';*

    VIEW_NAME       TEXT
    EMP1_VIEWS   select "EMPID","ENAME","QUAL","DEPT" from emp1_view with read only
  • 4. Re: ORA-01731: circular view definition encountered
    Aman.... Oracle ACE
    Currently Being Moderated
    rp0428 wrote:
    Well you can't create an object that is based on the object you are creating.
    SQL> create view emp_view as select * from emp;
    
    View created.
    
    SQL>create or replace view emp_view as select * from emp_view
    
    ORA-01731: circular view definition encountered
    Rp,

    Just tried to search for this in the documentation and found this,
    http://docs.oracle.com/cd/E11882_01/appdev.112/e11822/adobjvew.htm#ADOBJ7331

    Regards
    Aman....
  • 5. Re: ORA-01731: circular view definition encountered
    rp0428 Guru
    Currently Being Moderated
    >
    * rp please see here ..*

    SQL> create force view emp1_views as
    *2 select * from emp1_view with read only;*

    View created.

    SQL> select view_name , text from dba_views
    2 where view_name='EMP1_VIEWS';

    VIEW_NAME TEXT
    EMP1_VIEWS select "EMPID","ENAME","QUAL","DEPT" from emp1_view with read only
    >
    No - that now uses the plural 'EMP1_VIEWS' in the CREATE and the singular 'emp1_view' in the SELECT. That IS NOT what you first posted.

    * 969352 needs to see here ..* - This is an exact copy of what you posted.
    >
    Now i tried to use create or replace option

    SQL>create or replace view emp1_view as
    *2 select * from emp1_view with read only;*
    create or replace view emp1_view as
    *
    ERROR at line 1:
    ORA-01731: circular view definition encountered
    >
    You use 'emp1_view' in the CREATE statement and also use 'emp1_view' in the SELECT statement. What you posted DOES NOT use the pluran 'EMP1_VIEWS' that you are now posting.

    So I don't know what you mean by 'see here'. What you posted now is not what you posted before.

    Go read your own post again and explain why we should 'see here'.
  • 6. Re: ORA-01731: circular view definition encountered
    972355 Newbie
    Currently Being Moderated
    SQL> select * from emp1_views;

    EMPID ENAME QUAL DEPT
    ---------- --------------- --------------- ---------------
    1000 sandy mca it
    1002 sona mba hr
    1005 maya msc it
    1009 sony mba hr
    1003 chris mca it

    SQL> update emp1_views set ename='SANDYA';
    update emp1_views set ename='SANDYA'
    ***
    ERROR at line 1:
    ORA-01733: virtual column not allowed here

    I am checking given link. please help me to solve this error.
  • 7. Re: ORA-01731: circular view definition encountered
    Aman.... Oracle ACE
    Currently Being Moderated
    969352 wrote:
    To Aman :

    SQLPlus: Release 10.2.0.1.0 - Production on Fri Feb 22 06:13:00 2013*
    And you do know that's long time back obsoleted, don't you?
    * rp please see here ..*

    SQL> create force view emp1_views as
    *2 select * from emp1_view with read only;*

    View created.

    SQL> select view_name , text from dba_views
    *2 where view_name='EMP1_VIEWS';*

    VIEW_NAME       TEXT
    EMP1_VIEWS   select "EMPID","ENAME","QUAL","DEPT" from emp1_view with read only
    See Rp's reply for it!

    Aman....
  • 8. Re: ORA-01731: circular view definition encountered
    rp0428 Guru
    Currently Being Moderated
    >
    Just tried to search for this in the documentation and found this,
    >
    That doc section is talking about object views and doesn't apply to OPs example.

    OP is using the SAME object (same name) in the CREATE VIEW as in the SELECT. That is OPs circular reference.

    The object views discussed in the doc never refer directly to the same object. Each object refers to the 'other' object and one of the objects is an object view.
  • 9. Re: ORA-01731: circular view definition encountered
    rp0428 Guru
    Currently Being Moderated
    >
    please help me to solve this error.
    >
    Solve the error? You can solve the 'error' by not trying to update the READ ONLY view that you created.

    Why do you think you should be able to?
  • 10. Re: ORA-01731: circular view definition encountered
    972355 Newbie
    Currently Being Moderated
    Oh ! , I think you are getting confused.

    emp1_view => i tried with read only option ( but getting error : ORA-01731: circular view definition encountered )
    emp1_views => i tried using *"FORCE"* option to make read only. (in this method , i created a view ; but getting error ORA-01733: virtual column not allowed here).

    What's your confusion here ?

    I tried with other options - that's it !

    please note rp : I know ,  i am  not a paid subscriber.  i  don't have any rights to force anyone.
    but i don't know why i am getting serious (angry) reply ?
  • 11. Re: ORA-01731: circular view definition encountered
    Aman.... Oracle ACE
    Currently Being Moderated
    rp0428 wrote:
    >
    Just tried to search for this in the documentation and found this,
    >
    That doc section is talking about object views and doesn't apply to OPs example.

    OP is using the SAME object (same name) in the CREATE VIEW as in the SELECT. That is OPs circular reference.

    The object views discussed in the doc never refer directly to the same object. Each object refers to the 'other' object and one of the objects is an object view.
    Yes yes, just thought I would share this since I didn't know about it before :) . It's certainly not what the OP is doing and your demo answer already explained the issue pretty well!

    Regards
    Aman....
  • 12. Re: ORA-01731: circular view definition encountered
    rp0428 Guru
    Currently Being Moderated
    >
    Oh ! , I think you are getting confused.

    emp1_view => i tried with read only option ( but getting error : ORA-01731: circular view definition encountered )
    emp1_views => i tried using "FORCE" option to make read only. (in this method , i created a view ; but getting error ORA-01733: virtual column not allowed here).

    What's your confusion here ?

    I tried with other options - that's it !

    please note rp : I know , i am not a paid subscriber. i don't have any rights to force anyone.
    but i don't know why i am getting serious (angry) reply ?
    >
    You aren't getting any 'angry' replies. You are getting replies that try to emphasize what you don't seem to understand.

    You CANNOT UPDATE A READ ONLY VIEW!

    There - that wasn't angry - that bold is for emphasis. READ ONLY means just that: READ ONLY. Please explain what it is about READ ONLY that makes you think you should be able to update it.

    You are the one that is confused. You posted a CREATE VIEW that had a circular reference and then, when your mistake was pointed out to you you changed the VIEW and tried to make us think we were the ones that were wrong.

    And now you think that the FORCE option makes a view read only. The FORCE option doesn't make a view read only. Why do you think it does?

    The clause WITH READ ONLY in your second view definition is what makes the view read only
    EMP1_VIEWS select "EMPID","ENAME","QUAL","DEPT" from emp1_view with read only 
    The FORCE option causes Oracle to create the view even if the referenced object types DO NOT EXIST.

    See the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm
    >
    FORCE
    Specify FORCE if you want to create the view regardless of whether the base tables of the view or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.

    If the view definition contains any constraints, CREATE VIEW ... FORCE will fail if the base table does not exist or the referenced object type does not exist. CREATE VIEW ... FORCE will also fail if the view definition names a constraint that does not exist.

    NO FORCE
    Specify NOFORCE if you want to create the view only if the base tables exist and the owner of the schema containing the view has privileges on them. This is the default.
    >
    Note that there is no mention of READ ONLY in that section.

    READ ONLY has its own section in that doc
    >
    WITH READ ONLY Specify WITH READ ONLY to indicate that the table or view cannot be updated.
    >
    Did you notice this: 'the table or view cannot be updated'?

    And these are the notes for Updatable views:
    >
    Notes on Updatable Views The following notes apply to updatable views:

    An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

    To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

    •Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.

    •The view must not contain any of the following constructs:

    A set operator
    A DISTINCT operator
    An aggregate or analytic function
    A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
    A collection expression in a SELECT list
    A subquery in a SELECT list
    A subquery designated WITH READ ONLY
    >
    Did you notice the last bullet?
    >
    •The view must not contain any of the following constructs:
    . . .
    A subquery designated WITH READ ONLY
    >
    It says an updatable view 'must not contain any of the following constructs' and then lists 'a subquery designated WITH READ ONLY' as one of the constructs.

    You cannot update a READ ONLY view.
    You cannot update a READ ONLY view.
    You cannot update a READ ONLY view.
    You cannot update a READ ONLY view.
    You cannot update a READ ONLY view.
    >
    SQL> update emp1_views set ename='SANDYA';
    update emp1_views set ename='SANDYA'
    ***
    ERROR at line 1:
    ORA-01733: virtual column not allowed here
    >
    This is the description for that error
    >
    ORA-01733: virtual column not allowed here
    Cause: An attempt was made to use an INSERT, UPDATE, or DELETE statement on an expression in a view.
    Action: INSERT, UPDATE, or DELETE data in the base tables, instead of the view.
    >
    The use of the word 'virtual' in the error message is confusing but the 'cause' explains it means 'an expression in a view'.

    But it is telling you the same thing I said above.
  • 13. Re: ORA-01731: circular view definition encountered
    972355 Newbie
    Currently Being Moderated
    I AGREE ... READ ONLY CAN'T BE UPDATE.
    READ ONLY means just that: READ ONLY.

    Please explain what it is about READ ONLY that makes you think you should be able to update it.

    I was trying to check
    " How oracle responds when trying to update read-only table ..

    *Oracle throwed following error *
    *ERROR at line 1:*
    *ORA-01733: virtual column not allowed here*

    but i expected error statement like below statement ;

    *Oracle did n't say anything like cannot update a READ ONLY view.*
    but oracle says
    *ORA-01733: virtual column not allowed here*

    *I am very clear. THANKS rp and aman for all clean replies ..*
  • 14. Re: ORA-01731: circular view definition encountered
    972355 Newbie
    Currently Being Moderated
    You aren't getting any 'angry' replies. You are getting replies that try to emphasize what you don't seem to understand.

    Ok - Thanks. I got answer . Thanks ..
1 2 Previous Next

Legend

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