oracle 隐式转换 bug,隐式转换bug

执行下段SQL，结果报错，ORA-01722无效数字SELECT *

FROM (SELECT ROWNUM R, H.*

FROM (SELECT T.ESEQID, T.ATTACHNAME, T.INFOCODE

FROM NEWSADMIN.ANN_BASINFO T

WHERE T.ATTACHTYPE = 0

AND T.NOTICEDATE >= TO_DATE('2013-04-09', 'YYYY-MM-DD')

AND T.ESEQID > 4975226049

AND t.sourcename <> '发行人网站'

ORDER BY T.ESEQID ASC) H ) W

WHERE W.R between 1 and 50

我就很郁闷，调整成w.r > 50就可以了，一直在查rownum的原因，最后发现是隐式转换问题

T.ATTACHTYPE 字段是字符类型，执行计划中进行了to_Number()操作，所以这个字段存储了不可to_number的值，导致报错。至于为什么w.r >50 不报错，是因为plsql取了缓存又或者没取到那个出错的值。正确的如下SELECT *

FROM (SELECT ROWNUM R, H.*

FROM (SELECT T.ESEQID, T.ATTACHNAME, T.INFOCODE

FROM NEWSADMIN.ANN_BASINFO T

WHERE T.ATTACHTYPE = '0'

AND T.NOTICEDATE >= TO_DATE('2013-04-09', 'YYYY-MM-DD')

AND T.ESEQID > 4975226049

AND t.sourcename <> '发行人网站'

ORDER BY T.ESEQID ASC) H ) W

WHERE W.R between 1 and 50

此外，如果不使用order by排序应该考虑将rownum between 1 and 50 写在里面变成如下这样，效率很高SELECT *

FROM (SELECT ROWNUM R, H.*

FROM (SELECT T.ESEQID, T.ATTACHNAME, T.INFOCODE

FROM NEWSADMIN.ANN_BASINFO T

WHERE T.ATTACHTYPE = 0

AND T.NOTICEDATE >= TO_DATE('2013-04-09', 'YYYY-MM-DD')

AND T.ESEQID > 4975226049

AND t.sourcename <> '发行人网站'

and rownum between 1 and 50

) H ) W

WHERE W.R > 1