今天同事告诉我加载程序报错:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
确认是insert操作报错,后来转换为delete加载时候还存在这个报错。
本能的怀疑时间字段有问题,时间非法。
后来查到发现一个很有趣的事情:
说是Oracle date类型不能储存时间字段0年,但他怎么就储存进去了呢?后来了解到通过sysdate运算得到的,详细见下面测试示例:
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAiEAAABnCAYAAADWiZLfAAAVLUlEQVR4nO2drXLjvtfHv/7Pcyl2QadXoFyBExJUWqbMooSEFS4LcVAnRhu6KCQy3CXxTHmnoAroXokeYOfdjuXEL+mv38/MAU2PdY5kWTrWm53Pz08DAH///sXr6yteXl5ACCGEEFI3/2vbAUIIIYR8TxiEEEIIIaQVGIQQQgghpBUYhBBCCCGkFRiEEEIIIaQVGIQQQgghpBUYhOQglYExe6IDiLadIhlIKGOgg5bujpAIlIYxCrImf0SgYdrM438IqYqfZRudoyss7nnL9fRqEv9vpR3MfybSclYBZCWOSgRaH/QF6svew9uEQUgOYdeB4yQyiNr2htwkUkGvZuhhgo7TRViTmfhdAwD0e1yTBXIrCBlA6/2XH4XzsY1KOkbZmIs3Qf4zEaLrdDD56GG20leXi1QzDLFEx9n0Bx38/P0dnkMBIQMorWsPnC2CEAGpNPT+qIDR0PsRcfo2qJWCUhpaq6Mo9Ku/BTTBVyyjr+hzRYgAeuZDDxx43RC1NktvH1jXmT65KSZPnW2HN9A+hiudE4hIqLHfkFchuo4DxxvVW9dtOftMxAhHHpyBhj/LKzsbJPo+sF7+3stzjPgmCiCPa9tkkc4CrDAf9+C5bqXeZVEYhEi1wsxbYtLZjQx0Ok+YLN83GlCrGe4WHrxuF92uB+/pA+PcB4eQr498HsKNBujWNfxxwhofb03ZIm0RhyOE214uRvhzijVc9B5PG1OpxsBkgO87UFvwTIRdDCIXw+dvNkx0FfF2FsDzJtANWCwIQnaRYLgX/cVxjHCUvP2JYAx/PcXP/cY4HmFS8uZrJauZaxQSSh+N2mSNyYngQO9i+zbpHOlsfEr00rlWM4MPwB2u9vQ26wzK+HI4f1nPKIW9zyJQe6NoGiqoopx1Zjnv2zq551Zls3uLOPD7eB5cBBj7QLQ4H4Ec+3PxnYhH8BwPo4M3MIFAG5ic8eZkXYNKyqwRHcu1AlIldUQmZaOVTEaV0qkHaatjyeE9CHB/oc5l9rI9tdEpRCqMveVhu3uqlD6nGS+ElmV8sjYu198ztgC7dtm27QZynolTwkUE+ONyL8Sbcshs3/bzZ9denG0DK6vrNm1y9rqe8mugjm1eOeDw+flpPj8/za9fv8yPHz8MgAORyhhjlAmEOPkfIEygjTFKnvxPJP8wEjCANMoYo4OsNFI7gTLaaKMCaUSOTrHs/BF7v4lj30VgErX0d5H4l5WPbRno4NQvm3SSAjT62CcpjtIrLqNCEdIEcne9SBw3gbi0PIvkvM9J1vXOp7S88so5V/bK8LT80jI32uhN3ZEqvS9ly2Yvrc39EoHRx/f+bLnu0lBH+b7q3uaWS4Yf23rZsE7JeylEYLTRRqvAiLTcdrYKdErY2twHIdP6t38/bXQsn4PzddCynh5J0o5m1dPNb+fKZGfz5L6VLuP8tr7QllW7bNl2l5aSdSbj2vzntri9KGwDq6rrtj5n2j/fltnk/6o+pigISRp8ZbROG9ZAGiEsnDzInG0HK4xUemfn0kqjzl2b/TDlBhq5/7NJJ9WxatAqCEJyKtxlD+C1FT77f4fBqY0UlWFWAGlRlpllcz4YtctDtu1z9etyya6DItCndbARHQuRald2aaObtBFJ+rsgpEDnwrLJfD5LtAXnnoPzdfCCeprz/CbFIw/SKP2Mly7joiCkuHzOt8s2OpfINe2qZSdcEJidbQMrqevl8pvYN0ZJu/xV/vJ0JBYLU2PEYRee56DTmQC9GVYVrDrOsxV2PTidCT7uxliVHr4P8XMaAf4suVaKjGsfcOcC8GcHw4wzH4B7hwdrWzbppDr6vaHFXAIyULvV9ash6l9WlOfKPTycrl5PVrX76FvXH7syXBcumLAvm+K0LqRU/bIhxmhyPNws8Tx0EU02Cwib1NltnTyWOqYF82wlJPXm/L200bHHJh1rWyKAXg2Baedw3ZFUmPkRBs0tRqoAm3bZRuc2yb2nlbWB1RKPPAwiwJ/N4K+neCqa06qZUlt04zhE13MwiFz44wACb/hYA+7dadMq7j0AGk3vKoxHXXhOB53BEhjP00AmozpHg+1C251csM2yqnQqQKoVZkMPy0m6ur4z5a6KlCrLJmlEPNzfQisZ/sR0vVt/lblGq0GdeORlPA8OvBoaujxbXx8JlQYgx+WWtKs+ZtugK1kD4M9u5wyPLGzaZeu2+wK4vf12ueickLck8sADYvxergHv/qTyP9y5QLQo2Rkn24HNaoy7jwk6jofu6JLtjzHicISul27TGs733t6SwCnL53LYpFOVLRvSRcTTJ4zCG3jg4ndoAN5RT500ohEK1nTuUUUZVlw2bx9Yw0VG7J2DQJLtss+DDfsjFKcjE83r3BZZL0iX6DSHQKBn8KNBZuB2Gnglu2OiwQ1tn83lXLtcRqcEsg+/rZ1llbWB1SICnYymDaZYu0OsWj5kpnB3TKACSLFXiGnjs2lQ49EEkTvEwUYYEWDsrzE9v3T70FKgoM0c/WuCDyERHEzfCMi+j8MRmbQhdYeYq0NdIctM/diks9NZHeuIY0ubUaXHCzvb9Pre5noB+dzLnXLYDmVfVQHP+RxiEQHucI5gc2iMCDAflg1OD8swTSij/Cz8tCybYpdGmESAf2Y81R0+b8/KEfIZSbZPc13JfdiMUKxmp6MXbejcBEn9g99P74OADHQyXVpKp1lEMMfQLdd25lPR7oVrbdm0y1Zt9wVe9X0gmhTuoqmHqtrAMhT0I6n9aNBFGI7wNF0D/uzC5RXV1K+CIOQNQA/j+d52n9UYmHbgbOckkxPqPvq7w8rMqofl4HT71OG2ocO54vHdAk8Xj3zs0Rtjvk1/hbEXYdA5mh4Ju+gMptDeDKut7hzzccnNeTbphF04nSkib7yns8L8+eGoksQYPU0RecM9vTI3N70e6fV6jvvFEwbTNfxZXVt1z/scdh10phq9WXrfVz3o6QCdsvPZe2WYX34WflZYNuHPKdb+DCrnbOhosEB/npTJauYhGnRqPFMkDdSAMyMTTercBmG3g2nkYbZKnss+nk5OP7bRaQ6Bx54LwMVwVf+amkaxaZdtdMogFWYlX4arprI20JpzbfJumm9jfrc+ZKOTbv3P2e5by6BJ8e6YsrLb4aJVYOTVW6wo9Uiy+rrulc/5stnedY4yu2hakHSL5XUr+qu5DzY7OprUoVDaE2FkurW5vp2BFADpjp4r2+nqg5CNc9IorVvs5CjnRASb/ejt+/KlRUgTqLLbjqu9D2J71sVt6FAo7clmq29gZG3nI1ESESbQFQR6tQUhFAqlVtns9z84GK1FHQqFQikrzufnpwGAv3//4vX1FS8vLyCEEEIIqZuLtugSQgghhFwLgxBCCCGEtAKDEEIIIYS0AoMQQgghhLQCgxBCCCGEtAKDEEIIIYS0AoMQQgghhLQCgxBCCCGEtAKDEEIIIYS0AoMQQgghhLQCgxBCCCGEtAKDEEIIIYS0AoMQQgghhLQCgxDytRASgdIwRkHWZSLQMMZAB6ImC4QQQgAGIeSLoVcz9DBBx+kirMlG/K4TW+9xTRYIIYQADEKaQ6oL364FAm1gzBnRAQSwHSXQSkEpDa0V5IUv80Iq6AO7es93CZWRF6n2fDn8R07ek3QO86Kh8pwWAfTAgdcNEZ9Lw2ioa0Yx3j6wzjSvYYxGVtJSGRglG9UpZlM2R6NGB/fDRocQQmri8/PTfH5+ml+/fpkfP34YAJQaRCqToAMjrklLBEYbbQJx/D9plDFGSRtdG1+1CaTY/iaE3Ps7saUDcXpdRv7y836cjtjZzvBZKpPh76kvItDGHJdFJWUsTKCNMUpm6CsjG9cpkqRsjsticz+SMrPRaeeZoVAo30AYhDQgm44j6aVLBwWnaZ2mIQKdHwAcd2RnJOnAi3wsEYSczXtGOmnvdxJAiMBoYxeEbHQvDkJKlX8SLJwP/mrUOSvSKKOMOqgD0iijjVJ6Lwgp0qmhHCkUCgUwnI5pAPHYgxstEIYLRHDRe6x6iFvgsecC+h3HqxjePtaA37dcxJmms17id0XLIS7L+xofb0c/PdzBzZwkOTYoEDwP4a6n+FnHopF4hEnkYviclqh8xhBHtprUsWCxiHZ1QPbhr5dYfJTXIYSQqmEQUjtJxx4tQgAhFhHg9h5P101cxQPuXGB90nNvFll6uLcymKaz/H0SzFxGybyLAHrmA9EEoyMHxL0HQOdacoerZD3IaoUepuh4o4rycErYHSDyxwiEQDD2MH06tdWkTrHDC0Tw0ZeA7PtYL3/jpKbY6BBCSMUwCKkb8YieG2GRvr2Giwhwh3iua39pBbh3D3Z6m44/lZl/pGCZ910AMQSmHTjd8kMY62kHjuPA6Qyw9IZY5SzqrIYQP6fAcLVCb/l0EjA1qbPZTnwshwtKkwDQ7we499ZYZg5zFevY2SKEEHsYhNSMfB7ChY/ZptFOe2q/X2UU8oaPdXbwsBlBsNttmqQD795qpGbb8acyiA7/b5v3JJ0OpmvAHT5nTh1tRnQKiUOMvAEi7E1j1ED8e4k18jr05nTikXdwDzbiHUUr4SIC/CGGyJ9qK9KxtUUIIbYwCKkVib4PRIPDRrszLbNOw4YYv5frzODh4c4FooXlmRppOpWM1JTNe5wGDz5mWdtP3z6whmtpu1ww9S0IF4hQMNVmo0MIIRXCIKROZB9+xgLJ5M02mX+ving0QXQcPIgAY3+NaYmVjPHoCdM14M+SszZ2nbiAkMK+U78o7yG6nSnW/ux0iD8eYRJlXZOBeEQvZ23LdkrB6pyN/xIhuoWjFjY6hBBSHQxCamSzwO+kSY9/Y7muekomRLczwEd/77Cy+R0mHS9nrUEeMUaeg85Aw+utsNrO/c8x79utFQGuyHs8wtN0DXe4wnGcEP6c5h5kdrA+ZTUEogGe2JkSQshtw3NCvovsDqXKx/YQrPZEG2O0klcc+JactcHzLygUCqV94UjItyEZas9aWLiT+r7HUhVeZ4Alxlhd+AE7EczR01OOkhBCyA3gfH5+GgD4+/cvXl9f8fLy0rZPhBBCCPkGcCSEEEIIIa3AIIQQQgghrcAghBBCCCGtwCCEEEIIIa3AIIQQQgghrcAghBBCCCGtwCCEEEIIIa1QGITYfL67Cp2m7Nyijg235nPbOrfkC3X4mUBCyGXwsDJCCCGEtAKnYwghhBDSCgxCCCGEENIKDEIIIYQQ0goMQgghhBDSCgxCCCGEENIKDEIIIYQQ0gr/AwDHcdr2gxBCCCHfjP8xAGkCASEDKK2vO9hJBFA6PSRKK2QmVZVOgz4LqaC3h19pqEvsFdqRUCcHbSnIJvKUW8YlkArGGKgTh5vNFyAg1d6hZVojuMgYIYQA+Pfvn/n375/59euX+fHjhwFAqUyEkcoYY4zRWhttjNGBuCwtERhtjFEyuV5IZYzRJhBV6zTs89FvIjWuZMV2II06+a3OPCkjRVqegc7wp4xIo7TJKZcG8wVhkqwEad5gIKQR19qmUCjfVxiENCXSqCs69KRPCIw4/k3JynWa9FkE+kgn6ejKBCF2eaqmsy62lfh/WGZp551ZxjY2tVEyuRd1BSFWZbgJrq6wQ6FQKPvChalfAom+D6yXvxHv/fr2sQb8fjr0XpVOkz4D8bsG3B4eN0P/4hE9d42Pt2rtVIONrQfcuYB+39eI8a4BePc4nOHYTKXo/OkaqTD2lvgZXu97vi27MpR9H4gWuNoVQghJYRDyFRD38HDcsaUdODzciwp1mvQZAMIuBhEwXGkEQQA970EPPIzi4wSvtAMAcDFcbdadSJTObglb3oFhgXsPgHuHh1IGJdTMw/JphPPF0US+0kDlAwdrQrS6wB4hhKQwCCEtk3Zha6A3HMKFLjEKUoYQXceB4zjoDJbwhjOsTld5VmLn53QNdzjfjjgI+Yyee86n7KBLqhn8aFIQkNnm67wtW9zhGP3FExzHgdOZAv4Mc35FlxByIQxCyEVU9Vl3Ecwx85boeB48p4MpfAxXh7tAsmxds2MnDkfwBhHgj6/ftZKV/shLR3cSX+f9BSbTNbD+gP0sk8LMjzDo2k9+1J0vAMB6iZ9hGsXEIzxN13CHzxVPexFCvgsMQr4C8TuSJQWHPYu49wBovMcV6ti6NPKSt+Ej8Tav2Va2JJ6H7t5ahBgjr4PpGvDHwXaYP8tWOTsZvH1gDRd3ZeZHStgKuzufvW6IdKFIwbTKcZo+ZtvAawYfgD8zMDrInwKpLV9v+FjjJA/JlA0hhFwGg5AvQYhFBLi9x4PO5+HO3VsoWJVOkz5nkS7irNvOwx1clFkAe4WtdD1FtCgxqnESeA0QAYgGDhzvzBqR2vKVvbhW3HvlRngIIWQfbtFtSq7b7trcOSHN+pwcC6L2zp0Iyp9NYnnGhdg720KZ0y2p1d0HcXCOxuZsjVNbqR9WW2zztuja5qvAVom6s703IriuflAoFAqDkDolPR8ih1IHcgFJo79JT6vczuQ6naZ9FkaqfYN62xFWaiftMLdWlCwfgNjakoHR2sZWBUGIdb4sbJWtO0YbxQCEQqFcIc6/f/8MAPz58wevr694eXkBIYQQQkjdcE0IIYQQQlqBQQghhBBCWoFBCCGEEEJagUFIzQgZQOu9g7au+ay7zafWm9Rp0udbsUNb1dgihBCAW3TrFiEDI8VmB4FIt6Re8NXTJrfoltrG24DPt2KHtqqxRaFQKBthENKwXHIOBuw+td6kTpM+34od2qrGFoVCoWyE0zFfAptPrTep06TPt2KnDlsSyiRfvz2d1vjKtgghxI7/a9uB74Z47MHFGtPfJT7UYvWp9QZ1bFyvyuciW03Zoa1qbBFCyB4MQppEBJgPXUQD56rPqZP/CiG6TrVf7LkNW4QQYgenY5pCBNCrITDtoMTX2W8WEejdjp890TVsl8iyVYcdQgghzcIgpBEkVBqAeJcMgdh8ar1JHWR95TX9bP0mfzXbqsNOIbR1vS1CCNmHu2PqlvSDcFfuIODumPbt0BZ3x1AolIqFQUi9IpLvuF9/lgLPCWnfTuW2Cr5s+2VtUSgUiqUwCKlT0lGQDMqeEwKg/KfW69Zp0udbsVOprYLA4MvaolAoFDtx/v37ZwDgz58/eH19xcvLCwghhBBC6oYLUwkhhBDSCgxCCCGEENIKDEIIIYQQ0goMQgghhBDSCgxCCCGEENIKDEIIIYQQ0goMQgghhBDSCgxCCCGEENIKDEIIIYQQ0gr/D5/FXbygaTC2AAAAAElFTkSuQmCC)
后来我用to_char()转换后这个数据既然后面月、时、分、 ... 全变成了,而且Oracle还不报错。
是不是可以说:Oracle数据库在实际写入数据前没有做合法性检查,或者检查的不严格,导致非法数据也可以储存到数据库里面,最后用起来就是各种奇怪的问题了。
阅读(125) | 评论(0) | 转发(0) |