Error Ora 01786
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Why can't I use SELECT … FOR UPDATE with aggregate functions? up vote 6 down vote favorite 1 I have an application where I find a sum() of a database column for a set of records and later use that sum in a separate query, similar to the following (made up tables, but the idea is the same): SELECT Sum(cost) INTO v_cost_total FROM materials WHERE material_id >=0 AND material_id <= 10; [a little bit of interim work] SELECT material_id, cost/v_cost_total INTO v_material_id_collection, v_pct_collection FROM materials WHERE material_id >=0 AND material_id <= 10 FOR UPDATE; However, in theory someone could update the cost column on the materials table between the two queries, in which case the calculated percents will be off. Ideally, I would just use a FOR UPDATE clause on the first query, but when I try that, I get an error: ORA-01786: FOR UPDATE of this query expression is not allowed Now, the work-around isn't the problem - just do an extra query to lock the rows before finding the Sum(), but that query would serve no other purpose than locking the tables. While this particular example is not time consuming, the extra query could cause a performance hit in certain situations, and it's not as clean, so I'd like to avoid having to do that. Does anyone know of a particular reason why this is not allowed? In my head, the FOR UPDATE clause should just lock the rows that match the WHERE clause - I don't see why it matters what we are doing with those rows. EDIT: It looks like SELECT ... FOR UPDATE can be used with analytic fu
not work correctly without it enabled. Please turn JavaScript back on and reload this page. All Places > JBoss AS > Persistence > Discussions Please enter a title. You can not post a blank message. Please type your message and try again. 6 Replies Latest reply on Jun 22, 2004 5:08 AM by cslenkey ORA-01786: FOR UPDATE of this query expression is not allowe cslenkey May 19, 2004 11:38 AM 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 forumthat there is a solution to use JOINs and SUBSELECT in querys.When we are running our application we get the:ORA-01786: FOR http://stackoverflow.com/questions/18455473/why-cant-i-use-select-for-update-with-aggregate-functions UPDATE of this query expression is not allowedOracle 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 atend of the SQL which command https://developer.jboss.org/thread/24131 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 54419Views Tags: none (add) This content has been marked as final. Show 6 replies 1. Re: ORA-01786: FOR UPDATE of this query expression is not al Frank Langelage May 19, 2004 3:42 PM (in response to cslenkey) One reason for the "for update" clause would be a config line in standardjbosscmp-jdbc.xml /jbosscmp-jdbc.xml:
use a FOR UPDATE clause http://www.oracle-error.com/8i/ora-01786.html on the result of a set expression involving GROUP BY, DISTINCT, UNION, INTERSECT, or MINUS. Action: Check the syntax, remove the FOR UPDATE clause, and retry the statement. Version 8i FOR UPDATE of this query expression is not allowed Cause: An attempt was made to error ora use a FOR UPDATE clause on the result of a set expression involving GROUP BY, DISTINCT, UNION, INTERSECT, or MINUS. Action: Check the syntax, remove the FOR UPDATE clause, and retry the statement. This entry was posted in Oracle 8i Errors, Oracle 9i Errors, Oracle error ora 01786 Database Server Messages. Bookmark the permalink. Leave a Reply Cancel reply Your email address will not be published. Required fields are marked *Comment Name * Email * Website Currently you have JavaScript disabled. In order to post comments, please make sure JavaScript and Cookies are enabled, and reload the page. Click here for instructions on how to enable JavaScript in your browser. Search for: Recent Commentsabhi on ORA-24237oracle-error on ORA-24237abhi on ORA-24237Mike TAILOR on ORA-12154oracle-error on ORA-39776Support services ? Some messages recommend contacting Oracle Support Services to report a problem. Please go here for more information : Oracle Support Archives April 2015 Categories EXP Messages Oracle 10g Errors Oracle 11g Errors Oracle 8i Errors Oracle 9i Errors Oracle Database Server Messages Recent Posts XML-30017 XML-30016 XML-30014 XML-30013 XML-30012 Oracle-Error.com 2016Zerif Lite powered by WordPress