Re: character string buffer problem

Did you really want an offset of 1?
I'd have thought you wanted zero.

gdas wrote:
>
> Hi,
>
> I'm running Oracle 8.1.5 on NT. I'm trying to write procedure
> that will take some data and write it out to a text file using
> utl_file. Unfortunately to complicate the matter, the data I
> want to write out is in a column of type CLOB. I understand
> that utl_file can only handle up to 32767 characters, so I'm
> substringing the clob column.
>
> Here's the code:
>
> declare
>
> v_file_handle UTL_FILE.FILE_TYPE;
> big_field varchar2(32767);
>
> begin
>
> select dbms_lob.substr(contents,32767,1) into big_field from
> document where document_id=4063;
>
> v_file_handle := UTL_FILE.FOPEN ('i:\cluster-
> files','test.txt','w',32767);
>
> UTL_FILE.PUT_LINE(v_file_handle, big_field);
>
> UTL_FILE.FCLOSE (v_file_handle);
>
> end;
>
> This results in an error: "character string buffer too small"
>
> The problem does not appear to be in any of the utl_file
> referenced lines, but rather in my select or in my big_field
> variable declaration.
>
> if I change my select as follows:
>
> select dbms_lob.substr(contents,4000,1) into big_field from
> document where document_id=4063;
>
> everything works.
>
> if I change it as follows:
>
> select dbms_lob.substr(contents,4001,1) into big_field from
> document where document_id=4063;
>
> it fails.
>
> So it appears that I am bound in this case by the database
> datatype constraint of varchar2(4000). However, the PL/SQL
> varchar2 datatype supposedly can handle up to 32767.
>
> If I make the variable a clob, it won't work as the call to
> utl_file_put complains about wrong type of arguments.
>
> Does anyone have any ideas on how to work around this?

select dbms_lob.substr(contents,32767,0) into big_field from document where document_id=4063; Received on Wed Jul 12 2000 - 00:00:00 CDT