ORA-01786: FOR UPDATE of this query expression is not allowe

[cslenkey]

Hi,

We have an EJB application wich is running now in JBoss-3.2.2RC3.
I would like to use this on JBoss-3.2.4RC1 because I have read in the forum
that there is a solution to use JOINs and SUBSELECT in querys.
When we are running our application we get the:
ORA-01786: FOR UPDATE of this query expression is not allowed
Oracle error.
When I analized the server log of 3.2.3 and 3.2.4 I found the difference.
The SQL select which was generated by the previous version is the next:
SELECT DISTINCT t0_d.berletId, t0_d.berletNyk, t0_d.szeriaszam,
t0_d.megnevezes, t0_d.ervenyessegTol, t0_d.ervenyessegIg,
t0_d.eloadasSzam FROM BERLET t0_d WHERE ((t0_d.berletNyk = ?)) OR
((t0_d.berletNyk = ?))

The new version generates the same SQL code with an FOR UPDATE at
end of the SQL which command generates the ORA-1786 error.

Every SQL has the same FOR UPDATE ending which comes from a finder method.

I try to run this SQL in a simple SQL prompt but same situation.
What has changed? Why does differ the two SQL? I have not change any configuration of JBoss?
Thanks,
Csaba

[lafr]

One reason for the "for update" clause would be a config line in standardjbosscmp-jdbc.xml /jbosscmp-jdbc.xml:
<row-locking>true</row-locking>

[cslenkey]

Thanks for your answer.
Of course we use <row-locking>false</row-locking> setting.
That is the reason why I do not understand what has changed between the two versions.
Csaba

[cslenkey]

DISTINCT and FOR UPDATE are incompatible. This the reason of the Oracle-01786 error message.
But why does generate different SQL code JBoss3.2.2RC and 3.2.4?

Can anyone help?????
Csaba

[aloubyansky]

Unless there <row-locking>true</row-locking> we don't append FOR UPDATE.

[cslenkey]

Alex,

We use <row-locking>false</row-locking> setting in standardjbosscmp-jdbc.xml as you can see in my comment at Jun 6, 2003 10:19 AM

Csaba

[cslenkey]

Alex,

You are right! Mea culpa!

In standardjbosscmp-jdbc.xml we use <row-locking>false</row-locking>
But in jbosscmp-jdbc.xml which is generated by the generator of Software Through Pictures application we generate TRUE!!!!
I was mindless...
Thx