Mega Code Archive
An attempt to assign a negative value to a POSITIVE variable
<![CDATA[
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 age POSITIVE;
3 current_year NATURAL;
4 current_month POSITIVE;
5 current_day POSITIVE;
6 birth_year NATURAL;
7 birth_month POSITIVE;
8 birth_day POSITIVE;
9 birth_date DATE := TO_DATE('11-15-1999','mm-dd-yyyy');
10 current_date DATE;
11 BEGIN
12 current_date := sysdate;
13
14 current_year := TO_NUMBER(TO_CHAR(current_date,'yy'));
15 current_month := TO_NUMBER(TO_CHAR(current_date,'mm'));
16 current_day := TO_NUMBER(TO_CHAR(current_date,'dd'));
17
18 birth_year := TO_NUMBER(TO_CHAR(birth_date,'yy'));
19 birth_month := TO_NUMBER(TO_CHAR(birth_date,'mm'));
20 birth_day := TO_NUMBER(TO_CHAR(birth_date,'dd'));
21
22 IF current_month > birth_month THEN
23 age := current_year - birth_year;
24 ELSIF (current_month = birth_month) and (current_day >= birth_day) THEN
25 age := current_year - birth_year;
26 ELSE
27 age := current_year - birth_year - 1;
28 END IF;
29 END;
30 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 27
SQL>
]]>