ORA-01775: 同义词循环链，但没有同义词 [英] ORA-01775: looping chain of synonyms but there are no synonyms

问题描述

无法弄清楚为什么我在此存储过程的第 52 行收到SQL 语句被忽略"和ORA-01775:同义词循环链".有什么想法吗?

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?

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;

推荐答案

我不知道你为什么会收到同义词错误.但是对于应该是单个 DELETE 语句的东西来说，这是很多代码.我假设您已将其更改为 commit-every-n 以避免回滚错误.如果您能让您的 DBA 增加撤消空间，这样您就可以真正完成您需要做的工作，那就太好了.如果做不到这一点，我认为你仍然可以让它变得更简单:

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.

这篇关于ORA-01775: 同义词循环链，但没有同义词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！