主要是比较表结构的差异吧-- 比较两个数据库中表的差异 -- u表,p存储过程,v视图 -- INTFSIMSNEW新库,INTFSIMS旧库 SELECT NTABLE = A.NAME, OTABLE = B.NAME FROM INTFSIMSNEW..SYSOBJECTS A LEFT JOIN INTFSIMS..SYSOBJECTS B ON A.NAME = B.NAME WHERE ISNULL(B.NAME, '') = '' AND A.XTYPE = 'U'UNION ALLSELECT NTABLE = B.NAME, OTABLE = A.NAME FROM INTFSIMS..SYSOBJECTS A LEFT JOIN INTFSIMSNEW..SYSOBJECTS B ON A.NAME = B.NAME WHERE ISNULL(B.NAME, '') = '' AND A.XTYPE = 'U' ORDER BY 1, 2-- 比较两个数据库中每个表字段的差异 SELECT 表名A = CASE WHEN ISNULL(A.TABLENAME, '') <> '' THEN A.TABLENAME ELSE B.TABLENAME END, 字段名A = A.FIELDNAME, 字段名B = B.FIELDNAME, 顺序= A.FIELDSNO, 说明= CASE WHEN A.FIELDTYPE <> B.FIELDTYPE THEN '类型: ' + A.FIELDTYPE + '-->' + B.FIELDTYPE WHEN A.FIELDSNO <> B.FIELDSNO THEN '顺序: ' + str(A.FIELDSNO) + '-->' + str(B.FIELDSNO) WHEN A.LENGTH <> B.LENGTH THEN '长度: ' + str(A.LENGTH) + '-->' + str(B.LENGTH) WHEN A.LENSEC <> B.LENSEC THEN '小数位: ' + str(A.LENSEC) + '-->' + str(B.LENSEC) WHEN A.ALLOWNULL <> B.ALLOWNULL THEN '允许空值: ' + str(A.ALLOWNULL) + '-->' + str(B.ALLOWNULL) END FROM (SELECT TABLENAME = B.NAME, FIELDNAME = A.NAME, FIELDSNO = A.COLID, FIELDTYPE = C.NAME, LENGTH = A.LENGTH, LENSEC = A.XSCALE, ALLOWNULL = A.ISNULLABLE FROM INTFSIMSNEW..SYSCOLUMNS A LEFT JOIN INTFSIMSNEW..SYSOBJECTS B ON A.ID = B.ID LEFT JOIN INTFSIMSNEW..SYSTYPES C ON A.XUSERTYPE = C.XUSERTYPE WHERE B.XTYPE = 'U') A FULL JOIN (SELECT TABLENAME = B.NAME, FIELDNAME = A.NAME, FIELDSNO = A.COLID, FIELDTYPE = C.NAME, LENGTH = A.LENGTH, LENSEC = A.XSCALE, ALLOWNULL = A.ISNULLABLE FROM INTFSIMS..SYSCOLUMNS A LEFT JOIN INTFSIMS..SYSOBJECTS B ON A.ID = B.ID LEFT JOIN INTFSIMS..SYSTYPES C ON A.XUSERTYPE = C.XUSERTYPE WHERE B.XTYPE = 'U') B ON A.TABLENAME = B.TABLENAME AND A.FIELDNAME = B.FIELDNAME WHERE ISNULL(A.TABLENAME, '') = '' OR ISNULL(B.TABLENAME, '') = '' OR A.FIELDTYPE <> B.FIELDTYPE OR A.FIELDSNO <> B.FIELDSNO OR A.LENGTH <> B.LENGTH OR A.LENSEC <> B.LENSEC OR A.ALLOWNULL <> B.ALLOWNULL ORDER by 1, 4
这是oracle 官网的正确解释:ORA-06502: PL/SQL: numeric or value errorstring Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
-- u表,p存储过程,v视图
-- INTFSIMSNEW新库,INTFSIMS旧库
SELECT NTABLE = A.NAME, OTABLE = B.NAME
FROM INTFSIMSNEW..SYSOBJECTS A
LEFT JOIN INTFSIMS..SYSOBJECTS B
ON A.NAME = B.NAME
WHERE ISNULL(B.NAME, '') = ''
AND A.XTYPE = 'U'UNION ALLSELECT NTABLE = B.NAME, OTABLE = A.NAME
FROM INTFSIMS..SYSOBJECTS A
LEFT JOIN INTFSIMSNEW..SYSOBJECTS B
ON A.NAME = B.NAME
WHERE ISNULL(B.NAME, '') = ''
AND A.XTYPE = 'U'
ORDER BY 1, 2-- 比较两个数据库中每个表字段的差异
SELECT
表名A = CASE WHEN ISNULL(A.TABLENAME, '') <> '' THEN A.TABLENAME ELSE B.TABLENAME END,
字段名A = A.FIELDNAME,
字段名B = B.FIELDNAME,
顺序= A.FIELDSNO,
说明= CASE WHEN A.FIELDTYPE <> B.FIELDTYPE THEN '类型: ' + A.FIELDTYPE + '-->' + B.FIELDTYPE
WHEN A.FIELDSNO <> B.FIELDSNO THEN '顺序: ' + str(A.FIELDSNO) + '-->' + str(B.FIELDSNO)
WHEN A.LENGTH <> B.LENGTH THEN '长度: ' + str(A.LENGTH) + '-->' + str(B.LENGTH)
WHEN A.LENSEC <> B.LENSEC THEN '小数位: ' + str(A.LENSEC) + '-->' + str(B.LENSEC)
WHEN A.ALLOWNULL <> B.ALLOWNULL THEN '允许空值: ' + str(A.ALLOWNULL) + '-->' + str(B.ALLOWNULL)
END
FROM (SELECT
TABLENAME = B.NAME,
FIELDNAME = A.NAME,
FIELDSNO = A.COLID,
FIELDTYPE = C.NAME,
LENGTH = A.LENGTH,
LENSEC = A.XSCALE,
ALLOWNULL = A.ISNULLABLE
FROM INTFSIMSNEW..SYSCOLUMNS A
LEFT JOIN INTFSIMSNEW..SYSOBJECTS B
ON A.ID = B.ID
LEFT JOIN INTFSIMSNEW..SYSTYPES C
ON A.XUSERTYPE = C.XUSERTYPE
WHERE B.XTYPE = 'U') A
FULL JOIN (SELECT
TABLENAME = B.NAME,
FIELDNAME = A.NAME,
FIELDSNO = A.COLID,
FIELDTYPE = C.NAME,
LENGTH = A.LENGTH,
LENSEC = A.XSCALE,
ALLOWNULL = A.ISNULLABLE
FROM INTFSIMS..SYSCOLUMNS A
LEFT JOIN INTFSIMS..SYSOBJECTS B
ON A.ID = B.ID
LEFT JOIN INTFSIMS..SYSTYPES C
ON A.XUSERTYPE = C.XUSERTYPE
WHERE B.XTYPE = 'U') B
ON A.TABLENAME = B.TABLENAME
AND A.FIELDNAME = B.FIELDNAME
WHERE ISNULL(A.TABLENAME, '') = ''
OR ISNULL(B.TABLENAME, '') = ''
OR A.FIELDTYPE <> B.FIELDTYPE
OR A.FIELDSNO <> B.FIELDSNO
OR A.LENGTH <> B.LENGTH
OR A.LENSEC <> B.LENSEC
OR A.ALLOWNULL <> B.ALLOWNULL
ORDER by 1, 4
因为一个比较奇怪的错误。所以才来看看是否是数据库上有差异造成的。
WEB窗体的代码一个样,一个测试用数据环境,数据量较小。另外一个数据量多。
在执行WEB窗体时,数据量小没有报错。数据量大的那个有报一个错误。
所以怀疑是否是表的结构,或者是存储过程什么的是否有差异。
如果不是那就数据量方面的问题了。
错误提示:ORA-06502 ORA-06512。
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
在Packages中错误定位到的位置比较奇怪。
我们之前是定义
counter number;
counter := 1;
在后续的一个循环中有一句:counter := counter + 1;
他报错的位置就是counter := counter + 1;
number类型应该没有那么快爆上限吧。
测试用的环境,最多的循环大概去到2400左右。
在另外一个环境的循环可以去到多少目前不清楚。
你意思是循环的次数超了32767就会超出范围?number类型的值应该没有那么小吧?
郁闷了,测试机和运行环境上的数据库中Packages中的行数有差异。
已经大概确认到是另外一个定义的变量 VARCHAR2类型超上限了。
感谢楼上各位的帮忙解答,谢谢。另外能否帮看看这个怎么搞。http://bbs.csdn.net/topics/390349738