Oracle EBS 实战：并发报表全动态一键克隆及环境核对方案-LiaoDev's Blog

在 Oracle EBS 客制化开发中，克隆现有的标准报表或成熟的客制化报表进行二次开发是常态。但手动在 EBS 中克隆一个并发程序极其繁琐：要复制程序包、创建可执行、定义程序、一个个手动敲入参数，很费时间。

经过多次调试与避坑，我整理了一套完整的 PL/SQL 自动化解决方案，涵盖了从代码克隆到功能核对的全闭环。

方案对比：为什么不建议手动克隆？

维度	手动克隆 (UI)	自动化脚本 (PL/SQL)
效率	5-10 分钟，需频繁切换职责	< 10 秒，一键执行
准确性	参数多时极易遗漏提示文本	100% 物理对齐底层表
元数据	需手动记录输出格式、应用简称	动态获取，零配置
容错性	注册失败后清理繁琐	支持一键清理重启

核心脚本分享

1. 全动态一键克隆：自动清理旧数据，支持反复运行。

填入需要复制的旧程序代码，指定新的代码，指定初始化参数，执行脚本一键复制程序包，创建可执行，注册程序，克隆参数，暂不处理XML模板定义和文件上传。

SQL

DECLARE
  -- ==========================================
  -- 1. 配置信息 (请在此处修改)
  -- ==========================================
  -- 程序定义
  l_old_prog_code VARCHAR2(100) := 'CUX001';
  l_new_prog_code VARCHAR2(100) := 'CUX001_NEW';
  l_new_prog_name VARCHAR2(200) := 'CUX:库存收发存报表(NEW)';
  
  -- 程序包定义
  l_old_pkg_name  VARCHAR2(100) := 'CUX001_HTML_PKG'; 
  l_new_pkg_name  VARCHAR2(100) := 'CUX001_HTML_NEW_PKG';

  -- 内部变量
  l_appl_short_name VARCHAR2(50);
  l_output_type     VARCHAR2(20);
  l_vset_name       VARCHAR2(100);
  l_prompt          VARCHAR2(200);
  l_description     VARCHAR2(240);
  
  -- 程序包克隆专用变量
  l_ddl_source      CLOB;
  l_sql             CLOB;

  -- 步骤追踪
  l_step            VARCHAR2(100) := 'START';

BEGIN
  DBMS_OUTPUT.ENABLE(NULL);
  DBMS_OUTPUT.PUT_LINE('>>>>> Starting Full-Stack Cloning (With Sequence Log) <<<<<');

  -- ==========================================
  -- 2. 环境初始化
  -- ==========================================
  l_step := 'INIT_ENV';
  DBMS_OUTPUT.PUT_LINE('[STEP 1] Initializing Environment...');
  fnd_global.apps_initialize(user_id => 1111, resp_id => 53864, resp_appl_id => 20000);
  fnd_flex_dsc_api.set_session_mode(session_mode => 'customer_data');

  l_step := 'GET_APP_INFO';
  SELECT fa.application_short_name, fcp.output_file_type
    INTO l_appl_short_name, l_output_type
    FROM fnd_concurrent_programs fcp, fnd_application fa
   WHERE fcp.concurrent_program_name = l_old_prog_code
     AND fcp.application_id = fa.application_id;
  
  DBMS_OUTPUT.PUT_LINE('   > App: ' || l_appl_short_name || ', Output: ' || l_output_type);

  -- ==========================================
  -- 3. 自动克隆 PL/SQL 程序包
  -- ==========================================
  l_step := 'CLONE_PACKAGE';
  DBMS_OUTPUT.PUT_LINE('[STEP 2] Cloning PL/SQL Package: ' || l_old_pkg_name || ' -> ' || l_new_pkg_name);

  -- 关闭 SQLTERMINATOR 防止 PLS-00103
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', FALSE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'BODY', FALSE);

  FOR r IN (SELECT 'PACKAGE' type_name FROM DUAL UNION ALL SELECT 'PACKAGE_BODY' FROM DUAL) LOOP
    l_step := 'CLONE_PKG_' || r.type_name;
    BEGIN
        l_ddl_source := DBMS_METADATA.GET_DDL(r.type_name, UPPER(l_old_pkg_name));
        l_sql := REGEXP_REPLACE(l_ddl_source, l_old_pkg_name, l_new_pkg_name, 1, 0, 'i');
        EXECUTE IMMEDIATE l_sql;
        DBMS_OUTPUT.PUT_LINE('   > Compiled ' || r.type_name || ' successfully.');
    EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('   > Warning: Failed to clone ' || r.type_name || '. Error: ' || SQLERRM);
    END;
  END LOOP;
  
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT');

  -- ==========================================
  -- 4. 清理旧数据
  -- ==========================================
  l_step := 'CLEANUP_SRS';
  DBMS_OUTPUT.PUT_LINE('[STEP 3] Cleaning up old program definitions...');

  DELETE FROM fnd_descr_flex_column_usages WHERE descriptive_flexfield_name = '$SRS$.' || l_new_prog_code;
  DELETE FROM fnd_descr_flex_contexts WHERE descriptive_flexfield_name = '$SRS$.' || l_new_prog_code;
  DELETE FROM fnd_descriptive_flexs WHERE descriptive_flexfield_name = '$SRS$.' || l_new_prog_code;
  COMMIT;

  IF fnd_program.program_exists(l_new_prog_code, l_appl_short_name) THEN
    fnd_program.delete_program(l_new_prog_code, l_appl_short_name);
  END IF;
  
  IF fnd_program.executable_exists(l_new_prog_code, l_appl_short_name) THEN
    fnd_program.delete_executable(l_new_prog_code, l_appl_short_name);
  END IF;

  -- ==========================================
  -- 5. 创建可执行 & 注册程序
  -- ==========================================
  l_step := 'CREATE_EXEC';
  DBMS_OUTPUT.PUT_LINE('[STEP 4] Registering Executable & Program...');
  
  fnd_program.executable(
    executable          => l_new_prog_code,
    application         => l_appl_short_name,
    short_name          => l_new_prog_code,
    description         => l_new_prog_name,
    execution_method    => 'PL/SQL Stored Procedure',
    execution_file_name => l_new_pkg_name || '.main'
  );

  l_step := 'REGISTER_PROG';
  fnd_program.register(
    program                => l_new_prog_name,
    application            => l_appl_short_name,
    enabled                => 'Y',
    short_name             => l_new_prog_code,
    description            => l_new_prog_name,
    executable_short_name  => l_new_prog_code,
    executable_application => l_appl_short_name,
    output_type            => l_output_type,
    use_in_srs             => 'Y'
  );

  -- ==========================================
  -- 6. 克隆参数
  -- ==========================================
  l_step := 'CLONE_PARAMS';
  DBMS_OUTPUT.PUT_LINE('[STEP 5] Cloning Parameters...');
  
  FOR r IN (SELECT * FROM fnd_descr_flex_column_usages
             WHERE descriptive_flexfield_name = '$SRS$.' || l_old_prog_code
             ORDER BY column_seq_num) LOOP
    
    l_step := 'PARAM_' || r.column_seq_num;
    
    IF r.flex_value_set_id IS NOT NULL THEN
       SELECT flex_value_set_name INTO l_vset_name
         FROM fnd_flex_value_sets WHERE flex_value_set_id = r.flex_value_set_id;
    ELSE
       l_vset_name := NULL;
    END IF;

    BEGIN
      SELECT form_left_prompt, description INTO l_prompt, l_description
        FROM fnd_descr_flex_col_usage_tl
       WHERE descriptive_flexfield_name = r.descriptive_flexfield_name
         AND application_column_name = r.application_column_name
         AND language = 'ZHS';
    EXCEPTION WHEN NO_DATA_FOUND THEN
      l_prompt := r.end_user_column_name; l_description := r.end_user_column_name;
    END;

    fnd_program.parameter(
      program_short_name  => l_new_prog_code,
      application         => l_appl_short_name,
      sequence            => r.column_seq_num,
      parameter           => r.end_user_column_name,
      description         => l_description,
      enabled             => r.enabled_flag,
      value_set           => l_vset_name,
      default_type        => r.default_type,
      default_value       => r.default_value,
      required            => r.required_flag,
      enable_security     => r.security_enabled_flag,
      display             => r.display_flag,
      display_size        => NVL(r.display_size, 20),
      description_size    => NVL(r.maximum_description_len, 50),
      concatenated_description_size => NVL(r.concatenation_description_len, 25),
      prompt              => l_prompt,
      token               => r.srw_param
    );
    
    -- 显示序号，更方便核对
    DBMS_OUTPUT.PUT_LINE('   > Copied parameter: ' || r.end_user_column_name || ' (Seq: ' || r.column_seq_num || ')');
  END LOOP;

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('>>>>> ALL SUCCESS: Package, Program, and Parameters Cloned! <<<<<');

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('==============================================');
    DBMS_OUTPUT.PUT_LINE('FATAL ERROR AT STEP: ' || l_step);
    DBMS_OUTPUT.PUT_LINE('Error Msg : ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('FND Msg   : ' || fnd_program.message);
    DBMS_OUTPUT.PUT_LINE('==============================================');
END;

2. 全面核对脚本：确保功能“零偏差”

在克隆完成后，运行核对脚本。此版本特别修复了 l_status 缓冲区过小导致 ORA-06502 的问题，并增加了对 XML Publisher 的智能检测逻辑。

核对维度包括：

Package 状态：是否为 VALID。
SRS 参数对齐：对比原程序与新程序的参数总数、顺序及值集 ID。
XML Publisher：若原程序有模板而新程序缺失，会显式提示 MISSING。

DECLARE
    -- ==========================================
    -- 1. 配置核对简称 (请根据实际情况修改)
    -- ==========================================
    l_old_code  VARCHAR2(100) := 'CUX001';
    l_new_code  VARCHAR2(100) := 'CUX001_NEW'; 
    l_old_pkg   VARCHAR2(100) := 'CUX001_HTML_PKG';
    l_new_pkg   VARCHAR2(100) := 'CUX001_HTML_NEW_PKG';

    -- 2. 内部变量
    l_count_old NUMBER;
    l_count_new NUMBER;
    l_status    VARCHAR2(2000); 

BEGIN
    DBMS_OUTPUT.PUT_LINE('====================================================');
    DBMS_OUTPUT.PUT_LINE('CHECK REPORT: ' || l_old_code || ' VS ' || l_new_code);
    DBMS_OUTPUT.PUT_LINE('====================================================');

    -- 1. 检查数据库包状态
    BEGIN
        SELECT status INTO l_status FROM all_objects 
        WHERE object_name = UPPER(l_new_pkg) AND object_type = 'PACKAGE BODY';
        DBMS_OUTPUT.PUT_LINE('1. Package Body Status: ' || l_status);
    EXCEPTION WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('1. Package Body Status: MISSING (ERROR!)');
    END;

    -- 2. 检查并发程序与可执行定义
    SELECT COUNT(*) INTO l_count_new FROM fnd_concurrent_programs WHERE concurrent_program_name = l_new_code;
    DBMS_OUTPUT.PUT_LINE('2. Concurrent Program Exists: ' || CASE WHEN l_count_new > 0 THEN 'YES' ELSE 'NO (ERROR!)' END);

    IF l_count_new > 0 THEN
        SELECT execution_file_name INTO l_status FROM fnd_executables 
        WHERE executable_name = l_new_code;
        DBMS_OUTPUT.PUT_LINE('   Executable Points To: ' || l_status);

        SELECT output_file_type INTO l_status FROM fnd_concurrent_programs WHERE concurrent_program_name = l_new_code;
        DBMS_OUTPUT.PUT_LINE('3. Output Format: ' || l_status);
    END IF;

    -- 3. 核对参数数量 (SRS)
    SELECT COUNT(*) INTO l_count_old FROM fnd_descr_flex_column_usages WHERE descriptive_flexfield_name = '$SRS$.'||l_old_code;
    SELECT COUNT(*) INTO l_count_new FROM fnd_descr_flex_column_usages WHERE descriptive_flexfield_name = '$SRS$.'||l_new_code;
    DBMS_OUTPUT.PUT_LINE('4. Parameter Count Check:');
    DBMS_OUTPUT.PUT_LINE('   - Old Report: ' || l_count_old);
    DBMS_OUTPUT.PUT_LINE('   - New Report: ' || l_count_new);
    IF l_count_old <> l_count_new THEN
        DBMS_OUTPUT.PUT_LINE('   WARNING: Parameter count mismatch!');
    ELSE
        DBMS_OUTPUT.PUT_LINE('   [OK] Parameter counts match.');
    END IF;

    -- 4. 核对 XML Publisher 数据定义
    SELECT COUNT(*) INTO l_count_old FROM xdo_ds_definitions_b WHERE data_source_code = l_old_code;
    SELECT COUNT(*) INTO l_count_new FROM xdo_ds_definitions_b WHERE data_source_code = l_new_code;
    
    DBMS_OUTPUT.PUT_LINE('5. XML Publisher Data Definition: ' || 
        CASE 
            WHEN l_count_new > 0 THEN 'FOUND' 
            WHEN l_count_old > 0 THEN 'MISSING (Action Required!)'
            ELSE 'NOT FOUND (Original Missing)'
        END);

    -- 5. 检查 XML Publisher 模板
    SELECT COUNT(*) INTO l_count_old FROM xdo_templates_b WHERE template_code = l_old_code;
    SELECT COUNT(*) INTO l_count_new FROM xdo_templates_b WHERE template_code = l_new_code;
    
    DBMS_OUTPUT.PUT_LINE('6. XML Publisher Template File: ' || 
        CASE 
            WHEN l_count_new > 0 THEN 'FOUND' 
            WHEN l_count_old > 0 THEN 'MISSING (Action Required!)'
            ELSE 'NOT FOUND (Original Missing)'
        END);

    DBMS_OUTPUT.PUT_LINE('====================================================');
    DBMS_OUTPUT.PUT_LINE('核对完成。');
    DBMS_OUTPUT.PUT_LINE('====================================================');
END;

3. 并发可执行一键删除脚本

DECLARE
    -- ==========================================
    -- 1. 配置需要删除的简称
    -- ==========================================
    l_prog_short_name  VARCHAR2(100) := 'CUX001_NEW'; -- 并发程序简称
    l_exe_short_name   VARCHAR2(100) := 'CUX001_NEW'; -- 可执行简称
    l_appl_short_name  VARCHAR2(50)  := 'CUX';        -- 应用产品简称
    
BEGIN
    DBMS_OUTPUT.PUT_LINE('>>>>> Starting Cleanup Process for ' || l_prog_short_name || ' <<<<<');

    -- ==========================================
    -- 2. 删除并发程序 (Concurrent Program)
    -- ==========================================
    -- 该操作会自动清理关联的参数定义 (SRS Parameters)
    IF fnd_program.program_exists(l_prog_short_name, l_appl_short_name) THEN
        fnd_program.delete_program(l_prog_short_name, l_appl_short_name);
        DBMS_OUTPUT.PUT_LINE('--- Success: Concurrent Program deleted.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('--- Note: Concurrent Program not found.');
    END IF;

    -- ==========================================
    -- 3. 删除可执行文件 (Executable)
    -- ==========================================
    IF fnd_program.executable_exists(l_exe_short_name, l_appl_short_name) THEN
        fnd_program.delete_executable(l_exe_short_name, l_appl_short_name);
        DBMS_OUTPUT.PUT_LINE('--- Success: Executable deleted.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('--- Note: Executable not found.');
    END IF;

    -- ==========================================
    -- 4. 彻底清理底层残留数据 (可选)
    -- 如果之前使用了直接插入底层的 SQL，建议执行此步骤
    -- ==========================================
    DELETE FROM fnd_descr_flex_column_usages 
    WHERE descriptive_flexfield_name = '$SRS$.' || l_prog_short_name;
    
    DELETE FROM fnd_descr_flex_col_usage_tl 
    WHERE descriptive_flexfield_name = '$SRS$.' || l_prog_short_name;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('>>>>> Final Success: All data cleaned up. <<<<<');

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('FATAL ERROR: ' || SQLERRM);
END;

避坑指南：开发者需要注意的 3 个细节

物理分隔符 (Slash)：在 SQL 窗口手动编译 DDL 时，包头（Spec）和包体（Body）之间必须加正斜杠 /，否则会报 PLS-00103: 出现符号 "CREATE"。
包内硬编码：脚本只能替换包的定义名称。如果包体代码内部（如日志记录或内部调用）硬编码了旧包名字符串，必须手动全局替换，否则会调用旧逻辑。
缓存刷新：克隆完成后，如果 EBS 界面没有立即显示参数，请通过 Functional Administrator 职责清除所有缓存。

结语

这套脚本将原本繁琐的报表克隆过程缩短到了秒级，极大地提升了开发体验。特别是在应对参数众多的库存或成本报表时，自动化同步是保证质量的唯一途径。

本博客由廖开发 (LiaoDev) 整理发布。

关键词：Oracle EBS, Concurrent Program, PL/SQL, 克隆, 注册 API

目录CONTENT

Oracle EBS 实战：并发报表全动态一键克隆及环境核对方案