REPORT Error

[vamsioracle]

hi




I have a report with a formula column to display the invoice description. For few accounts it is working fine but for few accounts it is displaying an error

what might be the problem, i have tried for a solution in google but it says null value has been assigned to a non null column. but i don't find such thing any where in my code.

Please help me out

here are the details

ERROR:
Spawned Process 12357
MSG-00001: kakrishn 1
REP-1401: 'cf_inv_descfor mula': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error



FORMULA COLUMN:

function CF_INV_DESCForm ula return char is
v_desc varchar2(2000);
description varchar2(5000);
cursor cur1 is
(select reference2,refe rence5
from ap_ae_lines_all
where reference3 =:reference_3
and AE_LINE_TYPE_CO DE ='LIABILITY');
begin
for i in cur1
loop
begin
select description into v_desc
from ap_invoices
where invoice_id = i.reference2;
exception
when no_data_found then
null;
end;
description := description||' '||i.reference5 ||'-'||v_desc||chr( 10);
end loop;

if :C_category = 'Payments' then
return description;
else
return :descr;
end if;

-- return :descr;
end;

[amitpatel66]

hi




I have a report with a formula column to display the invoice description. For few accounts it is working fine but for few accounts it is displaying an error

what might be the problem, i have tried for a solution in google but it says null value has been assigned to a non null column. but i don't find such thing any where in my code.

Please help me out

here are the details

ERROR:
Spawned Process 12357
MSG-00001: kakrishn 1
REP-1401: 'cf_inv_descfor mula': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error



FORMULA COLUMN:

function CF_INV_DESCForm ula return char is
v_desc varchar2(2000);
description varchar2(5000);
cursor cur1 is
(select reference2,refe rence5
from ap_ae_lines_all
where reference3 =:reference_3
and AE_LINE_TYPE_CO DE ='LIABILITY');
begin
for i in cur1
loop
begin
select description into v_desc
from ap_invoices
where invoice_id = i.reference2;
exception
when no_data_found then
null;
end;
description := description||' '||i.reference5 ||'-'||v_desc||chr( 10);
end loop;

if :C_category = 'Payments' then
return description;
else
return :descr;
end if;

-- return :descr;
end;

Vamsi,

PLSQL Numeric value error occurs in one of the following cases:

1. You are trying to assign a VARCHAR value to INTEGER variable or field in the report with datatype as INTEGER
2. If the field is not able to HOLD the value that is returned by the function. Say u have a field of length 100 and the returned value is more than 100 characters then this error will be thrown.

Workaround:

1. Check that the field u r using in the report to display the function returned value is of type char.
2. Check whether the length of the field is larger enough to hold all the values returned by the function

Do POST back your findings.

Amit

[vamsioracle]

hi

thnak you very much


It was size of the variable created in function that created the problem, when i incresed the size, the problem was solved


thank you for your support


bye