Oracle异常（二）

ORA-01704：字符串文字太长

当我们在Oracle数据库中插入或更新一个字符串时，如果字符串的长度超过数据库的限制，就会出现ORA-01704错误。这个错误的具体描述是“字符串文字太长”。

在Oracle数据库中，一个字符串的长度不能超过4000个字符。如果字符串超过了这个限制，将导致ORA-01704错误。

ORA-01704错误通常发生在以下几种情况：

1. 插入或更新数据时，字符串的长度超过了4000个字符。
2. 利用INSERT INTO语句插入数据时，字符串的长度超过了目标列的最大长度。
3. 使用BIND变量绑定的字符串，其长度超过了绑定变量的容量。

示例1：字符串长度超过4000字符

假设我们有一个表格employees，其中有一个comment列，其数据类型为VARCHAR2(4000)。

如果我们尝试插入一个长度超过4000字符的字符串，就会出现ORA-01704错误。例如：

INSERT INTO employees(comment) VALUES('这是一个超过4000字符的字符串，这是一个超过4000字符的字符串，这是一个超过4000字符的字符串，...')

要解决这个问题，我们需要缩短字符串的长度，确保它不超过4000个字符。

示例2：目标列的最大长度限制

在某些情况下，ORA-01704错误可能是由于目标列的最大长度限制导致的。例如，假设我们有一个表格products，其中有一个description列，其数据类型为VARCHAR2(2000)。

如果我们尝试将一个长度超过2000字符的字符串插入到这个列中，就会出现ORA-01704错误。例如：

INSERT INTO products(description) VALUES('这是一个超过2000字符的字符串，这是一个超过2000字符的字符串，这是一个超过2000字符的字符串，...')

要解决这个问题，我们需要缩短字符串的长度，确保它不超过目标列的最大长度。

示例3：BIND变量容量不足

在使用BIND变量时，如果我们绑定的字符串的长度超过了绑定变量的容量，也会出现ORA-01704错误。

例如，假设我们有一个表格orders，其中有一个comment列，其数据类型为VARCHAR2(100)。我们使用BIND变量:bind_comment将一个长度为200的字符串插入到这个列中。

DECLARE
  comment_text VARCHAR2(200) := '这是一个长度为200的字符串，这是一个长度为200的字符串，...';
BEGIN
  INSERT INTO orders(comment) VALUES(:bind_comment);
END;

由于BIND变量:bind_comment的容量为100，而我们尝试绑定一个长度为200的字符串，因此会出现ORA-01704错误。

要解决这个问题，我们需要缩短字符串的长度，确保它不超过BIND变量的容量。

clob类型还报错：

分析sql执行失败的原因，在于没有强制指定插入字符串为clob类型，解析sql时，oracle会把插入的字符串作为 “字符串类型”处理，由于oracle有最大字符串限制（不超过4000个字符），所以会报错。

DECLARE 
      clobValue EMR_PATIENT_BCLIST.BCCONTENT%TYPE; 
    BEGIN 
      clobValue := '{2}';  
      UPDATE EMR_PATIENT_BCLIST T SET T.BCCONTENT = clobValue WHERE REG_NO='{0}' AND ROW_NO='{1}' ; 
      COMMIT; 
    END;

 

原因：一般为包含有对CLOB字段的数据操作。如果CLOB字段的内容非常大的时候，会导致SQL语句过长。隐式转换：oracle默认把字符串转换成varchar2类型，而这个字符串的长度,又比4000大，所以会报ora-01704错误。说得通俗一点，就是两个单引号之间的字符不能超过4000。

解决办法：使用PL/SQL语法，采取绑定变量的方式解决，而不是直接拼接SQL

DECLARE  
  clobValue 表名.字段名%TYPE;  
BEGIN  
  clobValue := 'XXX'; --字段内容  
  UPDATE 表名 T SET T.字段名 = clobValue WHERE 条件;  
  COMMIT;  
END;  
/

1   插入字符串对应的表字段类型要为clob类型，我试过long类型的不行
2   采用pl/sql语法绑定变量解决(结合3，这说法感觉不是很准确)，网上有说使用存储过程进行解决(我个人认为本质也是使用绑定变量的方式)，但个人发现字符串长度大于4000后还是不行
如：

DECLARE    v_patientSignName  clob;
BEGIN  
             v_patientSignName:='大字符串1abc大字符串2bbb大字符串3' ；
             insert into POOR.COVID19MESSAGE(isHot, patientSignName)  values(1,  v_patientSignName);
END;

3   当插入或者更新得字符串长度在4000-32767，就需要把字符串进行拆分，使用||进行字符串拼接(目的是使用pl/sql语法)，如上面的   v_patientSignName:='大字符串1abc大字符串2bbb大字符串3' 改为v_patientSignName:='大字符串1abc'||'大字符串2bbb'||'大字符串3' 
从Oracle官网PL/SQL Data Types中可以看到
(在sql或pl/sql中，即使表字段定义为clob类型，也会转成varchar2类型)varchar2类型在pl/sql的大小是32767，在sql的大小是4000

下面用C#写了一个demo类进行拼接，字符串拆分长度为4000

 public class ConvertClass
    {
        /// <summary>
        /// 把字符串使用||进行拼接，如  signName="abcdefg...adbdefg...adbdefg..."   转换结果为"'abcdefg..'||'adbdefg...'||'adbdefg...'"
        /// </summary>
        /// <param name="signName">要拆分并使用||进行拼接得字符串</param>
        /// <returns>拼接完毕的字符串</returns>
        public static string ConvertString(string signName)
        {
            int Strlength = signName.Length;
            int Section = Strlength / 4000;
            if (Section == 0)
            {
                return "'"+signName+"'";
            }
            string targetString = "";
            int StartIndex = 0;
            for (int i = 0; i < Section; i++)
            {
                if (i < (Section - 1)&&i>0)
                {
                    string subString = signName.Substring(StartIndex, 4000);
                    targetString += subString + "'||'";
                    StartIndex += 4000;
                }
                else if (i == 0)
                {
                    string subString = signName.Substring(StartIndex, 4000);
                    targetString += "'"+subString + "'||'";
                    StartIndex += 4000;
                }
                else
                {
                    string subString = signName.Substring(StartIndex);
                    targetString += subString + "'";
                }
            }
            return targetString;
        }
    }

4  当插入或者更新的字符串长度大于32767，就需要在代码中使用参数的形式，同时，要插入大字符串的表字段类型为clob,下面的是C#代码采用参数的形式(相信其它语言也是类似的做法)

//p1作为参数
string sql="insert into 表名(id,strValue) values(1,:p1)";
string data="你的大字符串";
OracleConnection conn = new OracleConnection('连接字符串');
OracleCommand cmd = new OracleCommand(sql, conn);
OracleParameter p1 = new OracleParameter("p1", OracleDbType.Clob);
p1.Value = data;
cmd.Parameters.Add(p1);
try
{
     conn.Open();
     cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
 
     Console.WriteLine(ex);
}