ORA-01795: maximum number of expressions in a list

解决 ora-01795 问题

找出抛异常时执行的SQL语句，貌不惊人啊，很平常一SQL语句，内容类似：SELECT * FROM DUAL T WHERE T.DUMMY IN ('1', '2', '3', ...)，只是IN后括号里的主键值多了些，其它没啥特别的。

看ORA-01795中给出的内容是SQL语句的 expressions 中list接受的最大值是1000，查了下ORA-01795的说明，确定问题出在IN后括号里的主键值超过1000上。

解决思路：将 SQL 中超过1000个的in list转换为 id in (1,2,3...999) or id in (1000,1001....1999) ...

 

namespace MetarNet.MetarView.Toolkit
{
    public class Oracle01795Helper
    {
        private string m_SQL = string.Empty;

        private List m_SqlSpliter = new List();

        public Oracle01795Helper(string m_SQL)
        {
            this.m_SQL = m_SQL;

            m_SqlSpliter.Add(" ");
            m_SqlSpliter.Add("\n");
            m_SqlSpliter.Add("\r");
            m_SqlSpliter.Add("(");
            m_SqlSpliter.Add(")");

            Convert();
        }

        private void Convert()
        {
            if (string.IsNullOrEmpty(m_SQL))
                return;

            string sql = m_SQL.ToLower();

            int index = sql.IndexOf("in");

            Dictionary replaceSql = new Dictionary();

            while (index >= 0)
            {
                string col;
                int colIndex = GetBeforeWordBeginPosition(sql, index, out col);
                if (colIndex > 0)
                {

                    string condition = string.Empty;
                    int conditionIndex = GetAfterWordEndPosition(sql, index, out condition);

                    if (conditionIndex >= 0)
                    {
                        string[] ids = SplitCondition(condition);
                        if (ids.Length < 1000)
                        {
                            index = sql.IndexOf("in", index + 1);
                            continue;
                        }
                        else
                        {
                            // 分解 id in list
                            string convertedSql = ConvertCondition(col, ids);
                            string key = m_SQL.Substring(colIndex, conditionIndex - colIndex + 1);
                            if (!replaceSql.ContainsKey(key))
                                replaceSql.Add(key, convertedSql);
                        }
                    }
                }

                // 处理下一个 in
                index = sql.IndexOf("in", index + 1);
            }

            if (replaceSql.Count > 0)
            {
                foreach (string key in replaceSql.Keys)
                {
                    m_SQL = m_SQL.Replace(key, replaceSql[key]);
                }
            }
        }

        private int GetBeforeWordBeginPosition(string sql, int inIndex, out string beforeIn)
        {
            System.Collections.Generic.Stack beforeInCharList = new Stack();

            bool beginInput = false;

            int i;
            for (i = inIndex - 1; i >= 0; i--)
            {
                string currentChar = sql.Substring(i, 1);

                if (!beginInput)
                {
                    if (m_SqlSpliter.Contains(currentChar))
                    {
                        beginInput = true;
                        continue;
                    }
                    else
                    {
                        beforeIn = string.Empty;
                        return -1;
                    }
                }

                if (beginInput)
                {
                    if (m_SqlSpliter.Contains(currentChar))
                    {
                        if (beforeInCharList.Count == 0)
                            continue;
                        else
                            break;
                    }

                    beforeInCharList.Push(currentChar);
                }
            }

            StringBuilder sbBeforeIn = new StringBuilder();
            while (beforeInCharList.Count > 0)
                sbBeforeIn.Append(beforeInCharList.Pop());

            beforeIn = sbBeforeIn.ToString();
            return i;
        }

        private int GetAfterWordEndPosition(string sql, int inIndex, out string afterIn)
        {
            System.Collections.Generic.Queue afterCharList = new Queue();

            afterIn = string.Empty;

            bool afterInput = false;
            bool lookupBracket = false;

            int i;
            bool isInContent = false;    // 标示当前字符是否在 '' 之间
            bool hasComma = false;

            for (i = inIndex + 2; i < sql.Length; i++)
            {
                string currentChar = sql.Substring(i, 1);
                if (!afterInput)
                {
                    if (m_SqlSpliter.Contains(currentChar))
                    {
                        afterInput = true;
                        continue;
                    }
                }

                if (afterInput & !lookupBracket)
                {
                    if (currentChar != "(" && m_SqlSpliter.Contains(currentChar))
                    {
                        continue;
                    }
                    else
                    {
                        if (currentChar == "(")
                        {
                            lookupBracket = true;
                            afterCharList.Enqueue(currentChar);

                            continue;
                        }
                        else
                        {
                            // in 关键字后面第一个字符不是 (
                            return -1;
                        }

                    }
                }

                if (lookupBracket)
                {
                    afterCharList.Enqueue(currentChar);

                    if (currentChar == "'")
                        isInContent = !isInContent;

                    if (!isInContent && currentChar == ",")
                        hasComma = true;

                    if (!isInContent && currentChar == "(") // 不处理括号嵌套的语句
                        return -1;

                    if (!isInContent && currentChar == ")")
                        if (hasComma)
                            break;
                        else
                            return -1;
                }
            }

            StringBuilder sbAfterIn = new StringBuilder();
            while (afterCharList.Count > 0)
                sbAfterIn.Append(afterCharList.Dequeue());

            afterIn = sbAfterIn.ToString();

            if (i == sql.Length)
                return -1;
            else
                return i;
        }

        private string[] SplitCondition(string condition)
        {
            string con = condition.Substring(1, condition.Length - 2);
            return con.Split(',');
        }

        private string ConvertCondition(string col, string[] ids)
        {
            List> idList = new List>();

            List currentList = null;

            if (ids.Length > 0)
            {
                currentList = new List();
                idList.Add(currentList);
            }

            foreach (string id in ids)
            {
                if (currentList.Count > 999)
                {
                    currentList = new List();
                    idList.Add(currentList);
                }

                currentList.Add(id);
            }

            List sqlList = new List();
            foreach (List list in idList)
            {
                if (list.Count > 0)
                {
                    sqlList.Add(col + " in (" + string.Join(",", list.ToArray()) + ")");
                }
            }

            if (sqlList.Count == 0)
                return string.Empty;
            else
            {
                return " (" + string.Join(" or ", sqlList.ToArray()) + ") ";
            }
        }

        public override string ToString()
        {
            return m_SQL;
        }
    }
}