Missing parenthesis in oracle sql developer-CodePudding

Hello so i was coding a procedure for my database but i got stuck when i the compiler(Oracle SQL Developer) started telling me that

there is a missing right parenthesis

but there is no missing parenthesis as much as I observe.

Here is the procedure:

CREATE OR REPLACE PROCEDURE generer_listes_preferees (
  p_id_user IN UTILISATEUR."id_user"%TYPE,
  p_films OUT SYS_REFCURSOR
)
AS
  -- Déclarer les variables locales
  CURSOR c_films IS
    SELECT *
    FROM OBJET
    WHERE "categorie_obj" LIKE 'Films' AND "id_objet" IN (
      SELECT "id_objet"
      FROM AVIS
      WHERE "id_user" = p_id_user
      ORDER BY "note" DESC, "date_avis" DESC
    )
    FETCH FIRST 10 ROWS ONLY;
BEGIN
  -- Générer la liste de films préférés
  OPEN p_films FOR SELECT * FROM TABLE(c_films);
END;

What could be the issue?

CodePudding user response：

The procedure does not compile because the query is not valid. In particular, Oracle is looking for the closing parenthesis to finish the subquery, but it finds an unexpected ORDER BY clause instead:

SELECT *
FROM OBJET
WHERE "categorie_obj" LIKE 'Films' AND "id_objet" IN (
  SELECT "id_objet"
  FROM AVIS
  WHERE "id_user" = p_id_user
  ORDER BY "note" DESC, "date_avis" DESC
)

(Demo)

The error message could indeed be more useful, but the problem with incorrect code is that the parser can't really figure out your intentions.

Even if the ORDER BY clause was allowed, it wouldn't accomplish anything. The IN () operator is not sensitive to order. I guess you meant to order the main outer query.

It's also worth noting that "categorie_obj" LIKE 'Films' is the same as "categorie_obj" = 'Films'. Performance overhead (it has to parse the expression looking for wildcards) is for sure negligible, but I think it's more readable to just be explicit.

On a side note, I'd advise against double quoting identifiers. The only feature it provides is to hard-code the table/column case, so you're forced to type the quotes and the exact case every time since expressions WHERE categorie_obj or WHERE CATEGORIE_OBJ will no longer work.