Skip to Main Content
  • Questions
  • ORA-06512 - PL/SQL: numeric or value error%s

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, mark.

Asked: May 29, 2020 - 7:50 pm UTC

Answered by: Connor McDonald - Last updated: May 30, 2020 - 8:06 am UTC

Category: SQL - Version: 12.1

Viewed 100+ times

You Asked

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;
/


and we said...

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>

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.