ORA-01775: looping chain of synonyms but there are no synonyms

Question

Can't figure out why I'm getting 'SQL Statement ignored' and 'ORA-01775: looping chain of synonyms' on line 52 of this stored procedure. Got any ideas?

Thanks, Tony

CREATE OR REPLACE PACKAGE PURGE_LOG_BY_EVENT_DAYS AS

TYPE dual_cursorType IS REF CURSOR RETURN dual%ROWTYPE;

PROCEDURE log_master_by_event_days (event_id NUMBER, purge_recs_older_than NUMBER, result_cursor OUT dual_cursorType);

END PURGE_LOG_BY_EVENT_DAYS;

/

CREATE OR REPLACE PACKAGE BODY PURGE_LOG_BY_EVENT_DAYS AS

err_msg VARCHAR2(4000);

PROCEDURE log_master_by_event_days (event_id NUMBER, purge_recs_older_than NUMBER, result_cursor OUT dual_cursorType) IS

TYPE type_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER; TYPE type_ref_cur IS REF CURSOR;

l_rid type_rowid; c1 type_ref_cur;

l_sql_stmt VARCHAR2(4000); proc_start_time DATE := sysdate; purge_date DATE;

l_bulk_collect_limit NUMBER := 1000; retry NUMBER := 5; retry_count NUMBER := 0; loop_count NUMBER := 0;

err_code VARCHAR2(10);

BEGIN

purge_date := to_date(sysdate - purge_recs_older_than);

l_sql_stmt := ''; l_sql_stmt := l_sql_stmt ||' SELECT rowid FROM LOG_MASTER '; l_sql_stmt := l_sql_stmt ||' WHERE last_changed_date < :purge_date'; l_sql_stmt := l_sql_stmt ||' AND event_id = :event_id';

-- The following while loop -- executes the purge code -- 'retry' number of times in case of ORA-01555

WHILE retry > 0 LOOP

BEGIN

  -- START of purge code
  OPEN c1 FOR l_sql_stmt USING purge_date, event_id;
  LOOP

    FETCH c1 BULK COLLECT into l_rid LIMIT l_bulk_collect_limit;

    FORALL i IN 1..l_rid.COUNT
      DELETE from log_master
       WHERE rowid = l_rid(i);
    COMMIT;

    loop_count := loop_count + 1;
    EXIT WHEN c1%NOTFOUND;

  END LOOP;
  CLOSE c1;

  -- End of purge code
  -- if processing reached this point
  -- Process completed successfuly, set retry = 0 to exit loop
  retry := 0;

EXCEPTION
WHEN OTHERS THEN
  -- ====================================
  -- Get error msg
  -- ====================================

  ROLLBACK;
  err_code := sqlcode;

  dbms_output.put_line(err_code);

  -- ====================================
  -- Check if it is 01555
  -- if so retry, else exit loop
  -- ====================================

  retry := retry - 1;
  if err_code = '-1555' and retry > 0 THEN
    CLOSE c1;
    retry_count :=  retry_count + 1;
  else
    err_msg := sqlerrm;
    exit;
  end if;
END;

END LOOP;

IF err_msg IS NULL THEN

open result_cursor for select '1 - PURGE_LOG_BY_EVENT_DAYS ran successfully (event_id : '||event_id||', loop_count : '||loop_count||', bulk_limit : '||l_bulk_collect_limit||', retries : '||retry_count||') ' from dual;

ELSE

open result_cursor for select '2 - PURGE_LOG_BY_EVENT_DAYS After (event_id : '||event_id||', loop_count : '||loop_count||', bulk_limit : '||l_bulk_collect_limit||', retries : '||retry_count||') with Error: ' || err_msg from dual;

END IF;

END log_master_by_event_days;

END PURGE_LOG_BY_EVENT_DAYS;

/

Answer 1

Do the answers from here: http://stackoverflow.com/questions/247090/how-to-debug-ora-01775-looping-chain-of-synonyms help?

Answer 2

SELECT table_owner, table_name, db_link FROM dba_synonyms WHERE owner = 'PUBLIC' and db_link is not null

returns 0 rows as far as i know, there are no synonyms.

Answer 3

I have no idea why you're getting the synonym error. But that's a lot of code for something that should be a single DELETE statement. I assume you've changed it to commit-every-n to avoid rollback errors. It would be nice if you could get your DBA to increase the undo space so you can actually do the work you need to do. Failing that, I think you can still make it much simpler:

LOOP
  DELETE FROM log_master
    WHERE last_changed_date < :purge_date
      AND event_id = :event_id
      AND rownum <= :batch_delete_limit
    USING purge_date, event_id, l_bulk_collect_limit;
  EXIT WHEN SQL%NOTFOUND;
END LOOP;

And you can throw your retry logic around that if you want.

Apologies if I've missed some subtlety that makes this different from what you're doing.