kravindra
asked on
ora-06502 pl sql numeric or value error character string buffer too small
I am getting above exception while excuting this function. i didn't no what is wrong in it.
CREATE OR REPLACE function MPP.FN_GET_DIVISION_BY_ID(
return varchar2
is
v_division varchar2(100);
begin
select NAME || ' ' || EXTENDED_NAME into v_division from ORGANIZATIONAL_UNIT where DIVISION_ID = p_division_id;
return v_division;
end;
/
My Expected output is "Housing Finance and Operations Administrative Support".
CREATE OR REPLACE function MPP.FN_GET_DIVISION_BY_ID(
return varchar2
is
v_division varchar2(100);
begin
select NAME || ' ' || EXTENDED_NAME into v_division from ORGANIZATIONAL_UNIT where DIVISION_ID = p_division_id;
return v_division;
end;
/
My Expected output is "Housing Finance and Operations Administrative Support".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What are the datatypes for NAME and EXTENDED_NAME? If they are fixed lengths, you may want to trim them before concatenating and inserting into your variable.
try this : basically it is the same what all above updates are being said.
just taking only 100 chars if the length is more than 100 to avoid the error. Check the string returned and if it is not complete, then increase the variable length or if it has spaces use trim to get rid of them.
CREATE OR REPLACE function MPP.FN_GET_DIVISION_BY_ID(
return varchar2
is
v_division varchar2(100);
begin
select substr(NAME || ' ' || EXTENDED_NAME,1,100) into v_division from ORGANIZATIONAL_UNIT where DIVISION_ID = p_division_id;
return v_division;
end;
/
just taking only 100 chars if the length is more than 100 to avoid the error. Check the string returned and if it is not complete, then increase the variable length or if it has spaces use trim to get rid of them.
CREATE OR REPLACE function MPP.FN_GET_DIVISION_BY_ID(
return varchar2
is
v_division varchar2(100);
begin
select substr(NAME || ' ' || EXTENDED_NAME,1,100) into v_division from ORGANIZATIONAL_UNIT where DIVISION_ID = p_division_id;
return v_division;
end;
/
ASKER
help ful
First, I would confirm that this is your expected output. If it is, does increasing your v_division to varchar2(200) make the function work, with the same expected output? If so, then this may be a multi-byte language issue.