SQL*Loader装载Blob问题,高手都过来看呀

Question

2|FFD8FFE000104A46494600010100000100010000FFDB0043000A07070807060A0808080B0A0A0B0E18100E0D0D0E1D15161118231F2524221F2221262B372F26293429212230413134393B3E3E3E252E4449433C48373D3E3BFFC0000B080078005A01011100FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFDA0008010100003F00ED9F51BC3D679318F5E9513DEDC13CCCFF004C9A61B99481FBD738FF006AB3EFF5CB6D3E132DCDD053D817AE5B56F8870288D60997DC035560F89368F8491D949FE22BD2B4A2F1C69692002E8BE467DAB774ED72CB54CF91282E3AA9EA2B40E739CE6A29327AF5ED5167E6F43EB4BBBDBF955D607AD30E7F1AE5FC61E2C8343B6F2D5B75C480ED41D6BC7EF750BAD42769AE65676273C9CE2AB1268E2943153904FE75AFA2EBB73A6DC2B236327AD7B3681ABAEB3622420798A30D8AD174F5A871E829430C77ABECBCE00CD51BEBA8ED2079A4E88A49FC2BC175FD55B59D667BD39DAED8407B28E959D5A7A7687717E0363629E848EB5B767E0C959F32E31566EFC160443C85CB77C57297DA6DD69B3159E364C7424706BD27E135CF9F05E42EC0B263A9ED5E8120038CF5EDDAAB1001A31FE735A654639AE5FC6C8C3C3D7ACB9522262315E115B5A3696B7189651C03903D6BB9D2AD42E114023DFB5749159ED45E86B634AB380CA04AB9CF43597E34F0E5B5CDA38312904641C7435E71E02BE9348F144D62012261B0E3B106BD8882783DEA020E4D26DFAFE75A8C0600E40EF5CFF8CEDCCBE1ABC55243344707BD780DBC2D713AC6BD49AECF4F48ADE1009007BD6FE99736C6505A78D79C60B62BAC824B7DA009D1B3E86A44D6F4DD372D7370A31D3D6A4D435FB3D574EF32DE394C638C95EB5E7506951AFC405640479909963C7722BD3ADD83DBA37AF6343E738FF269981FE456AF00027B572DE3332CFA15DB40E13CA4241F5AF16D2940D533FC273835D56C8ADE3F3648FCC27A0C54113C97578B0BE941C32E438EDF8FAD6FF87513EDBF672CC809FBAC7A558F12DACB637E45BC5192470CFC8CD4BA1CBAEDC5A23CE610A58AB4431903D6A4BDD301F14E9D7782AD16795AEC23C0C8C601E4531813918A663DEB4E607076F5F7AC8D434E37B6A229461594E403C1245791CDA52E9B7851976C91C8508AE8AD6CD6E1141ADDFEC4B6B5B2F38F2C3A0EC6B0AD8ECD514A9F9F776ED5DDDD5B5ADC41FE9880920156239AB5A3D8DB4119050608ACAD62351791BC79CAB8E9E99AD542085F6E0E28603D3AD47819FF00EBD683A6E9587A31C54651638483C633835E37E2DD4BFE2A4923DA332B8CE7B62B674DB8C46849C71D6B46FB50926B75843702B2ED2D2F0DE2B5AB26FCE72475AEB6CAD6F6EA366BDBD564618F2C0FBA47BD165793DB3BDBCADBF67DD61DC55DB380DDDD3CD273B3A035A4B6EA92330EE2A39571517FC0AB5641B269074C31AABA8C9E4E9934E403B509E6BC1FC5C8D16BC276042B28643FDEF715B9A35CADCDA2953EFC55CB8327964C432FDB3556D2EEF85C0525617EC7B56ED9FF0069CE768BE4527AED5C8AD4B58A4B666133798DFDF3DEBA2D142C96AD32F46723F2ABCFC67DEAA4C2A3E3D4569DC464DDCC79FBE718FAD366844D6EE8C32ACB822BC67E23E891E956B01174ECBBCAC30B00768FAF5C5616842EF4FB48EEDF3E4CCF8418AE9A0BA8E5EE0135721B34B938660056EE9D676D6A9912671CF5A91DA7D46E05A58A167738C819C7BD7650E9FF00D9F691C448638CB9038DDDEA36604E7355E55C93FA557C0F4AD9B9F96E65E7F8CD46CDC0F4ED5C078F34DB3D72F2DCC529F3AD0FCF8395FA7D6A3B0D2A1FECE5B3962568F1D31C550BBF08387DD67381DC06EDF8D2DAE83AE97548C42D9EE5B15D4E93E1160AB26A17A589C318E11804771935D6699A6C169104B58562C6416C72D83EB5ABE4831E31BAB32EAC91794181E80F27F0A864D3E568F2A4B1F4C553FB24D9FF0054FF00F7C9AB1ACDFC1A7C933CC79C92AA3A9AE4EF755BEBB2B874447FF966A48CFD4D538ED86FDAE8AA064B3039C9AD2B78BE4E3EE9F6A9D20DDB4004671576DECC41B19A5C670C3F3C62B6ADD618F0323962BF5F4ABD12B8DCC085CF3B4FB75A95660A4C4CDD7E6538EAB5109019CAA0E4E021C76EF53C6815B2B9C39CF3DA9C6DA127257AFF00B46B89D76D9E6D7E790A060A78C9F6AA2608CC8C0460E4F4C74A5B7B0FB449C03E5A287720638EDF9D6A69761E6ED565C166201F5E6A736052E7C8CBA71856C7BD596B130184DC92CA01C32AE4722AE41A7B2A19D4F999C15538007A1ABD6F32C8CCDB3630C8C67AF7A826DCD1B1C8009C295EAA7AE6AA47348F3E1B2B2A8C94C6411D4953DFB56C7DF857390700D3C608CEDEBED5CC6A9067519E43D33FD2AA0811B1B50ED039183F37A74E9CD5C1035B7EEE21F349F34849EBEDF4A98468F046F6CDF3AB6595719041EB53C7247299164622618EE39AB30868163126E68CE000DCED14F68B646EF0CACCADC907907E94D223BE08532B228E73C15E28673E5C91CA8544792ADED8EF55625F9C4671BD40962200CE3B8ABB35D17D8A9196DEA4FCBC114A2EF000DB37FDF354751855AE9C81E630E76FA567E1A3424939197CF5E9D062A68A578E58270A1D5E231B0F43D735620B791775CC241C370BD31C54E6D85C48D320DBC13B7DEA6B6B92EAB0CA391852DC734E712DBC842FCD1B01918FBB4D92368CACD6CC092338F5FAD25D4BE669B2B44577E30D45EA986DE29907CD181C7B52C0B99015C6C6F9D07A1EE063B55BDE9DD8E7BF06BFFFD9
--------------------------------------------------------------------------问题:
    用上面这个控制文件导入照片后,SQL*Loader直接把ZP的十六进制字符串当成的字符串处理了, 没有把十六进制字符串转换成二进制字节流. 
    如果目标表的ZP字段是 LONG RAW的话,上面这样导入没有问题, 换成Blob就不行了, 试了好多方法都不行,不知有什么别的什么好办法没有, 还望各位兄弟姐妹帮帮忙

Answer 1

按Oracle文档中LOB类型数据的导入都是生成文件导的，可以看看Oracle的文档

Answer 2

Oracle中的Lob确实都可以通过生成文件导入
主要是需求要求Oracle 中 Blob 字段(客户的业务中间件目前只有Blob的实现), 要从SQLServer中把照片导入进来, 考虑到通过文件方式用SQL*Loader装载会速度比较快, SQLServer中的照片记录很大(可能有几十个GB),而且需要24小时内搞定实在不行只有说服客户 在Oracle中采用 Long raw 数据类型了
谢谢 gzh_seagull(不可) 回复

Answer 3

参考
http://www.chinaunix.net/jh/19/221336.html

Answer 4

谢谢http://www.chinaunix.net/jh/19/221336.html 提到的两种方式可能都不适合我
首先, 我的BLOB数据本来就在数据中,而且数据量巨大,时间又有限,导成文件在用SQL*Loader装载可能不合适, 如果 转成 十六进制字符串再 用 hextoraw , hextoraw函数 对字符串有长度限制, 超过4k就有问题了    不过我这里问题还是解决了,解决办法是将Oracle中的表字段类型改成long raw, 
先用DTS转成十六进制字符串,然后直接用SQL*Loader装载,SQL*Loader会自动把十六进制字符串转换为字节流.   数度超快, 10GB的数据只要 1个小时不到就全部搞定了