title: Why can't I use SELECT ... FOR UPDATE with aggregate functions?
body: <p>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):</p>
  
  <pre><code>SELECT Sum(cost)
  INTO v_cost_total
  FROM materials
  WHERE material_id &gt;=0
  AND material_id &lt;= 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 &gt;=0
  AND material_id &lt;= 10
  FOR UPDATE; 
  </code></pre>
  
  <p>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.</p>
  
  <p>Ideally, I would just use a FOR UPDATE clause on the first query, but when I try that, I get an error: </p>
  
  <pre><code>ORA-01786: FOR UPDATE of this query expression is not allowed
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>EDIT: It looks like SELECT ... FOR UPDATE can be used with analytic functions, as suggested by David Aldridge below. Here's the test script I used to prove this works. </p>
  
  <pre><code>SET serveroutput ON;
  
  CREATE TABLE materials (
      material_id NUMBER(10,0),
      cost        NUMBER(10,2)
  );
  ALTER TABLE materials ADD PRIMARY KEY (material_id);
  INSERT INTO materials VALUES (1,10);
  INSERT INTO materials VALUES (2,30);
  INSERT INTO materials VALUES (3,90);
  
  &lt;&lt;LOCAL&gt;&gt;
  DECLARE
      l_material_id materials.material_id%TYPE;
      l_cost        materials.cost%TYPE;
      l_total_cost  materials.cost%TYPE;
  
      CURSOR test IS
          SELECT material_id,
              cost,
              Sum(cost) OVER () total_cost
          FROM   materials
          WHERE  material_id BETWEEN 1 AND 3
          FOR UPDATE OF cost;
  BEGIN
      OPEN test;
      FETCH test INTO l_material_id, l_cost, l_total_cost;
      Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
      FETCH test INTO l_material_id, l_cost, l_total_cost;
      Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
      FETCH test INTO l_material_id, l_cost, l_total_cost;
      Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
  END LOCAL;
  /
  </code></pre>
  
  <p>Which gives the output: </p>
  
  <pre><code>1 10 130
  2 30 130
  3 90 130
  </code></pre>