Strange ORA-00907: missing right parenthesis
oracle, sql
Solution
For whatever reason, the compiler does not accept the parens around the select in this cursor (as originally suggested by a_horse_with_no_name), although there is nothing inherently wrong with it as the following code compiles fine for me:
create or replace
procedure test is
cursor c is (select 1 from dual);
begin
null;
end;
However, if I remove the parens in your code, it does compile and refuses to compile for me otherwise. Sounds like a compiler bug to me.
Problem
The following code executes without error and produces the expected. ``` SELECT s.product_ID, s.price, s.quantity, d.wholesale_ID FROM ( SELECT product_ID, MIN(min_price) as price, quantity FROM ( SELECT SUM(ci.quantity) as quantity, MIN(d.price) as min_price, ci.product_ID as product_ID, wholesale_ID FROM "Customer order" co, "CO Item" ci, deal d WHERE co.processed = 0 AND co.ID = ci.order_ID AND d.product_ID = ci.product_ID GROUP BY ci.product_ID, d.wholesale_ID ORDER BY ci.product_ID ) GROUP BY product_ID, quantity ORDER BY product_ID ) s, deal d WHERE s.product_ID = d.product_ID AND s.price = d.price ORDER BY d.wholesale_ID ``` When I try to compile it into a procedure as a cursor, I get 'ORA-00907 missing right parenthesis' error. I'm using Oracle SQL Developer. ``` CREATE OR REPLACE PROCEDURE createWholesaleOrders IS CURSOR Curser IS( SELECT s.product_ID, s.price, s.quantity, d.wholesale_ID FROM ( SELECT product_ID, MIN(min_price) as price, quantity FROM ( SELECT SUM(ci.quantity) as quantity, MIN(d.price) as min_price, ci.product_ID as product_ID, wholesale_ID FROM "Customer order" co, "CO Item" ci, deal d WHERE co.processed = 0 AND co.ID = ci.order_ID AND d.product_ID = ci.product_ID GROUP BY ci.product_ID, d.wholesale_ID ORDER BY ci.product_ID ) GROUP BY product_ID, quantity ORDER BY product_ID ) s, deal d WHERE s.product_ID = d.product_ID AND s.price = d.price ORDER BY d.wholesale_ID -- < MISSING RIGHT PARENTHESIS HERE ); Pointer Curser%rowtype; current_wholesale NUMBER := -1; current_order NUMBER := -1; BEGIN OPEN Curser; LOOP FETCH Curser INTO Pointer; EXIT WHEN Curser%NOTFOUND; IF current_wholesale != Pointer.wholesale_ID THEN current_order := wholesale_order_sq.NEXTVAL; INSERT INTO "Wholesale order" (ID, wholesale_ID, "date") VALUES(current_order, Pointer.wholesale_ID, sysdate); current_wholesale := Pointer.wholesale_ID; END IF; INSERT INTO "WO Item" (ID, order_ID, product_ID, quantity, price) VALUES(wo_item_sq.NEXTVAL, current_order, Pointer.product_ID, Pointer.quantity, Pointer.price); UPDATE "CO Item" SET "CO Item"."wholesale-order_ID" = current_order WHERE "CO Item".order_ID IN ( SELECT "Customer order".ID FROM "Customer order" WHERE "Customer order".processed = 0 ) AND "CO Item".product_ID = Pointer.product_ID; END LOOP; UPDATE "Customer order" SET processed = 1 WHERE processed = 0; END; ``` Updated to include full code. There should be no errors on other places.