1

I made small changes in a procedure to calculate value for statistic monitoring.

In procedure calculate_1 on remote DB, I added check null or no null - PARAMETER_ID

    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 SERVERS_PARAMETERS_DIR@BMCDB t '||
    'where '||
    't.server_id IN (select server_id from servers_dir@BMCDB 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 SERVERS_PARAMETERS@BMCDB
        (PARAMETER_VALUE_ID, PARAMETER_ID, PARAMETER_VALUE, DATE_CURRENT_CHECK)
        values
         (SERVERS_PARAMETERS_ID.nextval@BMCDB,

    (select PARAMETER_ID from SERVERS_PARAMETERS_DIR@BMCDB t
    where
    t.server_id IN (select server_id from servers_dir@BMCDB 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;

try run this code

begin
calculate_1@db_link1;
calculate_2@db_link1;
calculate_3@db_link1;
end;

Execution finished with the following error

ORA-02046: Distributed transaction already begun

Why Oracle returned this error and how resolve this problem?

p.s.

  • Oracle version - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

  • select * from dba_2pc_pending; returned no rows

  • procedures on remote DB, calculate_1, calculate_2and calculate_3 are valid.

  • What was the 'small change' that you made ? Did you add 'calculate_2' and 'calculate_3' to the procedure ? – BriteSponge Jan 25 '17 at 12:34
  • added information in question body – Nikolay Baranenko Jan 25 '17 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.