Oracle異常（二）

ORA-01704：字符串文字太長

當我們在Oracle數據庫中插入或更新一個字符串時，如果字符串的長度超過數據庫的限制，就會出現ORA-01704錯誤。這個錯誤的具體描述是“字符串文字太長”。

在Oracle數據庫中，一個字符串的長度不能超過4000個字符。如果字符串超過了這個限制，將導致ORA-01704錯誤。

ORA-01704錯誤通常發生在以下幾種情況：

插入或更新數據時，字符串的長度超過了4000個字符。
利用INSERT INTO語句插入數據時，字符串的長度超過了目標列的最大長度。
使用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);
}

posted @ 2024-05-11 13:14 IT苦行僧-QF 閱讀(1510) 評論(0) 收藏舉報

刷新頁面返回頂部

IT苦行僧-QF