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
Re: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY etc..
Quote:
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
Re: Re: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY etc..
Quote:
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
Re: Re: Re: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY etc
Quote:
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.