DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY etc..

  1. #1
    Join Date
    Apr 2005
    Posts
    3

    Exclamation ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY etc..

    Hello All,

    I am using JDK 1.4.2. I am running oracle 8.1.7.

    I am stuck with the following error. When i try to update the view (CCS_RESOURCE_LIMITATION_V) thru Java screens.

    java.sql.SQLException: ORA-02014: cannot select FOR UPDATE from view with DISTIN
    CT, GROUP BY, etc.

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:
    643)
    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.jav
    a:1674)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
    nt.java:1870)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
    edStatement.java:363)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePrepare
    dStatement.java:314)
    at UserScreens.pluginpanel.ProfileUtility.select(ProfileUtility.java:314
    )
    at UserScreens.pluginpanel.ProfileUtility.readAndLock(ProfileUtility.jav
    a:158)
    at com.eservglobal.ccs.EditResourceLimitsDialog.doPlugInPanelSave(EditRe
    sourceLimitsDialog.java:608)
    at com.eservglobal.ccs.EditResourceLimitsDialog.savePressed(EditResource
    LimitsDialog.java:571)

    Here is how the view is created

    create or replace view CCS_RESOURCE_LIMITATION_V
    as select * from ccs_resource_limitation
    where (ccs_resource_limitation.acs_cust_id=acs.effectiveCustomer) and acs.realPermission >= 6;

    create public synonym CCS_RESOURCE_LIMITATION_V for CCS_RESOURCE_LIMITATION_V;


    When i try to update the view manually thru SQLPLUS i will be getting the following error

    update ccs_resource_limitation_v set MAX_RATE_TABLES =10;
    update ccs_resource_limitation_v set MAX_RATE_TABLES =10
    *
    ERROR at line 1:
    ORA-20016: User's terminal is not defined.
    ORA-06512: at "SMF.SMF_SECURITY_INTL", line 60
    ORA-06512: at "CCS_ADMIN.CCS_RESOURCE_LIMITATION_ABT", line 8
    ORA-04088: error during execution of trigger
    'CCS_ADMIN.CCS_RESOURCE_LIMITATION_ABT'

    HOw ever i am able to get rid of the error i am getting thru SQLPLUS by making some minor changes in the procedure body of SMF_SECURITY_INTL. But i am not convinced with the changes i made.
    But how over i am still getting the same error for view updation thru Java screens.

    Any help in resolving this error will be highly appreciable.

    Thanks

    Thippeswamy H

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY etc..

    Originally posted by thippes
    Here is how the view is created

    create or replace view CCS_RESOURCE_LIMITATION_V
    as select * from ccs_resource_limitation
    where (ccs_resource_limitation.acs_cust_id=acs.effectiveCustomer) and acs.realPermission >= 6;
    I don't think that can be right - the table acs is not in the FROM clause you posted.

    AFAIK, an update via a view is only permitted if it would be possible to target exactly one row of one table for the change (you may choose to update more). Oracle will use its knowledge about constraints (e.g. primary keys) to evaluate this.

    http://download-west.oracle.com/docs...5a.htm#2065830

  3. #3
    Join Date
    Apr 2005
    Posts
    3

    ORA-02014

    Hi,

    I have other views created using the same type of view creation such as

    create or replace view CCS_RESOURCE_LIMITATION_V
    as select * from ccs_resource_limitation
    where (ccs_resource_limitation.acs_cust_id=acs.effectiveCustomer) and acs.realPermission >= 6;

    But only the above view is giving me the problem when i try to update thru GUI. I have Instead-of-triggers in my database which will be fired after each insertion or deletion or updation in the database.

  4. #4
    Join Date
    Feb 2005
    Posts
    158

    Re: Re: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY etc..

    Originally posted by DaPi
    I don't think that can be right - the table acs is not in the FROM clause you posted.

    acs could be a package (access control system ?) and the effectiveCustomer/realPermission functions in that package (or acs could be a schema and they are functions in the schema).

    "ORA-02014: cannot select FOR UPDATE... "
    JAVA is trying a select for update (ie trying to lock rows) but there's no way Oracle can know which rows to lock. The INSTEAD OF trigger may do all sorts of things.
    You need to determine what locks you want and how to take them

  5. #5
    Join Date
    Apr 2005
    Posts
    3
    Hi,

    ACS is table.

    There is a statement in java code which reads and locks the view. So reading and locking is done by the java code. This view is working perfectly in 9i but i am using oracle 8.1.7. Does this oracle version make's a difference ??

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Re: Re: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY etc

    Originally posted by gamyers
    acs could be a package (access control system ?) and the effectiveCustomer/realPermission functions in that package (or acs could be a schema and they are functions in the schema).
    True. I missed that. But that must ensure that the view is not updateable.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by thippes
    ACS is table.
    You have to be able to guess right at times

    But I did miss the bit about a trigger

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width