0

I need to migrate all objects from oracle to postgres, but when I try to imoprt the package show me another thing.

This is the original package from 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;
/

=========== and this is the scrip to be generated in the process with ora2pg =================

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

What would to do in this case?

1
  • You need to convert each Oracle procedure or function into a PostgreSQL function or procedure. For Oracle procedures that don't return a value, use PostgreSQL procedures (CREATE PROCEDURE). For Oracle functions that return a value, use PostgreSQL functions (CREATE FUNCTION). Commented Dec 31, 2023 at 11:18

1 Answer 1

0

PostgreSQL doesn't have packages, but you can use schemas to organize functions and procedures. Check below example that converts the Oracle package into a PostgreSQL procedure, adjusting syntax for transaction control, exception handling, and date functions to fit PostgreSQL's PL/pgSQL language.

CREATE SCHEMA IF NOT EXISTS div;
     
CREATE OR REPLACE PROCEDURE div.registrar_bitacora(
    pv_origen VARCHAR,
    pv_id_entidad VARCHAR,
    pv_tipo_entidad VARCHAR,
    pv_par_entrada VARCHAR,
    pv_par_salida VARCHAR,
    pv_usuario VARCHAR,
    pd_fecha_inicio TIMESTAMP,
    pd_fecha_fin TIMESTAMP
)
LANGUAGE plpgsql
AS $$
DECLARE
    vv_flag VARCHAR(10);
BEGIN
    -- Assuming div_log_procesos table exists with correct columns
    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,
            CURRENT_TIMESTAMP, -- Replaced SYSDATE with CURRENT_TIMESTAMP
            pd_fecha_inicio,
            pd_fecha_fin,
            pv_usuario
        );
    END IF;
    -- Removed COMMIT; PostgreSQL handles transactions outside of stored procedures
EXCEPTION 
    WHEN OTHERS THEN
        -- PostgreSQL does not support a NULL statement in EXCEPTION block
        RAISE NOTICE 'An error occurred.';
END;
$$;
Sign up to request clarification or add additional context in comments.

1 Comment

thanks for the answer, I'll prove it tomorrow.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.