Apex中创建Movie表主键触发器遇PLS-00049错误求助

Let's break down why you're hitting this error and how to fix it. The PLS-00049 error means your trigger can't recognize :NEW.MOVIE_ID as a valid column on your Movie table—even though you confirmed the column exists. The most common culprit here is case sensitivity in Oracle object names.

Why This Happens #

Oracle treats object names (tables, columns) as case-insensitive by default, unless you created them with double quotes. For example:

• If you created your table with CREATE TABLE "Movie" (...), the table name is stored as exactly "Movie" (mixed case) in the data dictionary.
• Similarly, if you defined the column with "movie_id" instead of MOVIE_ID, the column name is stored in lowercase.

When your trigger references :NEW.MOVIE_ID without double quotes, Oracle automatically converts it to uppercase. If your actual column name is stored in a different case (e.g., lowercase or mixed case), Oracle can't find it, hence the bind variable error.

Step-by-Step Fix #

1. Verify the actual column name case
   Run this query to check how your column is stored in the data dictionary:

   SELECT column_name 
   FROM user_tab_columns 
   WHERE table_name = 'Movie'; -- Use exact case if you created the table with quotes
   

   If the result returns movie_id (lowercase) instead of MOVIE_ID, that's the root issue.

2. Adjust the trigger to match the column's case
   Wrap the column name in double quotes in the trigger to match its exact stored case. For example:

   create or replace trigger "MOVIE_T1" 
   BEFORE insert on "Movie" 
   for each row 
   begin 
     :new."movie_id" := MOVIE_PK_SEQ.nextval; -- Match the column's actual case here
   end;
   /
   

   Alternatively, if you didn't use double quotes when creating the table/column (so they're stored in uppercase), you can simplify the trigger by removing all double quotes (Oracle will auto-convert to uppercase):

   create or replace trigger MOVIE_T1 
   BEFORE insert on MOVIE 
   for each row 
   begin 
     :new.MOVIE_ID := MOVIE_PK_SEQ.nextval; 
   end;
   /
   

3. Validate the sequence works
   Double-check your sequence is functional with this test:

   SELECT MOVIE_PK_SEQ.nextval FROM dual;
   

   If this returns a number, your sequence is set up correctly.

Key Takeaway #

Always be consistent with double quotes in Oracle. If you use them for creating tables/columns, you must use them every time you reference those objects. If you avoid double quotes, Oracle handles case automatically, so you don't have to worry about mismatches.

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