暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle ORA-06512 - PL/SQL: 数值或值错误 % s

Oracle ORA-06512 - PL/SQL: 数值或值错误 % s

ASKTOM 2020-05-29
2738

问题描述

抱歉... 这与今天早些时候回答的上一个问题有关，但我不知道如何提出 “跟进” 问题...


我已经编写了下面的代码，以生成有关最近15天内未登录的实例中的用户的电子邮件报告。

我试图添加一个变量 (V_DAYS)，以便报告显示上次登录的天数，但是我收到以下错误...

错误报告:
ORA-06502: PL/SQL: 数值或值错误
ORA-06512: 在第34行
06502. 00000 - "PL/SQL: 数字或值错误 % s"

我相信这是我分配V_DAYS变量的数据类型和TRUNC(SYSDATE)-trunc (last_login) 的数据类型之间的某种不匹配，但我似乎无法将其固定下来。它在如下所示的FETCH C1处抛出错误...

选择到光标中的SQL工作，并给我用户名，las登录和自上次登录以来的天数。

如果我删除变量并且不尝试添加此第3列，则报告将正常执行并按预期生成电子邮件。

任何建议将不胜感激。谢谢!



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 := ' ';

-- Prepare HTML Table Header
v_htmlbody := v_htmlbody || '';
v_htmlbody := v_htmlbody || '
 Users Not logged in in last 15 days
';
v_htmlbody := v_htmlbody || '' 
     || ''
     || ''
     || ''
     || '' ;

-- 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  || '' 
       || '' 
       || ''
       || ''
       || '';   
  ELSE
    EXIT WHEN C1%NOTFOUND;
  END IF;
END LOOP;
CLOSE C1;

-- End HTML Table 
v_htmlbody := v_htmlbody|| '
 
' || 'Username'|| '' || 'last_login'|| '' || 'days'|| '
'  || v_username      || ''  || v_last_login     || ''  || v_days     || '
'; 

 -- End Header
v_htmlbody :=v_htmlbody ||  ' ';

-----------------------------------------
-----------------------------------------


-- Finally send the v_htmlbody using mail

REMOVED FOR PRIVACY

END;
/


专家解答

不是 “天”，而是last_login的长度 (这是时间戳)

例如，即使几天被完全删除，你也会得到错误

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 := ' ';
 18
 19  -- Prepare HTML Table Header
 20  v_htmlbody := v_htmlbody || '';
 21  v_htmlbody := v_htmlbody || '
 Users Not logged in in last 15 days
';
 22  v_htmlbody := v_htmlbody || ''
 23       || ''
 24       || ''
 25       || ''
 26       || '' ;
 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  || ''
 35         || ''
 36         || ''
 37         || '';
 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|| '
 
' || 'Username'|| '' || 'last_login'|| '' || 'days'|| '
'  || v_username      || ''  || v_last_login     || '
';
 46
 47   -- End Header
 48  v_htmlbody :=v_htmlbody ||  ' ';
 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


但是当我使last_login变大时...


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 := ' ';
 18
 19  -- Prepare HTML Table Header
 20  v_htmlbody := v_htmlbody || '';
 21  v_htmlbody := v_htmlbody || '
 Users Not logged in in last 15 days
';
 22  v_htmlbody := v_htmlbody || ''
 23       || ''
 24       || ''
 25       || ''
 26       || '' ;
 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  || ''
 35         || ''
 36         || ''
 37         || '';
 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|| '
 
' || 'Username'|| '' || 'last_login'|| '' || 'days'|| '
'  || v_username      || ''  || v_last_login     || '
';
 46
 47   -- End Header
 48  v_htmlbody :=v_htmlbody ||  ' ';
 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>















oracleasktom
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论