混合固定长度和可变长度字段的 SQLLDR
我正在尝试加载一个包含如下记录的示例数据文件：
12312^123456^0015GRAJ1M7J0002^SOME GIBBERISH ^123
我需要将这 5 个字段与 6 个数据库列相关联，其中第一个记录分为 2 个：
'12312' -> '123' 和 '12'
以及其余字段以“^”分隔。
我现在所拥有的是：
COL_ONE POSITION(1:3) INTEGER EXTERNAL,
COL_TWO POSITION(4:5) CHAR TERMINATED BY "^",
COL_THREE INTEGER EXTERNAL TERMINATED BY "^",
COL_FOUR CHAR TERMINATED BY "^",
COL_FIVE CHAR TERMINATED BY "^",
COL_SIX CHAR TERMINATED BY "^"
....
但这并不能按预期工作，因为由于某种原因字段
"SOME GIBBERISH "
最终被插入到 COL_SIX 而不是 COL_FIVE 中，这给了我一个错误。
我的日志显示：
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL_ONE 1:3 3 CHARACTER
COL_TWO 4:5 2 ^ CHARACTER
COL_THREE NEXT * ^ CHARACTER
COL_FOUR NEXT * ^ CHARACTER
COL_FIVE NEXT * ^ CHARACTER
COL_SIX NEXT * ^ CHARACTER
任何帮助将不胜感激。
I'm trying to load an example datafile with records like this:
12312^123456^0015GRAJ1M7J0002^SOME GIBBERISH ^123
I need to associate this 5 fields with 6 database columns, where the first record is broken into 2:
'12312' -> '123' and '12'
and the rest of the fields are "^" separated.
What I have now is:
COL_ONE POSITION(1:3) INTEGER EXTERNAL,
COL_TWO POSITION(4:5) CHAR TERMINATED BY "^",
COL_THREE INTEGER EXTERNAL TERMINATED BY "^",
COL_FOUR CHAR TERMINATED BY "^",
COL_FIVE CHAR TERMINATED BY "^",
COL_SIX CHAR TERMINATED BY "^"
....
but this doesn't work as expected as for some reason field
"SOME GIBBERISH "
ends up being inserted in COL_SIX instead of COL_FIVE, which gives me an error.
My log shows:
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL_ONE 1:3 3 CHARACTER
COL_TWO 4:5 2 ^ CHARACTER
COL_THREE NEXT * ^ CHARACTER
COL_FOUR NEXT * ^ CHARACTER
COL_FIVE NEXT * ^ CHARACTER
COL_SIX NEXT * ^ CHARACTER
any help would be highly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
好的，通过尝试和错误解决了问题：
Ok, problem solved with trial and error:
问题来自您的第三个字段：
该字段从位置 6 开始，不幸的是，该字段包含
^因此该字段将填充 NULL 值，将所有后续字段推入错误的列。您可以使用 填充符 来忽略 <位置 6 处的 code>^ 字符：
The problem comes from your third field:
This field starts at position 6, which unfortunately contains a
^so this field will be filled with a NULL value, pushing all following fields into the wrong columns.You could use a filler to ignore the
^character at position 6: