dbms_lob.writeappend 报错ORA-22921

 

执行下面语句时，在oracle11g上会报错，ORA-22921: length of input buffer is smaller than amount requested，但是在9i上执行不会报任何错误，很奇怪。

 

在oracle11g中必须把下面执行语句的lengthb改为length才会执行通过

 

declare

    c_content     clob:=EMPTY_CLOB();

    v_tmpstr     varchar2(400);

begin

    dbms_lob.createtemporary(c_content, TRUE);

    dbms_lob.open(C_content, dbms_lob.lob_readwrite); 

      v_tmpstr:=rpad('论坛',8,' ')||'  '||lpad('论坛',10,' ')||'  '||lpad('论坛',10,' ')||'  '||lpad('再论坛',10,' ')||chr(13)||chr(10);

    dbms_lob.writeappend(c_content,length(v_tmpstr),v_tmpstr);

      dbms_output.put_line('length输出:  '||c_content||'v_tmpstr值:    '||v_tmpstr||'lengthb字节数:    '||lengthb(v_tmpstr)||chr(10)||'length字符数:    '||length(v_tmpstr));

    dbms_lob.close(C_content);

    dbms_lob.createtemporary(C_content, TRUE);

    dbms_lob.open(C_content, dbms_lob.lob_readwrite); 

    dbms_lob.writeappend(c_content,lengthb(v_tmpstr),v_tmpstr);

      dbms_output.put_line(chr(10)||'lengthb输出:  '||c_content||'v_tmpstr值:    '||v_tmpstr||'lengthb字节数:    '||lengthb(v_tmpstr)||chr(10)||'length字符数:    '||length(v_tmpstr));

    dbms_lob.close(C_content);

end ;

 

 

 

-----9i下执行如下：

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

PL/SQL Release 9.2.0.8.0 - Production

CORE  9.2.0.8.0   Production

TNS for Linux: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production

SQL> set serveroutput on

SQL>

SQL> declare

  2      c_content     clob:=EMPTY_CLOB();

  3      v_tmpstr     varchar2(400);

  4  begin

  5      dbms_lob.createtemporary(c_content, TRUE);

  6      dbms_lob.open(C_content, dbms_lob.lob_readwrite);

  7        v_tmpstr:=rpad('论坛',8,' ')||'  '||lpad('论坛',10,' ')||'  '||lpad('论坛',10,' ')||'  '||lpad('再论坛',10,' ')||chr(13)||chr(10);

  8      dbms_lob.writeappend(c_content,length(v_tmpstr),v_tmpstr);

  9        dbms_output.put_line('length输出:  '||c_content||'v_tmpstr值:    '||v_tmpstr||'lengthb字节数:    '||lengthb(v_tmpstr)||chr(10)||'length字符数:    '||length(v_tmpstr));

 10      dbms_lob.close(C_content);

 11      dbms_lob.createtemporary(C_content, TRUE);

 12      dbms_lob.open(C_content, dbms_lob.lob_readwrite);

 13      dbms_lob.writeappend(c_content,lengthb(v_tmpstr),v_tmpstr);

 14        dbms_output.put_line(chr(10)||'lengthb输出:  '||c_content||'v_tmpstr值:    '||v_tmpstr||'lengthb字节数:    '||lengthb(v_tmpstr)||chr(10)||'length字符数:    '||length(v_tmpstr));

 15      dbms_lob.close(C_content);

 16  end ;

 17  /

length输出:  论坛            论坛        论坛      再论坛

v_tmpstr值:    论坛            论坛        论坛      再论坛

lengthb字节数:    46

length字符数:    37

lengthb输出:  论坛            论坛        论坛      再论坛

v_tmpstr值:    论坛            论坛        论坛      再论坛

lengthb字节数:    46

length字符数:    37

PL/SQL procedure successfully completed

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK

 

-----11g执行如下：

SQL> set serveroutput on

SQL>

SQL> declare

  2      c_content     clob:=EMPTY_CLOB();

  3      v_tmpstr     varchar2(400);

  4  begin

  5      dbms_lob.createtemporary(c_content, TRUE);

  6      dbms_lob.open(C_content, dbms_lob.lob_readwrite);

  7        v_tmpstr:=rpad('论坛',8,' ')||'  '||lpad('论坛',10,' ')||'  '||lpad('论坛',10,' ')||'  '||lpad('再论坛',10,' ')||chr(13)||chr(10);

  8      dbms_lob.writeappend(c_content,length(v_tmpstr),v_tmpstr);

  9        dbms_output.put_line('length输出:  '||c_content||'v_tmpstr值:    '||v_tmpstr||'lengthb字节数:    '||lengthb(v_tmpstr)||chr(10)||'length字符数:    '||length(v_tmpstr));

 10      dbms_lob.close(C_content);

 11      dbms_lob.createtemporary(C_content, TRUE);

 12      dbms_lob.open(C_content, dbms_lob.lob_readwrite);

 13      dbms_lob.writeappend(c_content,lengthb(v_tmpstr),v_tmpstr);

 14        dbms_output.put_line(chr(10)||'lengthb输出:  '||c_content||'v_tmpstr值:    '||v_tmpstr||'lengthb字节数:    '||lengthb(v_tmpstr)||chr(10)||'length字符数:    '||length(v_tmpstr));

 15      dbms_lob.close(C_content);

 16  end ;

 17  /

length输出:  论坛            论坛        论坛      再论坛

v_tmpstr值:    论坛            论坛        论坛      再论坛

lengthb字节数:    46

length字符数:    37

ORA-22921: length of input buffer is smaller than amount requested

ORA-06512: at "SYS.DBMS_LOB", line 1146

ORA-06512: at line 13

 

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK

 

 

关于DBMS_LOB.WRITEAPPEND包格式，官方如下

WRITEAPPEND procedures

This procedure appends a specified amount of data to the end of a LOB. The data is written from the buffer parameter. (Do not confuse this with the APPEND procedure.)

Note:

Also see "APPEND procedures", "COPY procedures", and "WRITE procedures".

Syntax

DBMS_LOB.WRITEAPPEND (

   lob_loc IN OUT NOCOPY BLOB,

   amount  IN            INTEGER,

   buffer  IN            RAW);

 

DBMS_LOB.WRITEAPPEND (

   lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,

   amount  IN            INTEGER,

   buffer  IN            VARCHAR2 CHARACTER SET lob_loc%CHARSET);

Parameters

Table 2-37 WRITEAPPEND procedure parameters

Parameter	Description
lob_loc	Locator for the LOB
amount	Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to write
buffer	Input buffer with data for the write

Usage notes

• There is an error if the input amount is more than the data in the buffer. If the input amount is less than the data in the buffer, then only the amount bytes or characters from the buffer are appended to the LOB.
• For a CLOB or NCLOB, the form of the VARCHAR2 buffer for the buffer parameter must be appropriate for the type of LOB. If the specified LOB is of type NCLOB, the buffer must contain NCHARdata. If the specified LOB is of type CLOB, the buffer must contain CHAR data.
• When calling WRITEAPPEND from a client, the buffer must contain data in the client character set. The database converts the client-side buffer to the server character set before it writes the buffer data to the LOB.
• It is recommended that you enclose write operations to the LOB with OPEN and CLOSE calls, but not mandatory. However, if you opened the LOB before performing the operation, you must close it before you commit or roll back the transaction.
• WRITEAPPEND gets the LOB, if necessary, before appending to it.

Exceptions

Table 2-38 WRITEAPPEND procedure exceptions

Exception	Description
VALUE_ERROR	Any of lob_loc, amount, or offset is null, out of range, or invalid.
INVALID_ARGVAL	Any of the following is true: amount < 1 or amount > 32767 bytes (or the character equivalent) or capacity of buffer
QUERY_WRITE	Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.)
BUFFERING_ENABLED	Cannot perform operation if LOB buffering is enabled on the LOB.

 

 

 

 

reference

http://docs.oracle.com/cd/E11882_01/timesten.112/e21645/d_lob.htm#TTPLP66759

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#ARPLS66611

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2106609/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2106609/