ORA-06502 character string buffer too small 2

[Andrzejek]

I have this piece of code (I hope I copied enough of it):

  f_in      utl_file.file_type;
  s_in      varchar2(10000);[blue]
  string    varchar2([red]32000[/red]);[/blue]
  x         XMLType;
BEGIN[green]
    -- Read XML File line by line[/green]
    f_in := UTL_FILE.FOPEN ('e:\data', xmlFileName, 'r');
    loop
        begin
        utl_file.get_line(f_in,s_in);[blue]
        string:= string || s_in;[/blue]
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
        EXIT;
        end;
    end loop;[green]
    -- dump it into XMLType[/green]
[blue]    x := XMLType(string);[/blue]
    utl_file.fclose(f_in);[green]
    --Clear this string for later use[/green]
    string := ' ';

The variable [tt]string[/tt] (I know it is not the best name for it, but I pieced it from a few samples of code from the Web) is declared as VARCHAR2(32000) and it works OK for most of the (small) xml files I get.

But if the xml file I need to process exceeds 32000 characters, I get the error: "ORA-06502 character string buffer too small"(I believe/guess the error refers to [tt]string[/tt] variable)

Any idea of how to fix it?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[LKBrwnDBA]

Use CLOB instead of VARCHAR2(32000).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

I did see this solution in the search I did, I just was not sure if it would work in my case. But works like a charm

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.