Why is oracle returning an ORA-02046 error?

Question

Oracle

Hello.
today I made a small change in the procedure for calculating statistics values ​​for monitoring
in the calculate_1 procedure on a remote server, a check of the PARAMETER_ID value for null or not null was added to mask possible exceptions of the absence of an ID in the directory

OPEN CALCULATE_PARAMETER; 
    LOOP
      FETCH CALCULATE_PARAMETER INTO CALCULATE_PARAMETER_ARRAY; 
      EXIT WHEN CALCULATE_PARAMETER%NOTFOUND;  
      if (CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE is null) then CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE:=0; end if;
      IF (CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE is not null)
        THEN
    /* ADDED from this */
        PARAMETER_ID:=null; 
        BEGIN
     EXECUTE_TEXT:='select t.PARAMETER_ID from [email protected] t '||
    'where '||
    't.server_id IN (select server_id from [email protected] a where upper(a.location) like upper(''%Moskow%'')) '||
    'and '||           
    't.parameter_name=:PARAMETER_NAME';
       EXECUTE IMMEDIATE EXECUTE_TEXT INTO PARAMETER_ID USING CALCULATE_PARAMETER_ARRAY.PARAMETER_NAME;
          EXCEPTION
          when OTHERS then begin /*dbms_output.put_line(PARAMETER_NAME);*/ goto PARAMETER_ID_SELECT; end;
          end;     
          <<PARAMETER_ID_SELECT>> 

        if (PARAMETER_ID is not null)
        THEN  
/* to this */
        insert into [email protected]
        (PARAMETER_VALUE_ID, PARAMETER_ID, PARAMETER_VALUE, DATE_CURRENT_CHECK)
        values
         ([email protected],

    (select PARAMETER_ID from [email protected] t
    where
    t.server_id IN (select server_id from [email protected] a where upper(a.location) like upper('%Moskow%'))
    and
    t.parameter_name=CALCULATE_PARAMETER_ARRAY.PARAMETER_NAME),
         CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE,
         sysdate);
        commit;
        END IF;
        END IF;
    END LOOP;

further when trying to execute

begin
[email protected]_link1;
[email protected]_link1;
[email protected]_link1;
end;

oracle began to return an error
ORA-02046: Distributed transaction already begun
Why is oracle returning this error and recommend what should be done to solve this problem?
ps
1. Oracle version - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2. select * from dba_2pc_pending;
returns no rows
3. remote database procedures calculate_1, calculate_2, calculate_3 are valid.

Answer 1

You have a LOOP in your task , and the loop can take a long time.
The ORA-02046 error means that the previous execution has not yet completed.
The standard solution is to put the set transaction read only command before the loop, but in your case, when you use insert, this will not work.

Answer 2

How many parameters do you have there that such simple selects and inserts take a long time to complete?
Try to create a temporary table, insert rows there during the loop, and after the loop move the rows to a regular table.