title: ORA-01775: looping chain of synonyms but there are no synonyms
body: <p>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?</p>
  
  <pre><code>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 &lt; :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 &gt; 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 &gt; 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;
  </code></pre>