带有utf8的SQL Loader [英] SQL Loader with utf8

问题描述

使用SQL * Loader加载日语数据时出现以下错误.我的数据库是UTF8(NLS参数)，我的操作系统支持UTF8.

I am getting following error while loading Japanese data using SQL*Loader. My Database is UTF8 (NLS parameters) and my OS supports UTF8.

Record 5: Rejected - Error on table ACTIVITY_FACT, column METADATA.
ORA-12899: value too large for column METADATA (actual: 2624, maximum: 3500)

我的控制文件:

load data
characterset UTF8
infile '../tab_files/activity_fact.csv' "STR ';'"
APPEND
into tableactivity_fact
fields terminated by ',' optionally enclosed by '~'
TRAILING NULLCOLS
(metadata CHAR(3500))

我的桌子

create table actvuty_facr{
metadata varchar2(3500 char)
}

为什么SQL Loader抛出错误的异常(actual: 2624, maximum: 3500). 2624小于3500.

Why SQL Loader is throwing the wrong exception, (actual: 2624, maximum: 3500). 2624 is less than 3500.

推荐答案

所有数据文件(UFT-16除外)的默认长度语义是字节.因此，在您的情况下，您有3500个字节的CHAR，而不是字符.您的文件中包含一些多字节字符，因此2624个字符占用了3500个字节以上，因此(误导)消息.

The default length semantics for all datafiles (except UFT-16) is byte. So in your case you have a CHAR of 3500 bytes rather than characters. You have some multi-byte characters in your file and the 2624 characters is therefore using more than 3500 bytes, hence the (misleading) message.

您可以通过使用字符长度语义来解决此问题

You can sort this out by using character length semantics instead

在控制文件中更改此行

characterset UTF8

对此

characterset UTF8 length semantics char

，它将以CHAR字段(以及其他一些字段)的字符形式工作-与您建立表格的方式相同，因此3500个字符(每个字符最多四个字节).

and it will work on characters for CHAR fields (and some others) - in the same way that you have set up your table, so 3500 characters of up to four bytes each.

有关字符长度语义的信息，请参见《实用程序指南》 有关更多信息

这篇关于带有utf8的SQL Loader的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！