第191行的错误:ORA-01489:字符串连接的结果太长 [英] ERROR at line 191: ORA-01489: result of string concatenation is too long

问题描述

Select TO_CLOB(a)|| TO_CLOB(b)|| TO_CLOB(c) || TO_CLOB(d) 
  from table1

以上查询未将数据正确地后台处理到文本文件中.

Above query is not spooling the data properly into text file.

而

Select a||b||c||d 
  from table1.

以

第191行出现错误:ORA-01489:字符串连接的结果太长.

ERROR at line 191: ORA-01489: result of string concatenation is too long.

请帮助!!!

推荐答案

VARCHAR2限制为4000个字节.如果出现此错误

VARCHAR2 are limited to 4000 bytes. If you get this error

在第191行出现错误:ORA-01489:字符串连接的结果也太 长.

ERROR at line 191: ORA-01489: result of string concatenation is too long.

然后很明显，串联超过4000个字节.

Then it is pretty clear that the concatenation exceed 4000 bytes.

现在该怎么办?

您第一个使用CLOB的解决方案是正确的.

Your first solution to use CLOB instead is correct.

select TO_CLOB(a)|| TO_CLOB(b)|| TO_CLOB(c) || TO_CLOB(d) 

您的真正问题似乎是保存到文件中

It seems like your real problem is saving to file

以上查询未将数据正确地后台处理到文本文件中.

Above query is not spooling the data properly into text file.

虽然您没有发布如何将生成的Clob保存到文件中，但我认为您没有正确执行此操作.如果您尝试使用与VARCHAR2相同的方式保存文件，则说明您做错了.

While you did not post how to save the resulting clob to a file, I believe you are not doing it correctly. If you try to save to file the same way as you were doing it with VARCHAR2, you are doing it wrong.

首先需要使用dbms_lob.read从数据库中读取Clob，然后使用utl_file.put_raw写入文件.

You need to first use dbms_lob.read to read the clob from database, then use utl_file.put_raw to write to file.

DECLARE
    position NUMBER := 1;
    byte_length NUMBER := 32760;
    length NUMBER;
    vblob BLOB;
    rawlob RAW(32760);
    temp NUMBER;
    output utl_file.file_type;
BEGIN
    -- Last parameter is maximum number of bytes returned.
    -- wb stands for write byte mode
    output := utl_file.fopen('DIR', 'filename', 'wb', 32760);

    position := 1;
    select dbms_lob.getlength(yourLob)
    into len
    from somewhere
    where something;

    temp := length;

    select yourLob
    into vlob
    from somewhere
    where something;

    IF len < 32760 THEN
        utl_file.put_raw(output, vblob);
        -- Don't forget to flush
        utl_file.fflush(output);
    ELSE -- write part by part
        WHILE position < len AND byte_length > 0
        LOOP
           dbms_lob.read(vblob, byte_length, position, rawlob);

           utl_file.put_raw(output,rawlob);

           -- You must admit, you would have forgot to flush.
           utl_file.fflush(output); 

           position := position + byte_length;

           -- set the end position if less than 32000 bytes
           temp := temp - bytelen;
           IF temp < 32760 THEN
               byte_length := temp;
           END IF;
    END IF;
END;

这篇关于第191行的错误:ORA-01489:字符串连接的结果太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！