指定Schema创建Oracle Scheduler Job遇报错，求代码修正方案

原代码核心错误点 #

1. 未定义变量l_client_id：代码多处使用该变量但未声明，属于笔误，应为l_specific_id。
2. 代理用户使用方式错误：PL/SQL块内无法执行CONNECT命令，CONNECT PROXY[l_schema_owner]/passwd语法完全错误，无法通过此方式切换身份创建Job。
3. Job Owner/Creator未正确指定：仅执行ALTER SESSION SET CURRENT_SCHEMA只会改变会话默认Schema，Job的Owner仍为执行操作的用户（SYS），无法达到指定Schema作为Owner的目的。
4. PL/SQL块字符串拼接错误：l_job_action中的单引号转义混乱，TO_CHAR(2)被错误嵌入字符串，导致语法报错。
5. Drop Job时未指定Schema：删除Job时未带上目标Schema前缀，若Job存在于指定Schema下会导致找不到对象。
6. 冗余COMMIT操作：DBMS_SCHEDULER的CREATE_JOB/DROP_JOB操作默认自动提交，额外COMMIT无意义。

修正后的实现代码 #

以下是修正后的Shell+PL/SQL脚本，确保Job的Owner和Creator为指定Schema：

# Run the PL/SQL block
set -vx

if [ $# -lt 1 ]
then
    echo "Invalid Parameter passed to the script"
    exit 1
fi

# SOURCE ENV FILE
. ${1}
. ${v_env}   # Source Oracle Env File

# 替换为实际代理用户的账号密码
PROXY_USER="你的代理用户名"
PROXY_PASSWORD="代理用户密码"

sqlplus -s "${PROXY_USER}[ABC]/${PROXY_PASSWORD}" << SQLEND
DECLARE
   l_specific_id         NUMBER := 1;
   l_repeat_interval   VARCHAR2 (2000);
   l_job_action        VARCHAR2 (2000);
BEGIN
   -- 检查目标Job是否存在
   FOR i IN (
      SELECT owner, repeat_interval
      FROM all_scheduler_jobs
      WHERE job_name = 'XYZ' || l_specific_id
        AND UPPER(owner) = UPPER('ABC')
   ) LOOP
      IF INSTR(i.repeat_interval, 'SECOND') != 0
         OR (INSTR(i.repeat_interval, 'MINUTE') != 0
             AND TO_NUMBER(SUBSTR(i.repeat_interval, INSTR(i.repeat_interval, '=', 1, 2)+1)) < 2)
      THEN
         -- 删除现有Job
         DBMS_SCHEDULER.drop_job(
            job_name => 'XYZ' || l_specific_id,
            force => TRUE
         );

         -- 构造Job执行的PL/SQL块（修正单引号转义）
         l_job_action := 'DECLARE
                            l_errbuf VARCHAR2(500);
                            l_retcode NUMBER;
                          BEGIN
                            MNOP_JOB_SCHEDULER_PKG.EXECUTE_JOB(l_errbuf, l_retcode, ' || l_specific_id || ', ''MINUTELY'', 2);
                            COMMIT;
                          END;';

         l_repeat_interval := 'FREQ=MINUTELY;INTERVAL=2';

         -- 创建Job：以代理用户连接的ABC Schema身份执行，Owner/Creator均为ABC
         DBMS_SCHEDULER.create_job(
            job_name          => 'XYZ' || l_specific_id,
            job_type          => 'PLSQL_BLOCK',
            job_action        => l_job_action,
            start_date        => SYSTIMESTAMP,
            repeat_interval   => l_repeat_interval,
            end_date          => NULL,
            enabled           => TRUE,
            comments          => 'testing Manager Setup FOR Client ID-' || l_specific_id
         );
      END IF;
   END LOOP;
END;
/
SQLEND

关键优化说明 #

1. 代理用户身份登录：直接在SQL*Plus连接时使用PROXY_USER[TARGET_SCHEMA]/PASSWORD语法，确保后续操作以目标Schema（ABC）身份执行，Job的Owner和Creator自然为ABC。
2. 修正变量与字符串拼接：替换l_client_id为l_specific_id，修正l_job_action中的单引号转义，移除错误嵌入的TO_CHAR(2)直接使用数值2。
3. 简化条件判断：将SUBSTR结果转为数值后再比较，避免字符串比较的潜在问题。
4. 移除冗余操作：删除无用的ALTER SESSION和错误的CONNECT语句，去掉冗余COMMIT。

前置权限检查 #

确保已完成以下权限配置：

• 目标Schema（ABC）拥有CREATE JOB权限：GRANT CREATE JOB TO ABC;
• 代理用户被授权连接至目标Schema：ALTER USER ABC GRANT CONNECT THROUGH PROXY_USER;
• 代理用户拥有Job操作权限：GRANT EXECUTE ON DBMS_SCHEDULER TO PROXY_USER;

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