如何处理DBMS_SCHEDULER调用带对象输出参数存储过程的返回值

要处理存储过程返回的对象并插入数据表，核心是在调度任务的PL/SQL块中直接捕获输出参数，再执行插入操作。原代码使用DBMS_SCHEDULER.run_program无法直接获取输出参数，需调整实现逻辑：

步骤1：明确存储过程与对象类型定义 #

假设你的对象类型和存储过程定义如下（需匹配实际场景）：

-- 示例对象类型
CREATE OR REPLACE TYPE MY_OBJ AS OBJECT(
  id NUMBER,
  name VARCHAR2(100),
  create_date DATE
);
/

-- 带输出参数的存储过程
CREATE OR REPLACE PROCEDURE EXAMPLE_PROG(p_result OUT MY_OBJ) IS
BEGIN
  -- 模拟生成对象数据
  p_result := MY_OBJ(1, '测试数据', SYSDATE);
END;
/

步骤2：修改调度任务的PL/SQL块 #

直接在job_action中调用存储过程，声明变量接收输出对象，随后插入目标数据表。分两种场景处理：

场景A：目标表为对象表 #

如果数据表是基于对象类型创建的（例如CREATE TABLE OBJ_TABLE OF MY_OBJ;），可直接插入对象变量：

DBMS_SCHEDULER.create_job (
  job_name     => 'EXAMPLE_JOB',
  job_type     => 'PLSQL_BLOCK',
  job_action   => '
    DECLARE
      v_result MY_OBJ;
    BEGIN
      -- 直接调用存储过程获取输出对象
      EXAMPLE_PROG(v_result);
      -- 将对象插入对象表
      INSERT INTO OBJ_TABLE VALUES v_result;
      COMMIT;
    END;',
  start_date   => SYSTIMESTAMP,
  enabled      => TRUE
);

场景B：目标表为普通关系表 #

如果数据表是普通关系表（例如CREATE TABLE REL_TABLE(id NUMBER, name VARCHAR2(100), create_date DATE);），需提取对象属性插入：

DBMS_SCHEDULER.create_job (
  job_name     => 'EXAMPLE_JOB',
  job_type     => 'PLSQL_BLOCK',
  job_action   => '
    DECLARE
      v_result MY_OBJ;
    BEGIN
      EXAMPLE_PROG(v_result);
      -- 提取对象属性插入关系表
      INSERT INTO REL_TABLE(id, name, create_date)
      VALUES(v_result.id, v_result.name, v_result.create_date);
      COMMIT;
    END;',
  start_date   => SYSTIMESTAMP,
  enabled      => TRUE
);

关键注意事项 #

• 确保调度任务的执行用户拥有EXAMPLE_PROG存储过程的执行权限，以及目标数据表的插入权限。
• 若需通过DBMS_SCHEDULER.run_program调用（比如复用已定义的program），需为program配置输出参数，并通过DBMS_SCHEDULER.set_job_argument_value绑定变量，但这种方式更繁琐，推荐直接在PL/SQL块中调用存储过程。

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