Oracle命令行执行sql脚本（存储过程）报错，plsql窗口执行正常

Question

问题遇到的现象和发生背景

Oracle使用命令行执行sql脚本，提示有错误字符串，但是在plsql窗口执行是正常运行的。

DECLARE V_ROWCOUNT NUMBER;
BEGIN
    SELECT COUNT(1) INTO V_ROWCOUNT FROM USER_TABLES WHERE TABLE_NAME = UPPER('msg_cfg_email');
    IF V_ROWCOUNT = 0 THEN
        EXECUTE IMMEDIATE 
        'CREATE TABLE msg_cfg_email (
            id NUMBER(20,0) NOT NULL,
            tenant_id VARCHAR2(100) DEFAULT NULL NULL,
            tenant_name VARCHAR2(100) DEFAULT NULL NULL,
            app_id VARCHAR2(50) NOT NULL,
            email_config_parameter VARCHAR2(4000) NOT NULL,
            create_time DATE DEFAULT NULL NULL,
            create_user VARCHAR2(255) DEFAULT NULL NULL,
            update_time DATE DEFAULT NULL NULL,
            update_user VARCHAR2(255) DEFAULT NULL NULL,
            del_flag NUMBER(4,0) DEFAULT NULL NULL,
            version_number NUMBER(4,0) NOT NULL,
            PRIMARY KEY (id) 
        )';
        EXECUTE IMMEDIATE 'COMMENT ON TABLE msg_cfg_email IS ''邮件发送的的配置表''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.id IS ''id''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.tenant_id IS ''租户id''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.tenant_name IS ''租户name''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.app_id IS ''终端编码''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.email_config_parameter IS ''邮箱配置参数''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.create_time IS ''创建时间''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.create_user IS ''创建人ID''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.update_time IS ''修改时间''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.update_user IS ''修改人ID''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.del_flag IS ''逻辑删除键：0-未删除;1-删除''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_email.version_number IS ''版本号''';    
    END IF;
END;
/

DECLARE V_ROWCOUNT NUMBER;
BEGIN
    SELECT COUNT(1) INTO V_ROWCOUNT FROM USER_TABLES WHERE TABLE_NAME = UPPER('msg_cfg_sms');
    IF V_ROWCOUNT = 0 THEN
        EXECUTE IMMEDIATE 
        'CREATE TABLE msg_cfg_sms (
            id NUMBER(20,0) NOT NULL,
            tenant_id VARCHAR2(100) NOT NULL,
            tenant_name VARCHAR2(100) NOT NULL,
            app_id VARCHAR2(50) NOT NULL,
            sms_config_parameter VARCHAR2(4000) NOT NULL,
            create_time DATE NOT NULL,
            create_user VARCHAR2(255) NOT NULL,
            update_time DATE NOT NULL,
            update_user VARCHAR2(255) NOT NULL,
            del_flag NUMBER(4,0) NOT NULL,
            version NUMBER(4,0) NOT NULL,
            PRIMARY KEY (id) 
        )';
        EXECUTE IMMEDIATE 'COMMENT ON TABLE msg_cfg_sms IS ''短信发送的配置表''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.id IS ''id''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.tenant_id IS ''租户id''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.tenant_name IS ''租户name''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.app_id IS ''终端编码''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.sms_config_parameter IS ''短信配置参数''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.create_time IS ''创建时间''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.create_user IS ''创建人ID''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.update_time IS ''修改时间''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.update_user IS ''修改人ID''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.del_flag IS ''逻辑删除键：0-未删除;1-删除''';
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN msg_cfg_sms.version IS ''版本号''';
    END IF;
END;
/

操作环境、软件版本等信息

linux Oracle11G

尝试过的解决方法

sql脚本是直接在linux中编辑粘贴的，应该不是字符集或文件编码问题

Answer 1

因为sqlplus读取文本默认用的是ANSI编码，读取时乱码导致引号缺失！你需要用文本编辑器（nodepad++之类的）将sql文件转换为ANSI编码，再执行