ORA-02064 - problem with commit in a remote function/procedure

From: Henrik Bachmann <Henrik.Bachmann_at_bim-consulting.de>
Date: 1998/11/24
Message-ID: <365a6ccb.93980546_at_news.topnet.de>#1/1

Hi there anybody!

I need help with the following database link:

CREATE DATABASE LINK dbLink
CONNECT TO user IDENTIFIED BY pwd
USING 'db';

This PL/SQL-Code starts a local function, wich will starts also a call to a remote
function (test_remote2):

DECLARE

    id          VARCHAR2(8) := 'xxxxxxxx';
    v_rc        INTEGER;

BEGIN
    v_rc:= test_lokal (id);
    DBMS_OUTPUT.PUT_LINE('v_rc: '||TO_CHAR(v_rc)); END; There are no transactions in the local function:

CREATE OR REPLACE FUNCTION test_lokal

    (   id                         VARCHAR2  ) RETURN INTEGER IS

BEGIN
    DECLARE

        x_rc                            INTEGER := 0;
    BEGIN
        BEGIN
            --- commit;
            x_rc:= test_remote2_at_dbLink(id);
            IF x_rc <> 0 THEN
                DBMS_OUTPUT.PUT_LINE('x_rc : '||TO_CHAR(x_rc));
                x_rc:= 1;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                DECLARE
                    x_error_code    NUMBER          := SQLCODE;
                    x_error_msg     VARCHAR2(1000)  := SQLERRM;
                BEGIN
                    x_rc := 1;
                    DBMS_OUTPUT.PUT_LINE('Fehler in test_lokal!
'||SUBSTR(x_error_msg, 1, 200));
                END;
        END;
        RETURN x_rc;

    END;
END test_lokal;
/

The transaction is only in the remote function. If I want to commit this transaction I run into an
oracle error: ORA-02064. The problem is the commit on the remote site, but why and what can I do? I need this commit on the remote site. The local site needs only the return value of the remote function (0 ... success, 1 ... error).

CREATE OR REPLACE FUNCTION test_remote2

    (   id                         VARCHAR2) RETURN INTEGER IS

BEGIN
    DECLARE

        x_err_nr                    INTEGER := 0;
    BEGIN
        commit;			--- here is the problem
        RETURN x_err_nr;
    EXCEPTION
        WHEN OTHERS THEN
            DECLARE
                x_error_code    NUMBER          := SQLCODE;
                x_error_msg     VARCHAR2(1000)  := SQLERRM;
            BEGIN
                x_err_nr:= x_error_code;
                dbms_output.put_line('Fehler in test_remote2!
'||SUBSTR(x_error_msg, 1, 200));
                RETURN x_err_nr;
            END;

    END;
END test_remote2;
/

Remote - Server: Oracle8 Enterprise Edition Release 8.0.4.1.3

• PL/SQL: PL/SQL Release 8.0.4.1.0

           or

• Oracle7 Server Release 7.3.4.1.0 with the 64-bit option - Production With the distributed, replication, parallel query and Spatial Data options
• PL/SQL Release 2.3.4.1.0 - Production

Local - Server: Oracle8 Enterprise Edition Release 8.0.4.0.0

• PL/SQL: PL/SQL Release 8.0.4.0.0

           or

• Oracle7 Server Release 7.3.4.1.0 with the 64-bit option - Production With the distributed, replication, parallel query and Spatial Data options
• PL/SQL Release 2.3.4.1.0 - Production

I hope anybody can help me.

Best regards

Henrik Bachmann

B.I.M.-Consulting Magdeburg mailto:Henrik.Bachmann_at_bim-consulting.de Lorenzweg 37-45
D-39124 Magdeburg Received on Tue Nov 24 1998 - 00:00:00 CET