Oracle Analytics Publisher

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to add a parameter when calling a function from the trigger section

Received Response
92
Views
8
Comments

I have a parameter that I pass directly to the data model - RDATE, and this parameter is automatically added to the triggers section.

I get an error when calling the function in the usual way - PKG_REPORT.CHECK_RESULT_FROM_CALC_REAL_CASH_EXPIRE(SYSDATE)

"ORA-06550: line 2, column 18: PLS-00302: component 'RDATE' must be declared ORA-06550: line 2, column 1: PL/SQL: Statement ignored"

There is a parameter declaration in the function call

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • Rank 6 - Analytics Lead

    The package in the database has to be updated, add the parameter.

    Rgds,

    Kevin

  • Rank 3 - Community Apprentice

    Kevin M-Oracle declaring a parameter didn't help.

    I have a script in the data model where the :RDATE parameter is used, and parameters are declared in the function I call. If, for example, my parameter in the data model is changed to TRUNC(SYSDATE), and the value is passed to the TRUNC(SYSDATE) function, then everything will work normally.

    Іn this case I have everything declared, the problem occurs when I add a variable in the script

  • Rank 6 - Analytics Lead

    If you add a variable (parameter) in a script, you have to declare it in the database package.

    Rgds,

    Kevin

  • Rank 3 - Community Apprentice

    @Kevin M-Oracle

    this is my code that I run, all variables are declared, but the problem still occurs

    create PACKAGE PKG_VKASIAN_TEST AS
    FUNCTION CALC_REAL_CASH_EXPIRE_TEST (
    R_DATE DATE
    )RETURN NUMBER;

    PROCEDURE CALC_REAL_CASH_EXPIRE_TEST_2(
    R_DATE DATE,
    P_UPDATED_ROW_COUNT OUT NUMBER
    );

    FUNCTION CHECK_RESULT_TEST(
    R_DATE DATE
    ) RETURN BOOLEAN; END PKG_VKASIAN_TEST;
    / create PACKAGE BODY PKG_VKASIAN_TEST AS FUNCTION CHECK_RESULT_TEST(
    R_DATE DATE
    ) RETURN BOOLEAN
    IS
    V_ROW_COUNT NUMBER;
    V_DATE DATE := R_DATE; -- Added variable to accept the date
    PRAGMA AUTONOMOUS_TRANSACTION; -- Add this line
    BEGIN

    V_ROW_COUNT := 0;

    DWH.PKG_VKASIAN_TEST.CALC_REAL_CASH_EXPIRE_TEST_2(R_DATE, V_ROW_COUNT);
    COMMIT; -- Consider committing the autonomous transaction if needed
    RETURN TRUE;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    ROLLBACK; -- Roll back any changes if necessary
    RETURN FALSE;
    END;





    FUNCTION CALC_REAL_CASH_EXPIRE_TEST(
    R_DATE DATE
    )
    RETURN NUMBER
    IS
    ROWS_UPDATED NUMBER;
    BEGIN
    MERGE INTO DWH.AD_CASH_FACT_EXPIRED_DATA M1
    USING (
    WITH
    WCONTRACT AS (
    SELECT
    R_DATE AS FIN_DATE,
    CON.CUSTOMER_ID AS CUSTOMER_ID,
    CUS.SHORT_NAME AS CUSTOMER_NAME,
    CON.ID AS CONTRACT_ID,
    CON.BEGIN_BASE_AMOUNT AS CONTRACT_BEGIN_AMOUNT_UAH,
    CON.BEGIN_DATE AS CONTRACT_BEGIN_DATE,
    CON.CLOSE_DATE AS CONTRACT_CLOSE_DATE
    FROM SR_BANK.CUSTOMER@PRODDB CUS
    JOIN SR_BANK.CONTRACT@PRODDB CON ON CUS.ID = CON.CUSTOMER_ID
    JOIN SR_BANK.CONTRACT_TYPE@PRODDB COT ON CON.TYPE_ID = COT.ID
    AND COT.SYS_NAME = 'I_POTR_GNV' -- тільки кеш кредити
    AND R_DATE BETWEEN CON.BEGIN_DATE AND COALESCE(CON.CLOSE_DATE,TRUNC(SYSDATE+10))
    and CON.BEGIN_DATE < TO_DATE('01.10.2022','DD.MM.YYYY') -- треба брати тільки кредити, що були відкриті до жовтня 2022
    ),
    WCONTRACT_ACCOUNT AS (
    SELECT
    CON.FIN_DATE,
    CON.CUSTOMER_ID,
    CON.CUSTOMER_NAME,
    CON.CONTRACT_ID, -- CON.CONTRACT_NO,
    CON.CONTRACT_BEGIN_DATE,
    CA.ACCOUNT_ID AS ACCOUNT_ID,
    ACC.ACCOUNT_NUMBER AS ACCOUNT_NO,
    BAL.CODE AS ACCOUNT_MASK,
    GAM.SYS_NAME AS MACRO_CODE,
    ASN.BASE_AMOUNT AS ACCOUNT_AMOUNT_UAH
    FROM WCONTRACT CON
    JOIN SR_BANK.COMPOUND_DOCUMENT@PRODDB COD ON CON.CONTRACT_ID = COD.ID
    JOIN SR_BANK.CONTRACT_ACCOUNT@PRODDB CA ON CON.CONTRACT_ID = CA.CONTRACT_ID
    JOIN SR_BANK.CONTRACT_ACC_MACRO@PRODDB CAM ON CA.MACRO_ID = CAM.ID
    JOIN SR_BANK.GENERATOR_ACC_MACRO@PRODDB GAM ON CAM.MACRO_ID = GAM.ID
    JOIN SR_BANK.ACCOUNT@PRODDB ACC ON CA.ACCOUNT_ID = ACC.ID
    JOIN SR_BANK.BALANCE_ACCOUNT@PRODDB BAL ON ACC.BALANCE_ID = BAL.ID
    AND BAL.CODE IN ('2909','3578','2203')
    JOIN SR_BANK.ACCOUNT_SNAPSHOT@PRODDB ASN ON CA.ACCOUNT_ID = ASN.ACCOUNT_ID
    AND ASN.SNAPSHOT_DATE = CON.FIN_DATE
    ),
    WEXPIRED_COMMISSION AS (
    SELECT
    TT.FIN_DATE,
    'COMMIS' AS RTYPE,
    TT.CONTRACT_ID,
    SUM(TT.ACCOUNT_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WCONTRACT_ACCOUNT TT
    WHERE TT.ACCOUNT_MASK = '3578'
    GROUP BY TT.FIN_DATE, TT.CONTRACT_ID
    ),
    WCONTRACT_ACCOUNT_PIVOT AS (
    SELECT
    FIN_DATE,
    CONTRACT_ID,
    CONTRACT_BODY_ACCOUNT_ID,
    EXPIRE_BODY_ACCOUNT_ID,
    DEBTS_ACCOUNT_ID
    FROM (
    SELECT CON.CONTRACT_ID, ACCOUNT_ID, MACRO_CODE, FIN_DATE
    FROM WCONTRACT_ACCOUNT CON
    )
    PIVOT (
    MAX(ACCOUNT_ID) AS ACCOUNT_ID
    FOR MACRO_CODE IN (
    'CREDIT_REST' AS CONTRACT_BODY,
    'CREDIT_EXPIRE' AS EXPIRE_BODY,
    'CREDIT_ALL_DEBTS' AS DEBTS
    )
    )
    ),
    WPAYMENT AS (
    SELECT
    CAP.FIN_DATE,
    CAP.CONTRACT_ID,
    'BODY' AS PAYMENT_TYPE,
    DOC.POST_DATE AS PAYMENT_DATE,
    DOC.BASE_AMOUNT AS PAYMENT_AMOUNT_UAH
    -- DOC.DESCRIPTION
    FROM WCONTRACT_ACCOUNT_PIVOT CAP
    JOIN SR_BANK.COMPOUND_ACTION@PRODDB COA ON CAP.CONTRACT_ID = COA.COMPOUND_DOCUMENT_ID
    JOIN SR_BANK.DOCUMENT@PRODDB DOC ON COA.ACTION_ID = DOC.ACTION_ID
    AND DOC.ACCOUNT_DEBIT_ID = CAP.DEBTS_ACCOUNT_ID
    AND DOC.ACCOUNT_CREDIT_ID = ANY(
    CAP.CONTRACT_BODY_ACCOUNT_ID, CAP.EXPIRE_BODY_ACCOUNT_ID
    )
    ),
    WSCHEDULE AS (
    SELECT
    FIN_DATE,
    CONTRACT_ID AS CONTRACT_ID,
    IS_CLOSED,
    POST_DATE AS SCHEDULE_DATE,
    TRUNC(POST_DATE,'MM') AS SCHEDULE_MONTH,
    SCHEDULE_TYPE AS SCHEDULE_TYPE,
    AMOUNT_UAH AS SCHEDULE_AMOUNT_UAH
    FROM (
    SELECT
    CON.FIN_DATE,
    CASE
    WHEN CON.CONTRACT_CLOSE_DATE <= CON.FIN_DATE
    THEN 1
    ELSE 0
    END AS IS_CLOSED,
    PAY.*
    FROM SR_BANK.PAYMENT_SCHEDULE@PRODDB PAY
    JOIN WCONTRACT CON ON PAY.CONTRACT_ID = CON.CONTRACT_ID
    ) TT
    UNPIVOT INCLUDE NULLS(
    AMOUNT_UAH FOR SCHEDULE_TYPE IN (
    ANN_AMOUNT_REST AS 'BODY'
    )
    )
    ),
    WMUST_DATA AS (
    SELECT
    SCH.FIN_DATE AS FIN_DATE,
    SCH.SCHEDULE_TYPE AS RTYPE,
    SCH.CONTRACT_ID AS CONTRACT_ID,
    SUM(SCH.SCHEDULE_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WSCHEDULE SCH
    WHERE
    (
    SCHEDULE_DATE <= R_DATE AND IS_CLOSED = 0
    OR
    IS_CLOSED = 1
    )
    GROUP BY SCH.FIN_DATE, SCH.SCHEDULE_TYPE, SCH.CONTRACT_ID
    ),
    WDONE_DATA AS (
    SELECT
    PAY.FIN_DATE AS FIN_DATE,
    PAY.PAYMENT_TYPE AS RTYPE,
    PAY.CONTRACT_ID AS CONTRACT_ID,
    SUM(PAY.PAYMENT_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WPAYMENT PAY
    WHERE PAY.PAYMENT_DATE <= PAY.FIN_DATE
    GROUP BY PAY.FIN_DATE, PAY.PAYMENT_TYPE, PAY.CONTRACT_ID
    ),
    WFINAL_CONTAINER AS (
    SELECT
    RTY.RTYPE,
    CON.*
    FROM WCONTRACT CON,
    (
    SELECT 'BODY' AS RTYPE FROM DUAL
    UNION ALL
    SELECT 'COMMIS' AS RTYPE FROM DUAL
    ) RTY
    )
    SELECT
    CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
    CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
    ,CON.RTYPE
    ,COALESCE(MST.AMOUNT_UAH,0) -
    COALESCE(DON.AMOUNT_UAH,0) AS AMOUNT_UAH
    FROM WFINAL_CONTAINER CON
    LEFT JOIN WMUST_DATA MST ON CON.CONTRACT_ID = MST.CONTRACT_ID
    AND CON.RTYPE = MST.RTYPE
    LEFT JOIN WDONE_DATA DON ON CON.CONTRACT_ID = DON.CONTRACT_ID
    AND CON.RTYPE = DON.RTYPE
    WHERE CON.RTYPE = 'BODY'
    UNION ALL
    SELECT
    CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
    CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
    ,CON.RTYPE
    ,ECM.AMOUNT_UAH
    FROM WFINAL_CONTAINER CON
    LEFT JOIN WEXPIRED_COMMISSION ECM ON CON.CONTRACT_ID = ECM.CONTRACT_ID
    AND CON.RTYPE = ECM.RTYPE
    WHERE CON.RTYPE = 'COMMIS'
    ) M2
    ON (
    M1.RDATE = M2.FIN_DATE
    AND
    M1.RTYPE = M2.RTYPE
    AND
    M1.CONTRACT_ID = M2.CONTRACT_ID
    )
    WHEN MATCHED THEN
    UPDATE SET
    M1.RVALUE = M2.AMOUNT_UAH
    WHEN NOT MATCHED THEN
    INSERT (M1.RDATE, M1.CUSTOMER_ID, M1.CONTRACT_ID, M1.RTYPE, M1.RVALUE)
    VALUES (M2.FIN_DATE, M2.CUSTOMER_ID, M2.CONTRACT_ID, M2.RTYPE, M2.AMOUNT_UAH)
    ;
    ROWS_UPDATED := SQL%ROWCOUNT;
    -- MICRO.SP_LOG_OK('CALC_CASH_EXPIRED', TO_CHAR(R_DATE,'DD.MM.YYYY')||' - '||SQL%ROWCOUNT||' rows was affected');
    COMMIT; IF ROWS_UPDATED > 0 THEN
    RETURN 1;
    ELSE
    RETURN 0;
    END IF;

    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RETURN 0;

    END; -- EXCEPTION
    -- WHEN OTHERS
    -- THEN MICRO.SP_LOG_ERROR('CALC_CASH_EXPIRED', TO_CHAR(R_DATE,'DD.MM.YYYY')||' - '||SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 900));
    -- THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    -- RAISE;
    PROCEDURE CALC_REAL_CASH_EXPIRE_TEST_2(
    R_DATE DATE,
    P_UPDATED_ROW_COUNT OUT NUMBER -- Added parameter to return row count
    )
    IS
    l_row_count NUMBER; -- Variable to store the number of affected rows
    BEGIN
    -- Perform the MERGE operation
    MERGE INTO DWH.AD_CASH_FACT_EXPIRED_DATA M1
    USING (
    WITH
    WCONTRACT AS (
    SELECT
    R_DATE AS FIN_DATE,
    CON.CUSTOMER_ID AS CUSTOMER_ID,
    CUS.SHORT_NAME AS CUSTOMER_NAME,
    CON.ID AS CONTRACT_ID,
    CON.BEGIN_BASE_AMOUNT AS CONTRACT_BEGIN_AMOUNT_UAH,
    CON.BEGIN_DATE AS CONTRACT_BEGIN_DATE,
    CON.CLOSE_DATE AS CONTRACT_CLOSE_DATE
    FROM SR_BANK.CUSTOMER@PRODDB CUS
    JOIN SR_BANK.CONTRACT@PRODDB CON ON CUS.ID = CON.CUSTOMER_ID
    JOIN SR_BANK.CONTRACT_TYPE@PRODDB COT ON CON.TYPE_ID = COT.ID
    AND COT.SYS_NAME = 'I_POTR_GNV' -- тільки кеш кредити
    AND R_DATE BETWEEN CON.BEGIN_DATE AND COALESCE(CON.CLOSE_DATE,TRUNC(SYSDATE+10))
    AND CON.BEGIN_DATE < TO_DATE('01.10.2022','DD.MM.YYYY') -- треба брати тільки кредити, що були відкриті до жовтня 2022
    ),
    WCONTRACT_ACCOUNT AS (
    SELECT
    CON.FIN_DATE,
    CON.CUSTOMER_ID,
    CON.CUSTOMER_NAME,
    CON.CONTRACT_ID,
    -- CON.CONTRACT_NO,
    CON.CONTRACT_BEGIN_DATE,
    CA.ACCOUNT_ID AS ACCOUNT_ID,
    ACC.ACCOUNT_NUMBER AS ACCOUNT_NO,
    BAL.CODE AS ACCOUNT_MASK,
    GAM.SYS_NAME AS MACRO_CODE,
    ASN.BASE_AMOUNT AS ACCOUNT_AMOUNT_UAH
    FROM WCONTRACT CON
    JOIN SR_BANK.COMPOUND_DOCUMENT@PRODDB COD ON CON.CONTRACT_ID = COD.ID
    JOIN SR_BANK.CONTRACT_ACCOUNT@PRODDB CA ON CON.CONTRACT_ID = CA.CONTRACT_ID
    JOIN SR_BANK.CONTRACT_ACC_MACRO@PRODDB CAM ON CA.MACRO_ID = CAM.ID
    JOIN SR_BANK.GENERATOR_ACC_MACRO@PRODDB GAM ON CAM.MACRO_ID = GAM.ID
    JOIN SR_BANK.ACCOUNT@PRODDB ACC ON CA.ACCOUNT_ID = ACC.ID
    JOIN SR_BANK.BALANCE_ACCOUNT@PRODDB BAL ON ACC.BALANCE_ID = BAL.ID
    AND BAL.CODE IN ('2909','3578','2203')
    JOIN SR_BANK.ACCOUNT_SNAPSHOT@PRODDB ASN ON CA.ACCOUNT_ID = ASN.ACCOUNT_ID
    AND ASN.SNAPSHOT_DATE = CON.FIN_DATE
    ),
    WEXPIRED_COMMISSION AS (
    SELECT
    TT.FIN_DATE,
    'COMMIS' AS RTYPE,
    TT.CONTRACT_ID,
    SUM(TT.ACCOUNT_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WCONTRACT_ACCOUNT TT
    WHERE TT.ACCOUNT_MASK = '3578'
    GROUP BY TT.FIN_DATE, TT.CONTRACT_ID
    ),
    WCONTRACT_ACCOUNT_PIVOT AS (
    SELECT
    FIN_DATE,
    CONTRACT_ID,
    CONTRACT_BODY_ACCOUNT_ID,
    EXPIRE_BODY_ACCOUNT_ID,
    DEBTS_ACCOUNT_ID
    FROM (
    SELECT CON.CONTRACT_ID, ACCOUNT_ID, MACRO_CODE, FIN_DATE
    FROM WCONTRACT_ACCOUNT CON
    )
    PIVOT (
    MAX(ACCOUNT_ID) AS ACCOUNT_ID
    FOR MACRO_CODE IN (
    'CREDIT_REST' AS CONTRACT_BODY,
    'CREDIT_EXPIRE' AS EXPIRE_BODY,
    'CREDIT_ALL_DEBTS' AS DEBTS
    )
    )
    ),
    WPAYMENT AS (
    SELECT
    CAP.FIN_DATE,
    CAP.CONTRACT_ID,
    'BODY' AS PAYMENT_TYPE,
    DOC.POST_DATE AS PAYMENT_DATE,
    DOC.BASE_AMOUNT AS PAYMENT_AMOUNT_UAH
    -- DOC.DESCRIPTION
    FROM WCONTRACT_ACCOUNT_PIVOT CAP
    JOIN SR_BANK.COMPOUND_ACTION@PRODDB COA ON CAP.CONTRACT_ID = COA.COMPOUND_DOCUMENT_ID
    JOIN SR_BANK.DOCUMENT@PRODDB DOC ON COA.ACTION_ID = DOC.ACTION_ID
    AND DOC.ACCOUNT_DEBIT_ID = CAP.DEBTS_ACCOUNT_ID
    AND DOC.ACCOUNT_CREDIT_ID = ANY(
    CAP.CONTRACT_BODY_ACCOUNT_ID, CAP.EXPIRE_BODY_ACCOUNT_ID
    )
    ),
    WSCHEDULE AS (
    SELECT
    FIN_DATE,
    CONTRACT_ID AS CONTRACT_ID,
    IS_CLOSED,
    POST_DATE AS SCHEDULE_DATE,
    TRUNC(POST_DATE,'MM') AS SCHEDULE_MONTH,
    SCHEDULE_TYPE AS SCHEDULE_TYPE,
    AMOUNT_UAH AS SCHEDULE_AMOUNT_UAH
    FROM (
    SELECT
    CON.FIN_DATE,
    CASE
    WHEN CON.CONTRACT_CLOSE_DATE <= CON.FIN_DATE
    THEN 1
    ELSE 0
    END AS IS_CLOSED,
    PAY.*
    FROM SR_BANK.PAYMENT_SCHEDULE@PRODDB PAY
    JOIN WCONTRACT CON ON PAY.CONTRACT_ID = CON.CONTRACT_ID
    ) TT
    UNPIVOT INCLUDE NULLS(
    AMOUNT_UAH FOR SCHEDULE_TYPE IN (
    ANN_AMOUNT_REST AS 'BODY'
    )
    )
    ),
    WMUST_DATA AS (
    SELECT
    SCH.FIN_DATE AS FIN_DATE,
    SCH.SCHEDULE_TYPE AS RTYPE,
    SCH.CONTRACT_ID AS CONTRACT_ID,
    SUM(SCH.SCHEDULE_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WSCHEDULE SCH
    WHERE
    (
    SCHEDULE_DATE <= R_DATE AND IS_CLOSED = 0
    OR
    IS_CLOSED = 1
    )
    GROUP BY SCH.FIN_DATE, SCH.SCHEDULE_TYPE, SCH.CONTRACT_ID
    ),
    WDONE_DATA AS (
    SELECT
    PAY.FIN_DATE AS FIN_DATE,
    PAY.PAYMENT_TYPE AS RTYPE,
    PAY.CONTRACT_ID AS CONTRACT_ID,
    SUM(PAY.PAYMENT_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WPAYMENT PAY
    WHERE PAY.PAYMENT_DATE <= PAY.FIN_DATE
    GROUP BY PAY.FIN_DATE, PAY.PAYMENT_TYPE, PAY.CONTRACT_ID
    ),
    WFINAL_CONTAINER AS (
    SELECT
    RTY.RTYPE,
    CON.*
    FROM WCONTRACT CON,
    (
    SELECT 'BODY' AS RTYPE FROM DUAL
    UNION ALL
    SELECT 'COMMIS' AS RTYPE FROM DUAL
    ) RTY
    )
    SELECT
    CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
    CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
    ,CON.RTYPE
    ,COALESCE(MST.AMOUNT_UAH,0) -
    COALESCE(DON.AMOUNT_UAH,0) AS AMOUNT_UAH
    FROM WFINAL_CONTAINER CON
    LEFT JOIN WMUST_DATA MST ON CON.CONTRACT_ID = MST.CONTRACT_ID
    AND CON.RTYPE = MST.RTYPE
    LEFT JOIN WDONE_DATA DON ON CON.CONTRACT_ID = DON.CONTRACT_ID
    AND CON.RTYPE = DON.RTYPE
    WHERE CON.RTYPE = 'BODY'
    UNION ALL
    SELECT
    CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
    CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
    ,CON.RTYPE
    ,ECM.AMOUNT_UAH
    FROM WFINAL_CONTAINER CON
    LEFT JOIN WEXPIRED_COMMISSION ECM ON CON.CONTRACT_ID = ECM.CONTRACT_ID
    AND CON.RTYPE = ECM.RTYPE
    WHERE CON.RTYPE = 'COMMIS'
    ) M2
    ON (
    M1.RDATE = M2.FIN_DATE
    AND
    M1.RTYPE = M2.RTYPE
    AND
    M1.CONTRACT_ID = M2.CONTRACT_ID
    )
    WHEN MATCHED THEN
    UPDATE SET
    M1.RVALUE = M2.AMOUNT_UAH
    WHEN NOT MATCHED THEN
    INSERT (M1.RDATE, M1.CUSTOMER_ID, M1.CONTRACT_ID, M1.RTYPE, M1.RVALUE)
    VALUES (M2.FIN_DATE, M2.CUSTOMER_ID, M2.CONTRACT_ID, M2.RTYPE, M2.AMOUNT_UAH)
    ; -- Get the number of rows affected
    l_row_count := SQL%ROWCOUNT;

    -- Commit the transaction
    COMMIT;

    -- Return the number of updated rows
    P_UPDATED_ROW_COUNT := l_row_count;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
    END; END PKG_VKASIAN_TEST;
    /
  • Rank 3 - Community Apprentice

    @Kevin M-Oracle hi

    this is my code that I run, all variables are declared, but the problem still occurs

    ```sql
    create PACKAGE PKG_VKASIAN_TEST AS
    FUNCTION CALC_REAL_CASH_EXPIRE_TEST (
    R_DATE DATE
    )RETURN NUMBER;

    PROCEDURE CALC_REAL_CASH_EXPIRE_TEST_2(
    R_DATE DATE,
    P_UPDATED_ROW_COUNT OUT NUMBER
    );

    FUNCTION CHECK_RESULT_TEST(
    R_DATE DATE
    ) RETURN BOOLEAN; END PKG_VKASIAN_TEST;
    / create PACKAGE BODY PKG_VKASIAN_TEST AS FUNCTION CHECK_RESULT_TEST(
    R_DATE DATE
    ) RETURN BOOLEAN
    IS
    V_ROW_COUNT NUMBER;
    V_DATE DATE := R_DATE; -- Added variable to accept the date
    PRAGMA AUTONOMOUS_TRANSACTION; -- Add this line
    BEGIN

    V_ROW_COUNT := 0;

    DWH.PKG_VKASIAN_TEST.CALC_REAL_CASH_EXPIRE_TEST_2(R_DATE, V_ROW_COUNT);
    COMMIT; -- Consider committing the autonomous transaction if needed
    RETURN TRUE;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    ROLLBACK; -- Roll back any changes if necessary
    RETURN FALSE;
    END;





    FUNCTION CALC_REAL_CASH_EXPIRE_TEST(
    R_DATE DATE
    )
    RETURN NUMBER
    IS
    ROWS_UPDATED NUMBER;
    BEGIN
    MERGE INTO DWH.AD_CASH_FACT_EXPIRED_DATA M1
    USING (
    WITH
    WCONTRACT AS (
    SELECT
    R_DATE AS FIN_DATE,
    CON.CUSTOMER_ID AS CUSTOMER_ID,
    CUS.SHORT_NAME AS CUSTOMER_NAME,
    CON.ID AS CONTRACT_ID,
    CON.BEGIN_BASE_AMOUNT AS CONTRACT_BEGIN_AMOUNT_UAH,
    CON.BEGIN_DATE AS CONTRACT_BEGIN_DATE,
    CON.CLOSE_DATE AS CONTRACT_CLOSE_DATE
    FROM SR_BANK.CUSTOMER@PRODDB CUS
    JOIN SR_BANK.CONTRACT@PRODDB CON ON CUS.ID = CON.CUSTOMER_ID
    JOIN SR_BANK.CONTRACT_TYPE@PRODDB COT ON CON.TYPE_ID = COT.ID
    AND COT.SYS_NAME = 'I_POTR_GNV' -- тільки кеш кредити
    AND R_DATE BETWEEN CON.BEGIN_DATE AND COALESCE(CON.CLOSE_DATE,TRUNC(SYSDATE+10))
    and CON.BEGIN_DATE < TO_DATE('01.10.2022','DD.MM.YYYY') -- треба брати тільки кредити, що були відкриті до жовтня 2022
    ),
    WCONTRACT_ACCOUNT AS (
    SELECT
    CON.FIN_DATE,
    CON.CUSTOMER_ID,
    CON.CUSTOMER_NAME,
    CON.CONTRACT_ID, -- CON.CONTRACT_NO,
    CON.CONTRACT_BEGIN_DATE,
    CA.ACCOUNT_ID AS ACCOUNT_ID,
    ACC.ACCOUNT_NUMBER AS ACCOUNT_NO,
    BAL.CODE AS ACCOUNT_MASK,
    GAM.SYS_NAME AS MACRO_CODE,
    ASN.BASE_AMOUNT AS ACCOUNT_AMOUNT_UAH
    FROM WCONTRACT CON
    JOIN SR_BANK.COMPOUND_DOCUMENT@PRODDB COD ON CON.CONTRACT_ID = COD.ID
    JOIN SR_BANK.CONTRACT_ACCOUNT@PRODDB CA ON CON.CONTRACT_ID = CA.CONTRACT_ID
    JOIN SR_BANK.CONTRACT_ACC_MACRO@PRODDB CAM ON CA.MACRO_ID = CAM.ID
    JOIN SR_BANK.GENERATOR_ACC_MACRO@PRODDB GAM ON CAM.MACRO_ID = GAM.ID
    JOIN SR_BANK.ACCOUNT@PRODDB ACC ON CA.ACCOUNT_ID = ACC.ID
    JOIN SR_BANK.BALANCE_ACCOUNT@PRODDB BAL ON ACC.BALANCE_ID = BAL.ID
    AND BAL.CODE IN ('2909','3578','2203')
    JOIN SR_BANK.ACCOUNT_SNAPSHOT@PRODDB ASN ON CA.ACCOUNT_ID = ASN.ACCOUNT_ID
    AND ASN.SNAPSHOT_DATE = CON.FIN_DATE
    ),
    WEXPIRED_COMMISSION AS (
    SELECT
    TT.FIN_DATE,
    'COMMIS' AS RTYPE,
    TT.CONTRACT_ID,
    SUM(TT.ACCOUNT_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WCONTRACT_ACCOUNT TT
    WHERE TT.ACCOUNT_MASK = '3578'
    GROUP BY TT.FIN_DATE, TT.CONTRACT_ID
    ),
    WCONTRACT_ACCOUNT_PIVOT AS (
    SELECT
    FIN_DATE,
    CONTRACT_ID,
    CONTRACT_BODY_ACCOUNT_ID,
    EXPIRE_BODY_ACCOUNT_ID,
    DEBTS_ACCOUNT_ID
    FROM (
    SELECT CON.CONTRACT_ID, ACCOUNT_ID, MACRO_CODE, FIN_DATE
    FROM WCONTRACT_ACCOUNT CON
    )
    PIVOT (
    MAX(ACCOUNT_ID) AS ACCOUNT_ID
    FOR MACRO_CODE IN (
    'CREDIT_REST' AS CONTRACT_BODY,
    'CREDIT_EXPIRE' AS EXPIRE_BODY,
    'CREDIT_ALL_DEBTS' AS DEBTS
    )
    )
    ),
    WPAYMENT AS (
    SELECT
    CAP.FIN_DATE,
    CAP.CONTRACT_ID,
    'BODY' AS PAYMENT_TYPE,
    DOC.POST_DATE AS PAYMENT_DATE,
    DOC.BASE_AMOUNT AS PAYMENT_AMOUNT_UAH
    -- DOC.DESCRIPTION
    FROM WCONTRACT_ACCOUNT_PIVOT CAP
    JOIN SR_BANK.COMPOUND_ACTION@PRODDB COA ON CAP.CONTRACT_ID = COA.COMPOUND_DOCUMENT_ID
    JOIN SR_BANK.DOCUMENT@PRODDB DOC ON COA.ACTION_ID = DOC.ACTION_ID
    AND DOC.ACCOUNT_DEBIT_ID = CAP.DEBTS_ACCOUNT_ID
    AND DOC.ACCOUNT_CREDIT_ID = ANY(
    CAP.CONTRACT_BODY_ACCOUNT_ID, CAP.EXPIRE_BODY_ACCOUNT_ID
    )
    ),
    WSCHEDULE AS (
    SELECT
    FIN_DATE,
    CONTRACT_ID AS CONTRACT_ID,
    IS_CLOSED,
    POST_DATE AS SCHEDULE_DATE,
    TRUNC(POST_DATE,'MM') AS SCHEDULE_MONTH,
    SCHEDULE_TYPE AS SCHEDULE_TYPE,
    AMOUNT_UAH AS SCHEDULE_AMOUNT_UAH
    FROM (
    SELECT
    CON.FIN_DATE,
    CASE
    WHEN CON.CONTRACT_CLOSE_DATE <= CON.FIN_DATE
    THEN 1
    ELSE 0
    END AS IS_CLOSED,
    PAY.*
    FROM SR_BANK.PAYMENT_SCHEDULE@PRODDB PAY
    JOIN WCONTRACT CON ON PAY.CONTRACT_ID = CON.CONTRACT_ID
    ) TT
    UNPIVOT INCLUDE NULLS(
    AMOUNT_UAH FOR SCHEDULE_TYPE IN (
    ANN_AMOUNT_REST AS 'BODY'
    )
    )
    ),
    WMUST_DATA AS (
    SELECT
    SCH.FIN_DATE AS FIN_DATE,
    SCH.SCHEDULE_TYPE AS RTYPE,
    SCH.CONTRACT_ID AS CONTRACT_ID,
    SUM(SCH.SCHEDULE_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WSCHEDULE SCH
    WHERE
    (
    SCHEDULE_DATE <= R_DATE AND IS_CLOSED = 0
    OR
    IS_CLOSED = 1
    )
    GROUP BY SCH.FIN_DATE, SCH.SCHEDULE_TYPE, SCH.CONTRACT_ID
    ),
    WDONE_DATA AS (
    SELECT
    PAY.FIN_DATE AS FIN_DATE,
    PAY.PAYMENT_TYPE AS RTYPE,
    PAY.CONTRACT_ID AS CONTRACT_ID,
    SUM(PAY.PAYMENT_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WPAYMENT PAY
    WHERE PAY.PAYMENT_DATE <= PAY.FIN_DATE
    GROUP BY PAY.FIN_DATE, PAY.PAYMENT_TYPE, PAY.CONTRACT_ID
    ),
    WFINAL_CONTAINER AS (
    SELECT
    RTY.RTYPE,
    CON.*
    FROM WCONTRACT CON,
    (
    SELECT 'BODY' AS RTYPE FROM DUAL
    UNION ALL
    SELECT 'COMMIS' AS RTYPE FROM DUAL
    ) RTY
    )
    SELECT
    CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
    CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
    ,CON.RTYPE
    ,COALESCE(MST.AMOUNT_UAH,0) -
    COALESCE(DON.AMOUNT_UAH,0) AS AMOUNT_UAH
    FROM WFINAL_CONTAINER CON
    LEFT JOIN WMUST_DATA MST ON CON.CONTRACT_ID = MST.CONTRACT_ID
    AND CON.RTYPE = MST.RTYPE
    LEFT JOIN WDONE_DATA DON ON CON.CONTRACT_ID = DON.CONTRACT_ID
    AND CON.RTYPE = DON.RTYPE
    WHERE CON.RTYPE = 'BODY'
    UNION ALL
    SELECT
    CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
    CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
    ,CON.RTYPE
    ,ECM.AMOUNT_UAH
    FROM WFINAL_CONTAINER CON
    LEFT JOIN WEXPIRED_COMMISSION ECM ON CON.CONTRACT_ID = ECM.CONTRACT_ID
    AND CON.RTYPE = ECM.RTYPE
    WHERE CON.RTYPE = 'COMMIS'
    ) M2
    ON (
    M1.RDATE = M2.FIN_DATE
    AND
    M1.RTYPE = M2.RTYPE
    AND
    M1.CONTRACT_ID = M2.CONTRACT_ID
    )
    WHEN MATCHED THEN
    UPDATE SET
    M1.RVALUE = M2.AMOUNT_UAH
    WHEN NOT MATCHED THEN
    INSERT (M1.RDATE, M1.CUSTOMER_ID, M1.CONTRACT_ID, M1.RTYPE, M1.RVALUE)
    VALUES (M2.FIN_DATE, M2.CUSTOMER_ID, M2.CONTRACT_ID, M2.RTYPE, M2.AMOUNT_UAH)
    ;
    ROWS_UPDATED := SQL%ROWCOUNT;
    -- MICRO.SP_LOG_OK('CALC_CASH_EXPIRED', TO_CHAR(R_DATE,'DD.MM.YYYY')||' - '||SQL%ROWCOUNT||' rows was affected');
    COMMIT; IF ROWS_UPDATED > 0 THEN
    RETURN 1;
    ELSE
    RETURN 0;
    END IF;

    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RETURN 0;

    END; -- EXCEPTION
    -- WHEN OTHERS
    -- THEN MICRO.SP_LOG_ERROR('CALC_CASH_EXPIRED', TO_CHAR(R_DATE,'DD.MM.YYYY')||' - '||SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 900));
    -- THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    -- RAISE;
    PROCEDURE CALC_REAL_CASH_EXPIRE_TEST_2(
    R_DATE DATE,
    P_UPDATED_ROW_COUNT OUT NUMBER -- Added parameter to return row count
    )
    IS
    l_row_count NUMBER; -- Variable to store the number of affected rows
    BEGIN
    -- Perform the MERGE operation
    MERGE INTO DWH.AD_CASH_FACT_EXPIRED_DATA M1
    USING (
    WITH
    WCONTRACT AS (
    SELECT
    R_DATE AS FIN_DATE,
    CON.CUSTOMER_ID AS CUSTOMER_ID,
    CUS.SHORT_NAME AS CUSTOMER_NAME,
    CON.ID AS CONTRACT_ID,
    CON.BEGIN_BASE_AMOUNT AS CONTRACT_BEGIN_AMOUNT_UAH,
    CON.BEGIN_DATE AS CONTRACT_BEGIN_DATE,
    CON.CLOSE_DATE AS CONTRACT_CLOSE_DATE
    FROM SR_BANK.CUSTOMER@PRODDB CUS
    JOIN SR_BANK.CONTRACT@PRODDB CON ON CUS.ID = CON.CUSTOMER_ID
    JOIN SR_BANK.CONTRACT_TYPE@PRODDB COT ON CON.TYPE_ID = COT.ID
    AND COT.SYS_NAME = 'I_POTR_GNV' -- тільки кеш кредити
    AND R_DATE BETWEEN CON.BEGIN_DATE AND COALESCE(CON.CLOSE_DATE,TRUNC(SYSDATE+10))
    AND CON.BEGIN_DATE < TO_DATE('01.10.2022','DD.MM.YYYY') -- треба брати тільки кредити, що були відкриті до жовтня 2022
    ),
    WCONTRACT_ACCOUNT AS (
    SELECT
    CON.FIN_DATE,
    CON.CUSTOMER_ID,
    CON.CUSTOMER_NAME,
    CON.CONTRACT_ID,
    -- CON.CONTRACT_NO,
    CON.CONTRACT_BEGIN_DATE,
    CA.ACCOUNT_ID AS ACCOUNT_ID,
    ACC.ACCOUNT_NUMBER AS ACCOUNT_NO,
    BAL.CODE AS ACCOUNT_MASK,
    GAM.SYS_NAME AS MACRO_CODE,
    ASN.BASE_AMOUNT AS ACCOUNT_AMOUNT_UAH
    FROM WCONTRACT CON
    JOIN SR_BANK.COMPOUND_DOCUMENT@PRODDB COD ON CON.CONTRACT_ID = COD.ID
    JOIN SR_BANK.CONTRACT_ACCOUNT@PRODDB CA ON CON.CONTRACT_ID = CA.CONTRACT_ID
    JOIN SR_BANK.CONTRACT_ACC_MACRO@PRODDB CAM ON CA.MACRO_ID = CAM.ID
    JOIN SR_BANK.GENERATOR_ACC_MACRO@PRODDB GAM ON CAM.MACRO_ID = GAM.ID
    JOIN SR_BANK.ACCOUNT@PRODDB ACC ON CA.ACCOUNT_ID = ACC.ID
    JOIN SR_BANK.BALANCE_ACCOUNT@PRODDB BAL ON ACC.BALANCE_ID = BAL.ID
    AND BAL.CODE IN ('2909','3578','2203')
    JOIN SR_BANK.ACCOUNT_SNAPSHOT@PRODDB ASN ON CA.ACCOUNT_ID = ASN.ACCOUNT_ID
    AND ASN.SNAPSHOT_DATE = CON.FIN_DATE
    ),
    WEXPIRED_COMMISSION AS (
    SELECT
    TT.FIN_DATE,
    'COMMIS' AS RTYPE,
    TT.CONTRACT_ID,
    SUM(TT.ACCOUNT_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WCONTRACT_ACCOUNT TT
    WHERE TT.ACCOUNT_MASK = '3578'
    GROUP BY TT.FIN_DATE, TT.CONTRACT_ID
    ),
    WCONTRACT_ACCOUNT_PIVOT AS (
    SELECT
    FIN_DATE,
    CONTRACT_ID,
    CONTRACT_BODY_ACCOUNT_ID,
    EXPIRE_BODY_ACCOUNT_ID,
    DEBTS_ACCOUNT_ID
    FROM (
    SELECT CON.CONTRACT_ID, ACCOUNT_ID, MACRO_CODE, FIN_DATE
    FROM WCONTRACT_ACCOUNT CON
    )
    PIVOT (
    MAX(ACCOUNT_ID) AS ACCOUNT_ID
    FOR MACRO_CODE IN (
    'CREDIT_REST' AS CONTRACT_BODY,
    'CREDIT_EXPIRE' AS EXPIRE_BODY,
    'CREDIT_ALL_DEBTS' AS DEBTS
    )
    )
    ),
    WPAYMENT AS (
    SELECT
    CAP.FIN_DATE,
    CAP.CONTRACT_ID,
    'BODY' AS PAYMENT_TYPE,
    DOC.POST_DATE AS PAYMENT_DATE,
    DOC.BASE_AMOUNT AS PAYMENT_AMOUNT_UAH
    -- DOC.DESCRIPTION
    FROM WCONTRACT_ACCOUNT_PIVOT CAP
    JOIN SR_BANK.COMPOUND_ACTION@PRODDB COA ON CAP.CONTRACT_ID = COA.COMPOUND_DOCUMENT_ID
    JOIN SR_BANK.DOCUMENT@PRODDB DOC ON COA.ACTION_ID = DOC.ACTION_ID
    AND DOC.ACCOUNT_DEBIT_ID = CAP.DEBTS_ACCOUNT_ID
    AND DOC.ACCOUNT_CREDIT_ID = ANY(
    CAP.CONTRACT_BODY_ACCOUNT_ID, CAP.EXPIRE_BODY_ACCOUNT_ID
    )
    ),
    WSCHEDULE AS (
    SELECT
    FIN_DATE,
    CONTRACT_ID AS CONTRACT_ID,
    IS_CLOSED,
    POST_DATE AS SCHEDULE_DATE,
    TRUNC(POST_DATE,'MM') AS SCHEDULE_MONTH,
    SCHEDULE_TYPE AS SCHEDULE_TYPE,
    AMOUNT_UAH AS SCHEDULE_AMOUNT_UAH
    FROM (
    SELECT
    CON.FIN_DATE,
    CASE
    WHEN CON.CONTRACT_CLOSE_DATE <= CON.FIN_DATE
    THEN 1
    ELSE 0
    END AS IS_CLOSED,
    PAY.*
    FROM SR_BANK.PAYMENT_SCHEDULE@PRODDB PAY
    JOIN WCONTRACT CON ON PAY.CONTRACT_ID = CON.CONTRACT_ID
    ) TT
    UNPIVOT INCLUDE NULLS(
    AMOUNT_UAH FOR SCHEDULE_TYPE IN (
    ANN_AMOUNT_REST AS 'BODY'
    )
    )
    ),
    WMUST_DATA AS (
    SELECT
    SCH.FIN_DATE AS FIN_DATE,
    SCH.SCHEDULE_TYPE AS RTYPE,
    SCH.CONTRACT_ID AS CONTRACT_ID,
    SUM(SCH.SCHEDULE_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WSCHEDULE SCH
    WHERE
    (
    SCHEDULE_DATE <= R_DATE AND IS_CLOSED = 0
    OR
    IS_CLOSED = 1
    )
    GROUP BY SCH.FIN_DATE, SCH.SCHEDULE_TYPE, SCH.CONTRACT_ID
    ),
    WDONE_DATA AS (
    SELECT
    PAY.FIN_DATE AS FIN_DATE,
    PAY.PAYMENT_TYPE AS RTYPE,
    PAY.CONTRACT_ID AS CONTRACT_ID,
    SUM(PAY.PAYMENT_AMOUNT_UAH) AS AMOUNT_UAH
    FROM WPAYMENT PAY
    WHERE PAY.PAYMENT_DATE <= PAY.FIN_DATE
    GROUP BY PAY.FIN_DATE, PAY.PAYMENT_TYPE, PAY.CONTRACT_ID
    ),
    WFINAL_CONTAINER AS (
    SELECT
    RTY.RTYPE,
    CON.*
    FROM WCONTRACT CON,
    (
    SELECT 'BODY' AS RTYPE FROM DUAL
    UNION ALL
    SELECT 'COMMIS' AS RTYPE FROM DUAL
    ) RTY
    )
    SELECT
    CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
    CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
    ,CON.RTYPE
    ,COALESCE(MST.AMOUNT_UAH,0) -
    COALESCE(DON.AMOUNT_UAH,0) AS AMOUNT_UAH
    FROM WFINAL_CONTAINER CON
    LEFT JOIN WMUST_DATA MST ON CON.CONTRACT_ID = MST.CONTRACT_ID
    AND CON.RTYPE = MST.RTYPE
    LEFT JOIN WDONE_DATA DON ON CON.CONTRACT_ID = DON.CONTRACT_ID
    AND CON.RTYPE = DON.RTYPE
    WHERE CON.RTYPE = 'BODY'
    UNION ALL
    SELECT
    CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
    CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
    ,CON.RTYPE
    ,ECM.AMOUNT_UAH
    FROM WFINAL_CONTAINER CON
    LEFT JOIN WEXPIRED_COMMISSION ECM ON CON.CONTRACT_ID = ECM.CONTRACT_ID
    AND CON.RTYPE = ECM.RTYPE
    WHERE CON.RTYPE = 'COMMIS'
    ) M2
    ON (
    M1.RDATE = M2.FIN_DATE
    AND
    M1.RTYPE = M2.RTYPE
    AND
    M1.CONTRACT_ID = M2.CONTRACT_ID
    )
    WHEN MATCHED THEN
    UPDATE SET
    M1.RVALUE = M2.AMOUNT_UAH
    WHEN NOT MATCHED THEN
    INSERT (M1.RDATE, M1.CUSTOMER_ID, M1.CONTRACT_ID, M1.RTYPE, M1.RVALUE)
    VALUES (M2.FIN_DATE, M2.CUSTOMER_ID, M2.CONTRACT_ID, M2.RTYPE, M2.AMOUNT_UAH)
    ; -- Get the number of rows affected
    l_row_count := SQL%ROWCOUNT;

    -- Commit the transaction
    COMMIT;

    -- Return the number of updated rows
    P_UPDATED_ROW_COUNT := l_row_count;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
    END; END PKG_VKASIAN_TEST;
    / ```
  • Rank 6 - Analytics Lead

    Any parameter has to be declared in your package and defined in the data model.

    What error is being returned.

    Rgds,

    Kevin

  • Rank 3 - Community Apprentice

    @Kevin M-Oracle

    I don't understand where else I need to add the parameter. I have specified the declaration both in the interface and in the code - in the body of the package procedure and function.

    I provided the code above, as well as the first screenshot of the parameter declaration in the BI.

  • Rank 6 - Analytics Lead

    In the trigger form simply call the procedure, do not try to declare the parameter.

    Rgds,

    Kevin

Welcome!

It looks like you're new here. Sign in or register to get started.