存储过程在TOAD运行正常但SQL*Plus报错绑定变量NEW未声明

原因说明 #

报错是SQLPlus和TOAD的语句执行规则差异导致的：TOAD会自动识别PL/SQL对象（触发器、存储过程等）的结束边界，无需额外标记即可执行；但SQLPlus需要显式的执行标记才能正确提交缓冲区中的PL/SQL代码，原脚本没有为触发器创建语句添加执行标记，导致SQL*Plus误识别语句边界，抛出NEW关键字相关错误。

解决方案 #

• 保留已有的SET SQLBLANKLINES ON、SET DEFINE OFF配置，额外添加SET SQLTERMINATOR ON确保语句终止符被正确识别
• 在每一段PL/SQL代码（包括前面的两个匿名块、最后的触发器）的END;后面，单独加一行斜杠/，显式通知SQL*Plus执行当前缓冲区的代码
• 若仍有报错，可将:new改为全大写:NEW，适配部分旧版本Oracle的大小写校验规则

修改后可直接在SQL*Plus运行的完整代码 #

SET SQLBLANKLINES ON;
SET DEFINE OFF;
SET SQLTERMINATOR ON;

BEGIN 
DECLARE
  cnt_master   NUMBER;
BEGIN
   SELECT COUNT (1)
   INTO cnt_master
   FROM all_objects
   WHERE
         UPPER (object_type) = UPPER ('TABLE')
         AND UPPER (object_name) = UPPER ('USER_LNG_DAILY_LIMITS_COMP')
         AND UPPER (Owner) IN (SELECT SYS_CONTEXT ('USERENV','CURRENT_SCHEMA') FROM DUAL);

  IF cnt_master = 0
  THEN
     BEGIN
        EXECUTE IMMEDIATE ('
        CREATE TABLE USER_LNG_DAILY_LIMITS_COMP
        (
            COMPONENT_ID      NUMBER(10) PRIMARY KEY NOT NULL, 
            TITLE             VARCHAR2 (255),
            USER_ID           NUMBER(10),
            LAST_UPDATE       DATE
        )');
        EXECUTE IMMEDIATE
           ('GRANT DELETE, INSERT, SELECT, UPDATE ON USER_LNG_DAILY_LIMITS_COMP TO OLF_USER');
        EXECUTE IMMEDIATE
           ('GRANT SELECT ON  USER_LNG_DAILY_LIMITS_COMP TO OLF_READONLY');
     END;
  END IF;
END;
END;
/

                
 BEGIN 
 DECLARE
   cnt_master   NUMBER;
 BEGIN
   SELECT COUNT (1)
   INTO cnt_master
   FROM all_objects
   WHERE
         UPPER (object_type) = UPPER ('SEQUENCE')
         AND UPPER (object_name) = UPPER ('USER_LNG_DAILY_LIMITS_COMP_SQ')
         AND UPPER (Owner) IN (SELECT SYS_CONTEXT ('USERENV','CURRENT_SCHEMA') FROM DUAL);

  IF cnt_master = 0
  THEN
     BEGIN
        EXECUTE IMMEDIATE
           ('CREATE SEQUENCE USER_LNG_DAILY_LIMITS_COMP_SQ START WITH 1 INCREMENT BY 1');
     END;
  END IF;
 END;
 END;
/


CREATE OR REPLACE trigger USER_LNG_DAILY_LIMITS_COMP_TR
BEFORE INSERT ON USER_LNG_DAILY_LIMITS_COMP
FOR EACH ROW
BEGIN
      SELECT USER_LNG_DAILY_LIMITS_COMP_SQ.NEXTVAL INTO :NEW.COMPONENT_ID FROM dual;          
END;
/

内容的提问来源于stack exchange，提问作者Prince Vijay Pratap