PL/SQL 游标 出错 ORA-06502: PL/SQL: numeric or value error
日期：2014-05-17 浏览次数：21009 次
PL/SQL 游标 报错 ORA-06502: PL/SQL: numeric or value error
每天需要跑一个存过，备份一个表。但是经常跑失败，报错
报错信息为：ORA-06502: PL/SQL: numeric or value error。
在测试环境调试的时候打开游标也老是报这个错。
其中表中的一个字段是long字段，里面存的是一大串XML报文。
结构为以下。
c_send_packet LONG
=========里面存的内容为=========
<?xml version="1.0" encoding="GBK"?>
<PACKET type="RESPONSE">
<HEAD>
。。。。。。。。。。。。。。。。
</HEAD>
<BODY>
。。。。。。。。
</packet>
=========存过的内容为=============
for p in (select *
from t_table
where t_tm < add_months(sysdate,-1))loop
insert into table (content)values(p.content);
commit;
end loop;
------解决方案--------------------
没看你内容，表字段不要用long字段，用clob有什么不好呢
long只是用来后兼容的，问题一大堆
ref：
The use of LONG values is subject to these restrictions:
A table can contain only one LONG column.
You cannot create an object type with a LONG attribute.
LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
LONG columns cannot be indexed.
LONG data cannot be specified in regular expressions.
A stored function cannot return a LONG value.
You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
In addition, LONG columns cannot appear in these parts of SQL statements:
GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
The UNIQUE operator of a SELECT statement
The column list of a CREATE CLUSTER statement
The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
SQL built-in function
每天需要跑一个存过，备份一个表。但是经常跑失败，报错
报错信息为：ORA-06502: PL/SQL: numeric or value error。
在测试环境调试的时候打开游标也老是报这个错。
其中表中的一个字段是long字段，里面存的是一大串XML报文。
结构为以下。
c_send_packet LONG
=========里面存的内容为=========
<?xml version="1.0" encoding="GBK"?>
<PACKET type="RESPONSE">
<HEAD>
。。。。。。。。。。。。。。。。
</HEAD>
<BODY>
。。。。。。。。
</packet>
=========存过的内容为=============
for p in (select *
from t_table
where t_tm < add_months(sysdate,-1))loop
insert into table (content)values(p.content);
commit;
end loop;
------解决方案--------------------
没看你内容，表字段不要用long字段，用clob有什么不好呢
long只是用来后兼容的，问题一大堆
ref：
The use of LONG values is subject to these restrictions:
A table can contain only one LONG column.
You cannot create an object type with a LONG attribute.
LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
LONG columns cannot be indexed.
LONG data cannot be specified in regular expressions.
A stored function cannot return a LONG value.
You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
In addition, LONG columns cannot appear in these parts of SQL statements:
GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
The UNIQUE operator of a SELECT statement
The column list of a CREATE CLUSTER statement
The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
SQL built-in function
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
