SQLERRM = ORA-06502: PL/SQL: numeric or value error: character string buffer too small

5 pts.
Tags:
ORA-06502
PL/SQL
PL/SQL error messages
PL/SQL: numeric or value error
My procedure is shown below. When I execute it, it gives me the title error. Field3 is identified as a CLOB. Any / all help would be greatly appreciated. Running Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 CREATE OR REPLACE PROCEDURE xxx IS FileHandle SYS.UTL_FILE.FILE_TYPE; FileName VARCHAR2(2000); FileText VARCHAR2(2000); vField1 TABLE.field1%TYPE; vField2 TABLE.field2%TYPE; vField3 TABLE.field3%TYPE; CURSOR TableCURSOR IS SELECT * FROM table; BEGIN DBMS_OUTPUT.ENABLE(1000000); FileName := 'Output.txt'; FileHandle := SYS.UTL_FILE.FOPEN('PATH', FileName, 'w'); FOR trec in TableCURSOR LOOP vField1 := trec.field1; vField2 := trec.field2; vField3 := trec.field3; FileText := vField1 || '~' || vField2 || '~' || vField3; SYS.UTL_FILE.PUT_LINE(FileHandle, FileText); END LOOP; SYS.UTL_FILE.FCLOSE(FileHandle); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Error on ' || vField1 || ' ' || vField2 || ' ' || vField3); DBMS_OUTPUT.PUT_LINE ('SQLCODE = ' || to_char(SQLCODE)); DBMS_OUTPUT.PUT_LINE ('SQLERRM = ' || substr(SQLERRM, 1,200)); END; / SHO ERR

Answer Wiki

Thanks. We'll let you know when a new response is added.

CLOB fields stores up to 4 GB of data, but your FileText variable can store just 2000 Bytes.

I think the problem is here:
<pre>
FileText :=
vField1 || ‘~’ ||
vField2 || ‘~’ ||
vField3;</pre>

You would need to increase the data length of FileText to at least the sum of data lengths from field1, field2 and field3 (but a varchar2 won’t be enough).

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Genderhayes
    A statement was executed that resulted in an arithmetic, numeric, string, conversion, or constraint error. This error mainly results from programmer error or invalid data input
    7,100 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following