ORA-06502: Character string buffer too small

Tags:
Oracle
SQL Scripting
SQL Server
String manipulation
Hi guys,

Here's my script I have created aggregate string function

'Tab_To_String' using this below script CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000); CREATE OR REPLACE FUNCTION Tab_To_String (p_varchar2_tab IN t_varchar2_tab, p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS l_string VARCHAR2(32767); BEGIN FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP IF i != p_varchar2_tab.FIRST THEN l_string := l_string || p_delimiter; END IF; l_string := l_string || p_varchar2_tab(i); END LOOP; RETURN l_string; END Tab_To_String; i have one TABLE mst_attribute The mst_attribute TABLE having two column attribute_code varchar2 NOT NULL attribute_value varchar2 NOT NULL both are PRIMARY KEY WHEN i execute the following script i get "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error SELECT attribute_code, Tab_To_String(CAST(COLLECT(attribute_value) AS t_varchar2_tab)) possible_values FROM mst_attribute GROUP BY attribute_code 


Is any one having solution for this error? How to resolve this?
ASKED: July 30, 2010  8:50 AM
UPDATED: April 19, 2013  5:14 PM

Answer Wiki

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

A function with return type VARCHAR2 will produce this error when used in a query if it returns more than 4000 bytes, because that is the max size of a database’s varchar2 type (as opposed to the pl/sql varchar2 type which can hold up to 32737 bytes).

You might probably avoid that error by changing your function’s return type to CLOB.

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
  • Kruti123456789shah
    thank you Carlosdl It's help me very much
    15 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