Running Procedure Remotely - getting error - need help pls.

Hello,

Can anyone can help me here.

The basic problem originally is I have a stored procedure on a remote computer which calls other stored procedures which process data and does commits and rollbacks. Oracle does not like this when done remotely. I get the error: ORA-2074 - Cannot rollback in a distributed transaction ORA-2064 - Distributed Operation not supported.

Both Remote and host servers have the distributed process turned-on.

So my handle is doing the following utilizing an Oracle Job to try and by-pass I wrote simple test procedures and it is able to update and commit without getting the above errors, but I get another error. The following is what I did:

On my remote server I have 2 procedures. One that inserts and runs an Oracle Job and another is a simple update that the Oracle Job runs. When running from SQLPLus or a simple Test form it returns the Error ORA-02068: following severe error from REMOTE_CONNECT. REMOTE_CONNECT is the database link I have set up that points my host computer to the remote: The DB_LINK REMOTE_CONNECT is set to point to a DB on an IBM AIX box and the HOST Oracle DB instance is a DEC Alpha.(I have tested also from a DEC boc to NT with the same results - not platform dependent) I have set up a synonym on the host that points to the Oracle Jobs procedure on the remote server.

When it runs the Oracle Job is executed and it does update the tables per the script that the Oracle Job executes - no problems there; however, the ORA-02068 error is returned. The SUBMIT of the job is fine. The error is given when I RUN the job. Again it does the proces fine but it returns the error

(1) 1st question is it true that commits and rollbacks cannot be in procedures that are called remotely, all my tested and the Oracle error documentation points to that? Any ideas on a work-around short of re-writing the stored procedures so I can utilize exits procs with the commits/rollacks?

(2) On my test using the Oracle Jobs, what is this 02068 error and how do I handle? If this is handled I believe everything will work great for me.

Any one have ideas?

Here are samples:

create or replace
-- This procedure inserts and runs the Oracle Job.
PROCEDURE jason_job IS
j number;
tmp_msg varchar2(2000);
tmp_num number;
BEGIN
DBMS_JOB.SUBMIT(j, 'jason_test;', sysdate, 'sysdate+1'); DBMS_JOB.RUN( job => j);

EXCEPTION when OTHERS then
tmp_num := sqlcode;
tmp_msg := sqlerrm;

END;
/
-- The following is a test proceedure that will be fired by a dbms_job

• It does the update but returns the ORA-02068 error. create or replace PROCEDURE jason_test is tmp_name varchar2(30); tmp_msg varchar2(2000); tmp_num number; BEGIN select first_name into tmp_name from acct where acct_no = 5;

update acct_c set last_name = last_name||'1', extra_a = tmp_name||extra_a where acct_no = 5;
commit;

END;
/

Any help is greatly appreciated. Please contact me at JASONL_at_TEKLAB.COM and JAL_at_ESC188.EXPANSION.COM Sincerely,

JasonL Received on Mon Sep 07 1998 - 00:00:00 CDT