Hey there! I’ve helped a bunch of folks work through Oracle-to-Postgres migrations with Ora2pg, so let’s break down your problem step by step—first fixing that missing signature issue, then converting your procedure to run natively in Postgres.

First: Why Are Signatures Missing in Postgres? #

Ora2pg is a solid tool, but it can trip up on Oracle-specific quirks like package-internal types or complex procedure dependencies. Here’s what’s probably going on:

• Unmigrated Custom Types: Your procedure uses pkg_mouvement_calcul_cmn.rec_key and rec_tab—these are Oracle package-level types that Postgres doesn’t recognize automatically. You need to explicitly define these in Postgres first.
• Ora2pg Configuration Gaps: Double-check your Ora2pg config file to make sure EXPORT_PROCEDURES=1, EXPORT_FUNCTIONS=1, and EXPORT_PACKAGES includes pkg_mouvement_calcul_cmn. If these are disabled, the tool won’t generate the procedure signatures at all.
• Partial Code Conversion: Complex PL/SQL (like dynamic SQL with package variable references) almost always needs manual tweaks after Ora2pg’s initial export—its auto-conversion isn’t perfect for edge cases.

Second: Equivalent Postgres Procedure Implementation #

Let’s convert your Oracle procedure to PL/pgSQL. First, we’ll define the missing types, then rewrite the procedure itself:

Step 1: Create Equivalent Custom Types #

Postgres requires explicit composite types for what Oracle hides in packages. Adjust the fields below to match your actual rec_key and rec_tab definitions:

-- Match Oracle's pkg_mouvement_calcul_cmn.rec_key structure
CREATE TYPE rec_key AS (
    id INT, -- Replace with your actual fields from the Oracle type
    movement_code VARCHAR(50)
);

-- Match Oracle's rec_tab structure (assuming ADDED_DATA is an array of mouvement_calcul rows)
CREATE TYPE rec_tab AS (
    ADDED_DATA mouvement_calcul%ROWTYPE[]
);

Step 2: Rewrite the Procedure #

Postgres’s PL/pgSQL has subtle differences from Oracle PL/SQL—here’s the adjusted, functional version of your procedure:

CREATE OR REPLACE PROCEDURE evtAfterEachRow (
    p_inserting BOOLEAN,
    p_updating BOOLEAN,
    p_deleting BOOLEAN,
    p_key rec_key,
    p_old mouvement_calcul%ROWTYPE,
    p_new mouvement_calcul%ROWTYPE,
    INOUT p_rec_tab rec_tab
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_rowdata mouvement_calcul%ROWTYPE;
    v_varsql TEXT; -- Postgres prefers TEXT over CLOB for dynamic SQL (CLOB is supported too)
    v_actiontype VARCHAR(1);
    v_loaddate DATE;
    v_sqlsignature VARCHAR(4000);
    n INT;
    v_logcontext VARCHAR(4000);
    -- Postgres doesn't support package-level variables, so we'll use a local variable here
    -- If you need global access, use a temporary table or session-level variable instead
    v_ref_2_addedData mouvement_calcul%ROWTYPE[];
BEGIN
    -- Assign the reference variable (mimicking Oracle's package var)
    v_ref_2_addedData := p_rec_tab.ADDED_DATA;

    -- Keep your extraction condition logic (replace TRUE with your actual dynamic query check)
    IF NOT (TRUE) THEN
        RETURN;
    END IF;

    -- Set rowData based on operation type
    v_rowdata := CASE
        WHEN p_deleting THEN p_old
        ELSE p_new
    END;

    -- Add your remaining logic here! For dynamic SQL with bindings, use Postgres's EXECUTE syntax:
    -- EXECUTE v_varsql USING v_ref_2_addedData; -- Similar to Oracle's USING clause for bind variables
END;
$$;

Final Checks to Fix Signatures #

• After creating the types and procedure, run \df+ evtAfterEachRow in psql to verify the signature exists and matches your expectations.
• If Ora2pg generated partial code, merge our manual adjustments into it instead of starting from scratch—this preserves any other logic you have in the full procedure.
• For dynamic SQL with signature references, stick to Postgres’s EXECUTE ... USING syntax to handle binding variables safely (it’s just as secure as Oracle’s bind variables, and avoids SQL injection risks).

备注：内容来源于stack exchange，提问作者user25650971