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

[thippes]

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

[DaPi]

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

[thippes]

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.

[gamyers]

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

[thippes]

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 ??

[DaPi]

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.

[DaPi]

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