Oracle NCLOB 问题

发布于 2024-09-11 14:23:34 字数 1491 浏览 8 评论 0原文

我有以下功能：

CREATE OR REPLACE FUNCTION GetVarchar2 (iclCLOB IN Nvarchar2)
return NVARCHAR2
IS
cnuMAX_LENGTH Constant number := 32767 ;
nuLength Number := DBMS_LOB.getlength(iclCLOB);
sbBuffer Nvarchar2(32767);
begin
dbms_lob.read(iclCLOB,nuLength,1,sbBuffer);
return sbBuffer;
END;

当我这样调用它时：

select GetVarChar2(text) from posts where postid = 'anId';

我收到此错误：

ORA-22835: 缓冲区太小，无法进行 CLOB 到 CHAR 或 BLOB 到 RAW 的转换（实际：6058，最大：2000）
22835. 00000 - “缓冲区太小，无法进行 CLOB 到 CHAR 或 BLOB 到 RAW 的转换（实际值：%s，最大值：%s）”
*原因：尝试将 CLOB 转换为 CHAR 或将 BLOB 转换为 RAW，其中
LOB 大小大于 CHAR 和 RAW 的缓冲区限制
类型。
请注意，如果字符长度，则宽度以字符为单位报告语义对列有效，否则宽度为以字节为单位报告。
*操作：执行以下操作之一
1. 在执行转换之前先缩小 LOB，
例如，通过在 CLOB 上使用 SUBSTR
2. 使用DBMS_LOB.SUBSTR 将CLOB 转换为CHAR 或将BLOB 转换为RAW。

问题在于 posts 表中的文本大小为 NCLOB 类型，为 6059 字节。这很奇怪，因为当我在不调用函数的情况下执行此操作时，它运行良好。即当我运行以下脚本时：

declare 
    iclCLOB nvarchar2(6100) := 'Here is the same 6059 bytes string';
    cnuMAX_LENGTH number := 32767 ;
    nuLength Number := DBMS_LOB.getlength(iclCLOB);
    sbBuffer Nvarchar2(32767);
    sbBuffer1 Nvarchar2(32767);
begin
    dbms_lob.read(iclCLOB,nuLength,1,sbBuffer);
    select GetVarChar2(text) into sbBuffer1 from posts where postid = 'anId';
end;

它运行没有任何问题。

谢谢。

原文

I have the following function:

CREATE OR REPLACE FUNCTION GetVarchar2 (iclCLOB IN Nvarchar2)
return NVARCHAR2
IS
cnuMAX_LENGTH Constant number := 32767 ;
nuLength Number := DBMS_LOB.getlength(iclCLOB);
sbBuffer Nvarchar2(32767);
begin
dbms_lob.read(iclCLOB,nuLength,1,sbBuffer);
return sbBuffer;
END;

when I call it like this:

select GetVarChar2(text) from posts where postid = 'anId';

I get this error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6058, maximum: 2000)
22835. 00000 - "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause: An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
the LOB size was bigger than the buffer limit for CHAR and RAW
types.
Note that widths are reported in characters if character length
semantics are in effect for the column, otherwise widths are
reported in bytes.
*Action: Do one of the following
1. Make the LOB smaller before performing the conversion,
for example, by using SUBSTR on CLOB
2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

The problem is that the size of text in posts table in of type NCLOB and is 6059 bytes.
It's strange because when I do this without calling function it runs well. i.e. when I run the following script:

declare 
    iclCLOB nvarchar2(6100) := 'Here is the same 6059 bytes string';
    cnuMAX_LENGTH number := 32767 ;
    nuLength Number := DBMS_LOB.getlength(iclCLOB);
    sbBuffer Nvarchar2(32767);
    sbBuffer1 Nvarchar2(32767);
begin
    dbms_lob.read(iclCLOB,nuLength,1,sbBuffer);
    select GetVarChar2(text) into sbBuffer1 from posts where postid = 'anId';
end;

It runs without any problems.

Thank you.

分享到QQ

分享到微博