Apologies... this is related to a previous question answered earlier today, but I did not know how to ask a 'follow up' question...
I've written the code below to generate an email report on users in an instance which have not been logged into in the last 15 days.
I tried to add a variable (V_DAYS) so that the report shows the number of days sine the last login, but I am getting the following error...
Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 34
06502. 00000 - "PL/SQL: numeric or value error%s"
I believe it is some sort of mismatch between the datatype I assign the V_DAYS variable, and the datatype that the TRUNC(SYSDATE) - trunc ( last_login ), but I can't seem to pin it down. It throws the error at the FETCH C1 shown below...
The SQL for the SELECT into the cursor works, and give me the username, las login and number of days since last login.
If I remove the variable and don't try to add this 3rd column, the report executes normally and generates the email as expected.
Any suggestions would be appreciated. Thanks!
DECLARE
v_htmlbody varchar2(32767);
v_username varchar2(50);
v_last_login varchar2(20);
v_days varchar2(10); --this was added as a variable--
CURSOR C1 is select username, last_login,
TRUNC(SYSDATE) - trunc ( last_login ) days
from dba_users
where username in (select username from all_users where oracle_maintained = 'N')
and username NOT IN ('DBAMETRICS')
and last_login <= TRUNC(SYSDATE) - 15
order by last_login ASC;
BEGIN
-- Header and Body
v_htmlbody := '<HTML> <BODY>';
-- Prepare HTML Table Header
v_htmlbody := v_htmlbody || '<table><caption> Users Not logged in in last 15 days</caption>';
v_htmlbody := v_htmlbody || '<table border=''1'' cellpadding=''1''> <font color=''330099''>';
v_htmlbody := v_htmlbody || '<tr>'
|| '<th>' || 'Username'|| '</th>'
|| '<th>' || 'last_login'|| '</th>'
|| '<th>' || 'days'|| '</th>'
|| '</tr>' ;
-- Prepare HTML Table Body
OPEN C1;
LOOP
FETCH C1 into v_username, v_last_login, v_days; --this is where it throws the error mentioned above--
IF C1%FOUND THEN
v_htmlbody := v_htmlbody || '<tr>'
|| '<td align="left">' || v_username || '</td>'
|| '<td align="left">' || v_last_login || '</td>'
|| '<td align="left">' || v_days || '</td>'
|| '</tr>';
ELSE
EXIT WHEN C1%NOTFOUND;
END IF;
END LOOP;
CLOSE C1;
-- End HTML Table
v_htmlbody := v_htmlbody|| '</table>';
-- End Header
v_htmlbody :=v_htmlbody || '</BODY> </HTML>';
-----------------------------------------
-----------------------------------------
-- Finally send the v_htmlbody using mail
REMOVED FOR PRIVACY
END;
/
Is it not the "days", it is the length of the last_login (which is a timestamp)
eg, even with days totally removed you get the error
SQL> DECLARE
2
3 v_htmlbody varchar2(32767);
4 v_username varchar2(50);
5 v_last_login varchar2(20);
6
7 CURSOR C1 is select username, last_login
8 from dba_users
9 where username in (select username from all_users where oracle_maintained = 'N')
10 and username NOT IN ('DBAMETRICS')
11 and last_login <= TRUNC(SYSDATE) - 15
12 order by last_login ASC;
13
14 BEGIN
15
16 -- Header and Body
17 v_htmlbody := '<HTML> <BODY>';
18
19 -- Prepare HTML Table Header
20 v_htmlbody := v_htmlbody || '<table><caption> Users Not logged in in last 15 days</caption>';
21 v_htmlbody := v_htmlbody || '<table border=''1'' cellpadding=''1''> <font color=''330099''>';
22 v_htmlbody := v_htmlbody || '<tr>'
23 || '<th>' || 'Username'|| '</th>'
24 || '<th>' || 'last_login'|| '</th>'
25 || '<th>' || 'days'|| '</th>'
26 || '</tr>' ;
27
28 -- Prepare HTML Table Body
29
30 OPEN C1;
31 LOOP
32 FETCH C1 into v_username, v_last_login;
33 IF C1%FOUND THEN
34 v_htmlbody := v_htmlbody || '<tr>'
35 || '<td align="left">' || v_username || '</td>'
36 || '<td align="left">' || v_last_login || '</td>'
37 || '</tr>';
38 ELSE
39 EXIT WHEN C1%NOTFOUND;
40 END IF;
41 END LOOP;
42 CLOSE C1;
43
44 -- End HTML Table
45 v_htmlbody := v_htmlbody|| '</table>';
46
47 -- End Header
48 v_htmlbody :=v_htmlbody || '</BODY> </HTML>';
49
50 -----------------------------------------
51 -----------------------------------------
52
53
54 -- Finally send the v_htmlbody using mail
55
56
57 END;
58 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 32
But when I make last_login larger...
SQL>
SQL> DECLARE
2
3 v_htmlbody varchar2(32767);
4 v_username varchar2(50);
5 v_last_login varchar2(60);
6
7 CURSOR C1 is select username, last_login
8 from dba_users
9 where username in (select username from all_users where oracle_maintained = 'N')
10 and username NOT IN ('DBAMETRICS')
11 and last_login <= TRUNC(SYSDATE) - 15
12 order by last_login ASC;
13
14 BEGIN
15
16 -- Header and Body
17 v_htmlbody := '<HTML> <BODY>';
18
19 -- Prepare HTML Table Header
20 v_htmlbody := v_htmlbody || '<table><caption> Users Not logged in in last 15 days</caption>';
21 v_htmlbody := v_htmlbody || '<table border=''1'' cellpadding=''1''> <font color=''330099''>';
22 v_htmlbody := v_htmlbody || '<tr>'
23 || '<th>' || 'Username'|| '</th>'
24 || '<th>' || 'last_login'|| '</th>'
25 || '<th>' || 'days'|| '</th>'
26 || '</tr>' ;
27
28 -- Prepare HTML Table Body
29
30 OPEN C1;
31 LOOP
32 FETCH C1 into v_username, v_last_login;
33 IF C1%FOUND THEN
34 v_htmlbody := v_htmlbody || '<tr>'
35 || '<td align="left">' || v_username || '</td>'
36 || '<td align="left">' || v_last_login || '</td>'
37 || '</tr>';
38 ELSE
39 EXIT WHEN C1%NOTFOUND;
40 END IF;
41 END LOOP;
42 CLOSE C1;
43
44 -- End HTML Table
45 v_htmlbody := v_htmlbody|| '</table>';
46
47 -- End Header
48 v_htmlbody :=v_htmlbody || '</BODY> </HTML>';
49
50 -----------------------------------------
51 -----------------------------------------
52
53
54 -- Finally send the v_htmlbody using mail
55
56
57 END;
58 /
PL/SQL procedure successfully completed.
SQL>
SQL>
</code>