ORA-01461的解決過程~~

近日生產庫中的一個過程報出了ora-1461的錯誤，雖然錯誤實際處理起來非常簡單，但解決過程中與yangtingkun老大就該問題思想火花的碰撞還是讓我頗有收益。

建立一個最簡單的示例演示一下錯誤的觸發：

SQL> CREATE TABLE T_VARCHAR (ID VARCHAR2(10));

 

Table created

SQL> DECLARE

  2  V_STR VARCHAR2(10000) := lpad('a',10000,'a');

  3  BEGIN

  4   INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,10));

  5  END;

  6  /

 

DECLARE

V_STR VARCHAR2(10000) := lpad('a',10000,'a');

BEGIN

 INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,10));

END;

 

ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-06512: at line 5

一條最簡單的insert居然報錯了，有意思吧，是因為v_str的值超長？不應該啊，因為我們在insert時是執行了substr的，甚至說將substr改成這樣，還是會報錯：

SQL> DECLARE

  2  V_STR VARCHAR2(10000) := lpad('a',10000,'a');

  3  BEGIN

  4   INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,1));

  5  END;

  6  /

 

DECLARE

V_STR VARCHAR2(10000) := lpad('a',10000,'a');

BEGIN

 INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,1));

END;

 

ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-06512: at line 5

 

顯然不會是因為多位元組擷取問題導致的插入失敗，而且如果是插入字元超出列長度的話，應該是報這個錯誤才對：

SQL> insert into t_varchar values (lpad('a',11,'a'));

 

insert into t_varchar values (lpad('a',11,'a'))

 

ORA-12899: value too large for column "TEST"."T_VARCHAR"."ID" (actual: 11, maximum: 10)

下面，改一下我們的插入語句：

SQL> DECLARE

  2  V_STR VARCHAR2(10000) := lpad('a',10000,'a');

  3  BEGIN

  4   V_STR := SUBSTR(V_STR,1,10);

  5   INSERT INTO T_VARCHAR VALUES (V_STR);

  6  END;

  7  /

 

PL/SQL procedure successfully completed

這下成功了，似乎有點兒頭緒，再改一下：

SQL> DECLARE

  2  V_STR CHAR(4000) := 'a';

  3  BEGIN

  4   INSERT INTO T_CHAR VALUES (SUBSTR(V_STR,1,10));

  5  END;

  6  /

 

PL/SQL procedure successfully completed

也成功了。ok，基本確認問題所在，總結如下：

字元型別在pl/sql中做為變數存大，最大可支援32767個位元組，但在sql中通常只能夠支援到4000位元組(char/nchar為2000)，因此如果宣告的變數長度超出了sql中型別長度，並且變數實際值也超出型別可接受最大值時，就會觸發ORA-01461錯誤，解決方法自然相當簡單，只要在插入/更新之前擷取字元長度到符合要求的長度就可以了。

另外，這裡需要注意，一定要在插入/更新之前擷取，而不要在插入/更新時substr，如例：

SQL> DECLARE

  2  V_STR VARCHAR2(10000) := lpad('a',10000,'a');

  3  BEGIN

  4   INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,10));

  5  END;

  6  /

 

DECLARE

V_STR VARCHAR2(10000) := lpad('a',10000,'a');

BEGIN

 INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,10));

END;

 

ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-06512: at line 5

這樣也仍然是會出錯的，這應該是由於pl/sql引擎在編譯時對變數長度的判斷先於substr進行，因此不等執行就先報錯了。