Oracle至PostgreSQL存储包迁移：ora2pg生成脚本适配咨询

原Oracle存储包代码 #

CREATE OR REPLACE PACKAGE DIV.div_pkg_bitacora AS


PROCEDURE registrar_bitacora( pv_origen       IN VARCHAR2,
                              pv_id_entidad   IN VARCHAR2,
                              pv_tipo_entidad IN VARCHAR2,
                              pv_par_entrada  IN VARCHAR2,
                              pv_par_salida   IN VARCHAR2,
                              pv_usuario      IN VARCHAR2,
                              pd_fecha_inicio IN TIMESTAMP,
                              pd_fecha_fin    IN TIMESTAMP);
END div_pkg_bitacora;
/

CREATE OR REPLACE PACKAGE body DIV.div_pkg_bitacora AS

PROCEDURE registrar_bitacora( pv_origen       IN VARCHAR2,
                              pv_id_entidad   IN VARCHAR2,
                              pv_tipo_entidad IN VARCHAR2,
                              pv_par_entrada  IN VARCHAR2,
                              pv_par_salida   IN VARCHAR2,
                              pv_usuario      IN VARCHAR2,
                              pd_fecha_inicio IN TIMESTAMP,
                              pd_fecha_fin    IN TIMESTAMP) IS
                              
   PRAGMA AUTONOMOUS_TRANSACTION;
   vv_flag   VARCHAR2(10);
BEGIN
   BEGIN
      SELECT es_log
        INTO vv_flag
        FROM div_log_procesos
       WHERE upper(origen) = upper(pv_origen);
   EXCEPTION 
      WHEN OTHERS THEN
         vv_flag := 'N';
   END;
   --
   IF vv_flag = 'S' THEN
      INSERT INTO div_log_dividendos(
             origen,
             id_entidad,
             tipo_entidad,
             parametros_entrada,
             parametros_salida,
             fecha,
             fecha_inicio,
             fecha_fin,
             usuario)
      VALUES(pv_origen,
             pv_id_entidad,
             pv_tipo_entidad,
             pv_par_entrada,
             pv_par_salida,
             SYSDATE,
             pd_fecha_inicio,
             pd_fecha_fin,
             pv_usuario);
      COMMIT;
   END IF;
EXCEPTION 
   WHEN OTHERS THEN
      NULL;
END;

END div_pkg_bitacora;
/

ora2pg生成的PostgreSQL脚本 #

SET client_encoding TO 'UTF8';

SET search_path = div,public;
\set ON_ERROR_STOP ON



-- Oracle package 'DIV_PKG_BITACORA' declaration, please edit to match PostgreSQL syntax.

--DROP SCHEMA IF EXISTS div_pkg_bitacora CASCADE;
CREATE SCHEMA IF NOT EXISTS div_pkg_bitacora;



--
-- dblink wrapper to call function div_pkg_bitacora.registrar_bitacora() as an autonomous transaction
--
CREATE EXTENSION IF NOT EXISTS dblink;

CREATE OR REPLACE PROCEDURE div_pkg_bitacora.registrar_bitacora ( pv_origen text, pv_id_entidad text, pv_tipo_entidad text, pv_par_entrada text, pv_par_salida text, pv_usuario text, pd_fecha_inicio TIMESTAMP, pd_fecha_fin TIMESTAMP) AS $body$
DECLARE
        -- Change this to reflect the dblink connection string
        v_conn_str  text := format('port=%s dbname=%s user=%s', current_setting('port'), current_database(), current_user);
        v_query     text;

BEGIN
        v_query := 'CALL div_pkg_bitacora.registrar_bitacora_atx ( ' || quote_nullable(pv_origen) || ',' || quote_nullable(pv_id_entidad) || ',' || quote_nullable(pv_tipo_entidad) || ',' || quote_nullable(pv_par_entrada) || ',' || quote_nullable(pv_par_salida) || ',' || quote_nullable(pv_usuario) || ',' || quote_nullable(pd_fecha_inicio) || ',' || quote_nullable(pd_fecha_fin) || ' )';
        PERFORM * FROM dblink(v_conn_str, v_query) AS p (ret boolean);

END;
$body$ LANGUAGE plpgsql SECURITY DEFINER;




CREATE OR REPLACE PROCEDURE div_pkg_bitacora.registrar_bitacora_atx ( pv_origen text, pv_id_entidad text, pv_tipo_entidad text, pv_par_entrada text, pv_par_salida text, pv_usuario text, pd_fecha_inicio TIMESTAMP, pd_fecha_fin TIMESTAMP) AS $body$
DECLARE
vv_flag   varchar(10);

BEGIN
   BEGIN
      SELECT es_log
        INTO STRICT vv_flag
        FROM div_log_procesos
       WHERE upper(origen) = upper(pv_origen);
   EXCEPTION
      WHEN OTHERS THEN
         vv_flag := 'N';
   END;
   --
   IF vv_flag = 'S' THEN
      INSERT INTO div_log_dividendos(
             origen,
             id_entidad,
             tipo_entidad,
             parametros_entrada,
             parametros_salida,
             fecha,
             fecha_inicio,
             fecha_fin,
             usuario)
      VALUES (pv_origen,
             pv_id_entidad,
             pv_tipo_entidad,
             pv_par_entrada,
             pv_par_salida,
             clock_timestamp(),
             pd_fecha_inicio,
             pd_fecha_fin,
             pv_usuario);

   END IF;
EXCEPTION
   WHEN OTHERS THEN
      NULL;
END;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE div_pkg_bitacora.registrar_bitacora ( pv_origen text, pv_id_entidad text, pv_tipo_entidad text, pv_par_entrada text, pv_par_salida text, pv_usuario text, pd_fecha_inicio TIMESTAMP, pd_fecha_fin TIMESTAMP) FROM PUBLIC; -- REVOKE ALL ON PROCEDURE div_pkg_bitacora.registrar_bitacora_atx ( pv_origen text, pv_id_entidad text, pv_tipo_entidad text, pv_par_entrada text, pv_par_salida text, pv_usuario text, pd_fecha_inicio TIMESTAMP, pd_fecha_fin TIMESTAMP) FROM PUBLIC;
-- End of Oracle package 'DIV_PKG_BITACORA' declaration

具体调整步骤 #

1. 修正dblink连接字符串 #

生成的v_conn_str需适配实际环境：

• 本地连接可简化为：v_conn_str := 'dbname=' || current_database();
• 若需密码认证，建议通过pgpass文件配置，避免硬编码密码；必须硬编码时可改为：format('port=%s dbname=%s user=%s password=%s', ...)

2. 移除INTO STRICT关键字 #

原Oracle代码无STRICT约束，ora2pg添加的INTO STRICT会强制查询返回单行，虽然后续有异常捕获，但多余的约束可能引发非预期行为，建议修改为：

SELECT es_log
INTO vv_flag
FROM div_log_procesos
WHERE upper(origen) = upper(pv_origen);

3. 对齐时间函数逻辑 #

原Oracle的SYSDATE返回事务开始时的固定时间，ora2pg替换的clock_timestamp()会实时更新，若需和原逻辑一致，改为CURRENT_TIMESTAMP或now()：

CURRENT_TIMESTAMP  -- 事务内固定，和SYSDATE行为一致

4. 权限与安全设置 #

• 若原Oracle包无特殊权限需求，将SECURITY DEFINER改为默认的SECURITY INVOKER，降低权限提升风险；
• 根据业务需求执行注释中的REVOKE语句，收回PUBLIC对存储过程的默认权限：

REVOKE ALL ON PROCEDURE div_pkg_bitacora.registrar_bitacora FROM PUBLIC;
REVOKE ALL ON PROCEDURE div_pkg_bitacora.registrar_bitacora_atx FROM PUBLIC;

5. 验证功能一致性 #

测试核心场景确保逻辑和原Oracle一致：

• 当div_log_procesos存在匹配origen且es_log='S'时，确认数据插入div_log_dividendos；
• 无匹配origen时，确认vv_flag设为'N'且无插入操作；
• 异常场景下（如表不存在、权限不足），确认静默处理（无报错，逻辑终止）。

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