ORA-00907 On a delete statement

Question

I'm trying to delete a random record from a table but I get an ORA-00907: missing right parenthesis error and I can't find what's wrong.

Here's my statement:

DELETE FROM participation WHERE ROWID IN (
    SELECT ROWID FROM participation
        WHERE ROWNUM = 1
        ORDER BY DBMS_RANDOM.RANDOM);

And here's the table:

CREATE TABLE participation (
    matrEmp     NUMBER(4)     NOT NULL,
    codeProj     VARCHAR(14)   NOT NULL,
    fonction VARCHAR2(255) NOT NULL,
    CONSTRAINT pk_part PRIMARY KEY (matrEmp, codeProj)
);

Answer 1

ROWNUM filters data before you use it, so it won't work. For older versions you can choose a random pk like,

DELETE FROM participation s3
WHERE (s3.matrEmp, s3.codeProj) IN (SELECT s2.matrEmp,
                                         s2.codeProj
                                    FROM (SELECT RANK() over(ORDER BY DBMS_RANDOM.value) random_rank,
                                                 s1.matrEmp,
                                                 s1.codeProj
                                            FROM participation s1) s2
                                   WHERE random_rank = 1)

If you use Oracle 12c, you could use row limiting

DELETE FROM participation s3
 WHERE (s3.matrEmp, s3.codeProj) IN (SELECT s1.matrEmp,
                                        s1.codeProj
                                   FROM participation s1
                                  ORDER BY DBMS_RANDOM.value 
                                  FETCH FIRST 1 ROWS ONLY);