Error:

[ejeffcott]

I receive the following error after attempting to run the program unit below in a report.

REP-1401: 'cf_1' : Fatal PL/SQL error occurred
ORA-06502: PL/SQL: numeric or value error

FUNCTION CF_1 RETURN CHARACTER IS
   v_age     NUMBER;
   v_return  CHARACTER;
BEGIN
   v_age := TRUNC(SYSDATE) - :sol;
   IF v_age < 31 THEN
      v_return := 'less than 30';
   ELSIF v_age BETWEEN 31 AND 60 THEN
      v_return := '60 days out';
   ELSIF v_age BETWEEN 61 AND 90 THEN
      v_return := '90 days out';
   END IF;
   RETURN (v_return);
END;

 

[BJCooperIT]

Is :SOL a date?

Make sure your field that receives the returned character string is at least VARCHAR2(12).


[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[ejeffcott]

Yes, it is a date.

 

[ejeffcott]

I have modified the code so it will return at least 200 characters.

However, I now receive the following error:

REP-2103: Column 'CF_2': PL/SQL formula returned invalid value or no value

I'm at a loss...

function CF_2 return VARCHAR is
v_age NUMBER;
v_return VARCHAR2(200);

begin
v_age := :sol - TRUNC(SYSDATE);
IF v_age < 0 THEN 
	v_return := 'Statute has run';
ELSIF v_age BETWEEN 1 AND 30 THEN
	v_return := '1 - 30 days remaining';
ELSIF v_age BETWEEN 31 AND 60 THEN
	v_return := '31 - 60 days remaining';
ELSIF v_age BETWEEN 61 AND 90 THEN
	v_return := '61 - 90 days remaining';
ELSIF v_age > 91 THEN
	v_return := '91+ days remaining';


END IF;
RETURN(v_return);
end;

 

[nagornyi]

Make sure the report field sourced with this formula is wide enough to accept the output. Also make header like
function CF_2 return CHAR is

 

[ejeffcott]

This worked!

Thank you very much!