问题：
ORA-06502:PL/SQL:oracle存储过程中出现数字或值错误
汪欣德
这里是我的过程中，我过滤4个参数的数据库：
开始日期、结束日期、小时和分钟。
当我过滤数据之间2017-11-01到2017-11-30（与一个月）它的工作罚款，但当我过滤数据大于1个月，如2017-11-01到2017-12-10然后它给我的错误如下。
程序
create or replace PROCEDURE RentedTotalCars (
startDate IN VARCHAR2,
endDate IN VARCHAR2,
control_time_hour IN VARCHAR2,
control_time_min IN VARCHAR2,
p_refcur OUT SYS_REFCURSOR
)
IS
dateDiffernce INT;
v_no INT;
l_query clob;
startDateInTimestamp TIMESTAMP;
endDateInTimestamp TIMESTAMP;
startDateInDate DATE;
endDateInDate DATE;
startDateText VARCHAR2(1200);
endDateText VARCHAR2(1200);
addedDate DATE;
addedTimestamp TIMESTAMP;
BEGIN
v_no := 0;
startDateText := startDate || ' ' || control_time_hour || ':' || control_time_min || ':00';
endDateText := endDate || ' ' || control_time_hour || ':' || control_time_min || ':00';
startDateInTimestamp := TO_TIMESTAMP(startDateText, 'yyyy-mm-dd hh24:mi:ss');
endDateInTimestamp := TO_TIMESTAMP(endDateText, 'yyyy-mm-dd hh24:mi:ss');
startDateInDate := TO_DATE(startDate, 'YYYY-MM-DD');
endDateInDate := TO_DATE(endDate, 'YYYY-MM-DD');
dateDiffernce := endDateInDate - startDateInDate+1;
l_query:= 'Select date1, codice, CODICE_NAZIONALE, sum(RentedCars)as RentedCars, sum(TotalCars) as TotalCars from (';
while dateDiffernce > 0
loop
addedDate := startDateInDate+v_no;
addedTimestamp := startDateInTimestamp+v_no;
l_query:= l_query || ' (Select q2.date1, q2.CODICE, q2.CODICE_NAZIONALE, count(q2.id) as RentedCars, 0 as TotalCars from';
l_query:= l_query || ' (Select ''' || addedDate || ''' as date1, m.ID_VEICOLO as id, m.ID_SEDE_USCITA, pv.ID_GRUPPO,s.CODICE, g.CODICE_NAZIONALE from';
l_query:= l_query || ' (SELECT id_veicolo, ID_SEDE_USCITA from movimenti_auto where';
l_query:= l_query || ' inizio <= ''' || addedTimestamp || ''' and';
l_query:= l_query || ' fine >= ''' || addedTimestamp || ''' ) m';
l_query:= l_query || ' left join parco_veicoli pv on m.id_veicolo = pv.id';
l_query:= l_query || ' left join gruppi g on pv.ID_GRUPPO = g.id';
l_query:= l_query || ' left join sedi s on m.ID_SEDE_USCITA = s.id';
l_query:= l_query || ' where pv.IMPEGNATO = 1 ) q2';
l_query:= l_query || ' group by CODICE_NAZIONALE, CODICE, date1)';
l_query:= l_query || ' union all ';
l_query:= l_query || ' (Select ''' || addedDate || ''' as date1, s.CODICE, g.CODICE_NAZIONALE, 0 as RentedCars, count(pv.id) TotalCars from Parco_veicoli pv';
l_query:= l_query || ' left join gruppi g on pv.id_gruppo = g.id';
l_query:= l_query || ' left join sedi s on pv.id_sede = s.id where';
l_query:= l_query || ' (data_acq < ''' || addedDate || ''') and';
l_query:= l_query || ' ((data_scadenza_contratto > ''' || addedDate || ''' or data_proroga_1 > ''' || addedDate || '''';
l_query:= l_query || ' or data_proroga_2 > ''' || addedDate || ''')';
l_query:= l_query || ' or data_vend > ''' || addedDate || ''')';
l_query:= l_query || ' group by s.CODICE, g.CODICE_NAZIONALE )';
if dateDiffernce > 1
then
l_query:= l_query || ' union all ';
end if;
v_no := v_no + 1;
dateDiffernce := dateDiffernce-1;
end loop;
l_query:= l_query || ' ) q1';
l_query:= l_query || ' group by Codice, Codice_nazionale, date1';
l_query:= l_query || ' order by date1, Codice, Codice_nazionale';
OPEN p_refcur FOR l_query;
END;
错误
ORA-06502:PL/SQL:数字或值错误
ORA-06512：在"MYRENT. RENTEDTOTALCARS"，第58行
ORA-06512：在第13行
我在程序中的58号线是：
l_query：=l_query || ' (data_acq
共有1个答案
缪英锐
这就是我的意思：我注释了OUT参数和OPEN语句，并包含了DBMS_输出。
SQL> create or replace PROCEDURE RentedTotalCars (
2 startDate IN VARCHAR2,
3 endDate IN VARCHAR2,
4 control_time_hour IN VARCHAR2,
5 control_time_min IN VARCHAR2
6 --, p_refcur OUT SYS_REFCURSOR
7 )
8 IS
9 dateDiffernce INT;
10 v_no INT;
11 l_query varchar2(10000);
12 startDateInTimestamp TIMESTAMP;
13 endDateInTimestamp TIMESTAMP;
14 startDateInDate DATE;
15 endDateInDate DATE;
16 startDateText VARCHAR2(1200);
17 endDateText VARCHAR2(1200);
18 addedDate DATE;
19 addedTimestamp TIMESTAMP;
20 BEGIN
21 v_no := 0;
22 startDateText := startDate || ' ' || control_time_hour || ':' || control_time_min || ':00';
23 endDateText := endDate || ' ' || control_time_hour || ':' || control_time_min || ':00';
24 startDateInTimestamp := TO_TIMESTAMP(startDateText, 'yyyy-mm-dd hh24:mi:ss');
25 endDateInTimestamp := TO_TIMESTAMP(endDateText, 'yyyy-mm-dd hh24:mi:ss');
26 startDateInDate := TO_DATE(startDate, 'YYYY-MM-DD');
27 endDateInDate := TO_DATE(endDate, 'YYYY-MM-DD');
28
29 dateDiffernce := endDateInDate - startDateInDate+1;
30 l_query:= 'Select date1, codice, CODICE_NAZIONALE, sum(RentedCars)as RentedCars, sum(TotalCars) as TotalCars from (';
31 while dateDiffernce > 0
32 loop
33 addedDate := startDateInDate+v_no;
34 addedTimestamp := startDateInTimestamp+v_no;
35 l_query:= l_query || ' (Select q2.date1, q2.CODICE, q2.CODICE_NAZIONALE, count(q2.id) as RentedCars, 0 as TotalCars from';
36 l_query:= l_query || ' (Select ''' || addedDate || ''' as date1, m.ID_VEICOLO as id, m.ID_SEDE_USCITA, pv.ID_GRUPPO,s.CODICE, g.CODICE_NAZIONALE from';
37 l_query:= l_query || ' (SELECT id_veicolo, ID_SEDE_USCITA from movimenti_auto where';
38 l_query:= l_query || ' inizio <= ''' || addedTimestamp || ''' and';
39 l_query:= l_query || ' fine >= ''' || addedTimestamp || ''' ) m';
40 l_query:= l_query || ' left join parco_veicoli pv on m.id_veicolo = pv.id';
41 l_query:= l_query || ' left join gruppi g on pv.ID_GRUPPO = g.id';
42 l_query:= l_query || ' left join sedi s on m.ID_SEDE_USCITA = s.id';
43 l_query:= l_query || ' where pv.IMPEGNATO = 1 ) q2';
44 l_query:= l_query || ' group by CODICE_NAZIONALE, CODICE, date1)';
45 l_query:= l_query || ' union all ';
46 l_query:= l_query || ' (Select ''' || addedDate || ''' as date1, s.CODICE, g.CODICE_NAZIONALE, 0 as RentedCars, count(pv.id) TotalCars from Parco_veicoli pv';
47 l_query:= l_query || ' left join gruppi g on pv.id_gruppo = g.id';
48 l_query:= l_query || ' left join sedi s on pv.id_sede = s.id where';
49 l_query:= l_query || ' (data_acq < ''' || addedDate || ''') and';
50 l_query:= l_query || ' ((data_scadenza_contratto > ''' || addedDate || ''' or data_proroga_1 > ''' || addedDate || '''';
51 l_query:= l_query || ' or data_proroga_2 > ''' || addedDate || ''')';
52 l_query:= l_query || ' or data_vend > ''' || addedDate || ''')';
53 l_query:= l_query || ' group by s.CODICE, g.CODICE_NAZIONALE )';
54 if dateDiffernce > 1
55 then
56 l_query:= l_query || ' union all ';
57 end if;
58
59 v_no := v_no + 1;
60 dateDiffernce := dateDiffernce-1;
61
62 end loop;
63
64 l_query:= l_query || ' ) q1';
65 l_query:= l_query || ' group by Codice, Codice_nazionale, date1';
66 l_query:= l_query || ' order by date1, Codice, Codice_nazionale';
67
68 -- OPEN p_refcur FOR l_query;
69 dbms_output.put_line(l_query);
70 END;
71 /
Procedure created.
SQL>
SQL> exec rentedtotalcars('2018-02-15', '2018-02-15', '15', '20');
PL/SQL procedure successfully completed.
SQL>
如您所见，该过程已成功创建和执行。DBMS_输出的结果（在我的数据库中）如下所示：
SELECT date1,
codice,
CODICE_NAZIONALE,
SUM (RentedCars) AS RentedCars,
SUM (TotalCars) AS TotalCars
FROM ( ( SELECT q2.date1,
q2.CODICE,
q2.CODICE_NAZIONALE,
COUNT (q2.id) AS RentedCars,
0 AS TotalCars
FROM (SELECT '15.02.18' AS date1,
m.ID_VEICOLO AS id,
m.ID_SEDE_USCITA,
pv.ID_GRUPPO,
s.CODICE,
g.CODICE_NAZIONALE
FROM (SELECT id_veicolo, ID_SEDE_USCITA
FROM movimenti_auto
WHERE inizio <= '15.02.18 15:20:00,000000'
AND fine >= '15.02.18 15:20:00,000000') m
LEFT JOIN parco_veicoli pv ON m.id_veicolo = pv.id
LEFT JOIN gruppi g ON pv.ID_GRUPPO = g.id
LEFT JOIN sedi s ON m.ID_SEDE_USCITA = s.id
WHERE pv.IMPEGNATO = 1) q2
GROUP BY CODICE_NAZIONALE, CODICE, date1)
UNION ALL
( SELECT '15.02.18' AS date1,
s.CODICE,
g.CODICE_NAZIONALE,
0 AS RentedCars,
COUNT (pv.id) TotalCars
FROM Parco_veicoli pv
LEFT JOIN gruppi g ON pv.id_gruppo = g.id
LEFT JOIN sedi s ON pv.id_sede = s.id
WHERE (data_acq < '15.02.18')
AND ( ( data_scadenza_contratto > '15.02.18'
OR data_proroga_1 > '15.02.18'
OR data_proroga_2 > '15.02.18')
OR data_vend > '15.02.18')
GROUP BY s.CODICE, g.CODICE_NAZIONALE)) q1
GROUP BY Codice, Codice_nazionale, date1
ORDER BY date1, Codice, Codice_nazionale
我不能运行它，因为我没有你的桌子，但你可以尝试这样做。注日期格式-为DD.MM。YY。也许您需要将其转换为有效的日期格式-这取决于您的NLS设置。最安全的方法是显式地将其设置为日期文字（始终是DATE'YYYY-MM-DD'），或者使用适当的格式掩码设置日期。
类似资料：
-
我试图插入数据到Oracle数据库从ASP. NET MVC应用程序。我使用存储过程这样做，但我得到这个错误： ORA-06502:PL/SQL:数值或值错误：字符串缓冲区太小ORA-06512:在“C#AET.KATEGORIJA_PKG”第40行ORA-06502:PL/SQL:数值或值错误：字符串缓冲区太小ORA-06512:在第1行 程序代码为： 以及调用它的方法： 与数据库通信的方法有：
-
我正在尝试编写一个调用oracle存储过程的web服务。该过程有两个输入-一个字符串（公司代码）和一个int（客户代码）。它有2个输出参数；一个数字（pout_addr_code）和一个varchar2（pout_descr）。当我从sqldeveloper运行这个过程时，一切正常，两个值都按预期返回。当我从C#web服务调用该过程时，我在执行时（而不是在编译期间）得到错误“Oracle.Data
-
我有一小段代码，抓取一些数据并生成SQL语句，将它们插入Oracle数据库。然后通过Oracle服务器上的JDBC驱动程序执行这些操作。 我遇到的问题是，如果这些语句包含一个TO_BINARY_DOUBLE调用，它们总是对我失败，而不是对我团队中的任何人失败，他们应该和我有完全相同的驱动程序和环境，这是非常奇怪的。 错误是： 希望有人能给我提供一些线索，或者给我指出正确的方向，试着找到它。
-
正如您所知，由于Oracle11g不支持json，我使用CLOB创建json数据，因为varchar2(32767)大小，但此时我得到了ORA-06502:PL/SQL:numeric或value错误。同时，数据大小为68075。为什么我得到错误，尽管Clob是支持4 gb的数据？
-
我实现了一个返回clob数据类型的函数，我希望在DBMS输出中打印结果。不幸的是，我得到了ORA-06502:PL/SQL:numeric或value错误，我认为这是由于dbms_output的大小造成的。 这是密码。 这里有一些东西可以帮助您理解这个问题 1)添加了以下内容来设置缓冲区的大小不受限制，但不起作用… 3）我可以通过执行以下操作来解决这个问题，但我认为这不是一个好的解决方案，因为它执
-
我在执行触发器时遇到以下错误： 请说明问题的原因。 编辑 数据类型：