Oracle INSERT子查询语句执行报错ORA-00907求助

Hey Norman, I see exactly what's causing that missing right parenthesis error—let's break it down and fix it quickly.

The Root Cause #

Your SQL has the WITH CHECK OPTION clause in the wrong position. This clause is an option for inline views (the subquery you're inserting into), and it needs to come after the WHERE clause, not before it. In your original code, you placed it between FROM PHYSICIAN and WHERE LICENSE_NO..., which throws off Oracle's parser and leads to the ORA-00907 syntax error.

Corrected SQL Statement #

Here's the fixed version with the clause in the proper place:

INSERT INTO (
    SELECT PHYSICIAN_ID, LAST_NAME, FIRST_NAME, LICENSE_NO, HIRE_DATE 
    FROM PHYSICIAN 
    WHERE LICENSE_NO BETWEEN 1 AND 200
    WITH CHECK OPTION
) VALUES (PHY_NUM_SEQ.NEXTVAL, '&LNAME', '&FNAME', &LNO, SYSDATE);

Why This Works #

• WITH CHECK OPTION ensures that any rows inserted or updated through the inline view adhere to the WHERE clause condition (in this case, LICENSE_NO BETWEEN 1 AND 200). Placing it at the end of the subquery tells Oracle to apply this constraint correctly without breaking the query structure.
• Your sequence PHY_NUM_SEQ is working, and the table structure matches the columns you're inserting, so once the syntax is fixed, the insert should run successfully with your test values (BARRETT, NORMAN, 1).

内容的提问来源于stack exchange，提问作者nbarr007