Ora-00907: Missing Right Parenthesis

ora -00907 -missing right parenthesis

I'm not sure that placing the entire select clause inside parentheses is valid. I would have written your query as:

INSERT INTO table2 (column1, column2, ..., columnN)
SELECT column1, column2, ..., columnN
FROM table1
WHERE <condition>;

Oracle - ORA-00907: missing right parenthesis

Obvious error is in listagg (missing concatenation):

No : listagg (path, bildnr '.' filtyp)
Yes: listagg (PATH, bildnr || '.' || filtyp)

The rest should be OK (as far as syntax is concerned; can't tell whether query will (or will not) return what you want).

---

Also, perhaps you'd rather switch to JOINs instead of using bunch of nested subqueries, e.g.

  SELECT u.artnr,
         LISTAGG (u.PATH, u.bildnr || '.' || u.filtyp)
            WITHIN GROUP (ORDER BY u.bildnr) AS sokvag
    FROM kund u
         JOIN kundorder o ON u.knr = o.knr
         JOIN orderrad r ON r.ordnr = o.ordnr
         JOIN artikel a ON a.artnr = r.artnr
         JOIN artikelbild b ON b.bildnr = a.bildnr
ORDER BY u.artnr;

---

As of ORA-30496: Argument should be a constant: apparently, that's true.

Code you'd like to run:

SQL> select listagg(e.ename, e.job ||'.'|| to_char(e.deptno))
  2           within group (order by null) result
  3  from emp e
  4  where rownum <= 3;
select listagg(e.ename, e.job ||'.'|| to_char(e.deptno))
                        *
ERROR at line 1:
ORA-30496: Argument should be a constant.

Let's try to fool Oracle and "prepare" the separator:

SQL> with temp as
  2    (select e.ename, e.job ||'.'||to_char(e.deptno) separator
  3     from emp e
  4     where rownum <= 3
  5    )
  6  select listagg(t.ename, t.separator)
  7           within group (order by null)
  8  from temp t;
select listagg(t.ename, t.separator)
                        *
ERROR at line 6:
ORA-30496: Argument should be a constant.

Still no luck. But if a separator really is a constant (comma in my example), then it works:

SQL> select listagg(e.ename ||': ' || e.job, ', ')
  2           within group (order by null) result
  3  from emp e
  4  where rownum <= 3;

RESULT
------------------------------------------------------------------------
ALLEN: SALESMAN, SMITH: CLERK, WARD: SALESMAN

SQL>

ORA-00907: missing right parenthesis

The errors are:

• INT or NUMBER(4,0) and not INT(4)
• You cannot have a unique key and a primary key on the same column.
• An inline foreign key just need the REFERENCES keyword and does not need FOREIGN KEY.

Other issues:

• You probably don't want actor name/surname as fixed-length CHAR strings and want to us variable-length VARCHAR2.
• You probably want gender to be a code from a fixed list (which can be as long or as short as you find appropriate to describe the actors) rather than as a string.
• You probably don't want to have an AGE column as that will be out of date as soon as the first birthday of an actor occurs; instead have a DATE_OF_BIRTH column that is a DATE data type and then you can calculate the age as and when necessary.
• Using the table name as a prefix for every column is a waste of key strokes; you would be better to just name the columns for what they are without the prefix. Similar with fk as a suffix.
• If you are using a NUMBER(7,0) for id values then you don't need to check that it is less than or equal to 9999999 as it is impossible to be a greater value; however, you can have zero or negative values so the check constraint for the lower bound may still be valid.
• If you are using Oracle 12c or later then you should probably be using an IDENTITY column for the id values unless you are taking the id values from a 3rd party.

CREATE TABLE Movie (
    id       NUMBER(7,0) NOT NULL,
    name     varchar2(50) NOT NULL ,
    director varchar2(50) NOT NULL ,
    year     NUMBER(4,0) NOT NULL ,
    duration NUMBER(3,0),
    language varchar2(15) ,
    rating   number(4,2) , 
    PRIMARY KEY(id),
    CONSTRAINT movie_id_checker CHECK(id>0)
);

CREATE TABLE Casts (
    movie_id INT REFERENCES Movie(id) ,
    id       NUMBER(7,0) NOT NULL, 
    fullname varchar2(50) NOT NULL ,
    role     varchar2(50) ,
    PRIMARY KEY(id),
    CONSTRAINT cast_id_checker CHECK(id>0)
);

CREATE TABLE Actor (
    cast_id       NUMBER(7,0) REFERENCES Casts(id) ,
    id            NUMBER(7,0) NOT NULL,
    name          VARCHAR2(30) ,
    surname       VARCHAR2(25) ,
    gender        CHAR(1)
                  CHECK ( gender IN ( 'M', 'F', 'X', 'Y', 'Z' ) ),
    date_of_birth DATE
                  CONSTRAINT real_age_check CHECK(date_of_birth >= DATE '1870-01-01' ),
    PRIMARY KEY(id),
    CONSTRAINT actor_id_checker CHECK(id>0)
);

db<>fiddle here

ORA-00907: missing right parenthesis, and nothing is working

You can't specify precision for the int data type in Oracle. You are using it for column id_a. int is shorthand for number(38,0).

To get the desired effect, replace that with number(11,0) - which means precision of 11 digits, of which zero decimal places.

Other than that, you would be well advised, in Oracle, to use varchar2(n) rather than varchar(n). For a very long time Oracle has warned us that in future releases varchar may be used for something else (even though that hasn't happened and is unlikely to happen).

To explain the error message: Right after the parser reads id_a int it expects (optional space and) a comma, or a constraint, etc. What it does not expect (since it makes no sense in that position) is an opening parenthesis, as you have in (11). The error handling is written to assume that at that point the create table statement should have ended - with a closing parenthesis. It doesn't find it, so it complains about that.

That's why you saw so many unrelated mentions of "missing right parenthesis" errors - they are often thrown by the parser when it finds something unexpected in a statement. The parser doesn't know what the real error is - it just sees something that doesn't make sense somewhere, and if at that point a closing parenthesis would have ended a valid statement, it throws that error.

---

Related Topics