oracle 数据未从 sql 加载器填充

data not getting populated from sql loader

提问by Gaurav Soni

I have a control file, loader.ctl, in C:\oracle\product\10.2.0\oradata\orcl.

我有一个控制文件 loader.ctl，在C:\oracle\product\10.2.0\oradata\orcl.

Content of loader.ctl file is

loader.ctl 文件的内容是

 load data
 infile 'd:\mydata\test.csv'
 into table emp1
 fields terminated by "," optionally enclosed by '"'          
 ( empno, ename,job,mgr,hiredate,sal,comm,deptno )

emp1table is already present in the database and there are 9 records in test.csv

emp1表已存在于数据库中，并且 test.csv 中有 9 条记录

I executed loader.ctl from sqlldr:

我从 sqlldr 执行了 loader.ctl：

Now, when I check my database I find no records in emp1... why is this so? After the commit why is the data not being populated in the table?

现在，当我检查我的数据库时，我发现没有记录emp1......为什么会这样？提交后为什么数据没有填充到表中？

回答by Ben

Firstly you haven't specified a log file, which means it's probably in the same place as your ctl file but it could also be in the directory you called SQL*Loader from or the directory the data is in - as an aside it's a good idea to call SQL*Loader from the same place you store the ctl file to avoid confusion. Go looking for it and the associated bad file.

首先，您没有指定日志文件，这意味着它可能与您的 ctl 文件位于同一位置，但它也可能位于您从中调用 SQL*Loader 的目录或数据所在的目录中 - 顺便说一句，这是一个很好的从存储 ctl 文件的同一位置调用 SQL*Loader 以避免混淆的想法。去寻找它和相关的坏文件。

I would always explicitly state the location of the log and bad - and discard file if appropriate - either in the command line or in the ctl file. I prefer the command line so you can put them all in different folders so they don't overwrite each other. So you don't have to change the ctl file every time you load something you can also put the data file (and almost everything else) in the command line. Something like this:

我总是会在命令行或 ctl 文件中明确说明日志和错误的位置 - 并在适当时丢弃文件。我更喜欢命令行，因此您可以将它们全部放在不同的文件夹中，这样它们就不会相互覆盖。因此，您不必每次加载内容时都更改 ctl 文件，您还可以将数据文件（以及几乎所有其他内容）放在命令行中。像这样的东西：

call sqlldr scott/tiger@mydb my_ctl.ctl data=d:110201\my_file.csv log=d:110201\my_log.log bad=d:110201\my_bad.bad   

There are two probable reasons for your problem.

您的问题有两个可能的原因。

1. As @JustinCave suggested you're simply selecting from the wrong table. We'll put this aside for now.

2. You've noted that you've reached a commit point so there should be data in the table. This is not at all the case. You have reached a commit point, but, according to your posted ctl file, you haven't specified the number of errors allowed. This means SQL*Loader is using the default - 50. It's possible to reach a commit point, where everything loaded before it is errors; i.e. you're committing nothing.

1. 正如@JustinCave 所建议的那样，您只是从错误的表格中进行选择。我们暂时把这个放在一边。

2. 您已经注意到您已到达提交点，因此表中应该有数据。事实并非如此。您已达到提交点，但根据您发布的 ctl 文件，您尚未指定允许的错误数。这意味着 SQL*Loader 使用默认值 - 50。有可能到达一个提交点，在那里加载的所有内容都是错误的；即你什么都不做。

Point 2 is the most likely cause of your problem. Look in the log file and it should tell you, in a not necessarily very helpful way, why you've got errors. The bad file contains all the data that hasn't been loaded, which you can check against the log.

第 2 点是您问题的最可能原因。查看日志文件，它应该会以一种不一定很有帮助的方式告诉您为什么出现错误。坏文件包含所有尚未加载的数据，您可以对照日志检查这些数据。

There are quite a few reasons for the second one to occur so here's a list of things that can go wrong with SQL*Loader:

第二个发生的原因有很多，所以这里列出了 SQL*Loader 可能出错的事情：

1. Your ctl columns and table columns are not called by exactlythe same names.
2. Your file doesn't exist.
3. Your table doesn't exist.
4. You have a delimiter at the end of your file, which means you need to add the option TRAILING NULLCOLS.
5. You have a new-line in the middle of a line - you're in big trouble. You'll need to ask another question with full ctl and table descriptions along with sample data.
6. One of the columns in your table is a date datatype. As every piece of data in a csv is by definition is a string SQL*Loader can't turn this into a date. I'm going to assume this is hiredate, which would become hiredate "to_date(:hiredate,'yyyy/mm/dd')"in the ctl file, where yyyy/mm/ddis changed to whatever date format you need. See herefor a good list. Of course you could always change this column to a char and deal with the transformation later.
7. One of the columns in your table is a number datatype and you're trying to load a non-number into it. Sorry, in this case you need to change the datatype of your column to a char.
8. One of the columns in your table is a number and you're trying to insert formatted numbers into it. Remember that commas and decimal points are not a number, in which case you could use the to_numberfunction: sal "to_number(:sal,'999.99')". As with dates you could always change this column to a char and deal with the transformation later.
9. You have a new line at the end of each line in your csv, which takes the length of the column over the maximum. Change deptnoto deptno terminated by whitespace.
10. The fields in your table aren't big enough.
11. You're loading multi-byte data, for e.g. UTF-8 into a byte semantic table meaning that the number of characters are the same but the number of bytes is too few. Change this to char semantic.
12. A number has a space at the end, let's say this is sal as well you should change this to sal integer external, which explicitly tells SQL*Loader it's a number.
13. Your file is called a csv but it's not actually. Somebody's renamed a pipe delimited text file as a csv ( this is just one example of quite literally hundreds of example I can give - .txtto .exeanyone? )
14. The simplest reason, that should probably be at the top, is that the data in your csv bears no relation to the specification of your table.
15. The characterset in your csv file is different to that of your database and Oracle is having problems translating it. Use the charactersetoption.

1. 您的 ctl 列和表列的名称不完全相同。
2. 您的文件不存在。
3. 你的表不存在。
4. 您的文件末尾有一个分隔符，这意味着您需要添加选项TRAILING NULLCOLS.
5. 你在一行中间有一个换行符——你遇到了大麻烦。您需要提出另一个问题，包括完整的 ctl 和表格描述以及示例数据。
6. 表中的一列是日期数据类型。由于 csv 中的每条数据根据定义都是一个字符串，SQL*Loader 无法将其转换为日期。我将假设这是hiredate，它将成为hiredate "to_date(:hiredate,'yyyy/mm/dd')"ctl 文件，其中yyyy/mm/dd更改为您需要的任何日期格式。见这里有一个良好的名单。当然，您始终可以将此列更改为字符并稍后处理转换。
7. 表中的一列是数字数据类型，您正尝试将非数字加载到其中。抱歉，在这种情况下，您需要将列的数据类型更改为字符。
8. 表格中的一列是一个数字，您正试图将格式化的数字插入其中。请记住，逗号和小数点不是数字，在这种情况下，您可以使用to_number函数：sal "to_number(:sal,'999.99')"。与日期一样，您始终可以将此列更改为字符并稍后处理转换。
9. 您在 csv 中每行的末尾都有一个新行，它使列的长度超过最大值。更改deptno为deptno terminated by whitespace。
10. 您表中的字段不够大。
11. 您正在加载多字节数据，例如将 UTF-8 加载到字节语义表中，这意味着字符数相同但字节数太少。将此更改为字符语义。
12. 一个数字的末尾有一个空格，假设这也是sal，您应该将其更改为sal integer external，它明确告诉SQL*Loader 它是一个数字。
13. 您的文件称为 csv，但实际上并非如此。别人的改名管道分隔文本文件作为CSV（这是毫不夸张地只是一个例子数百例子我可以给-.txt到.exe？任何人）
14. 最简单的原因（可能应该在顶部）是 csv 中的数据与表的规格无关。
15. 您的 csv 文件中的字符集与您的数据库中的字符集不同，并且 Oracle 在翻译它时遇到了问题。使用该characterset选项。

Off the top of my head that's most of what could go wrong with a load as simple as yours.

在我的脑海里，像你这样简单的负载可能会出错。

Now the advice. Specify. It's as simple as that. If you don't take advantage of the extremely powerful nature of SQL*Loader and the myriad of options it provides you will come across problems like this. Not only that when a supplier changes something without telling you you're less likely to notice the change.

现在的建议。指定。就这么简单。如果您不利用 SQL*Loader 极其强大的特性及其提供的无数选项，您就会遇到这样的问题。不仅如此，当供应商在不告诉您的情况下更改某些内容时，您不太可能注意到该更改。

I would also highly recommend ALWAYSchecking the log file after a load. Normally this is one of the onlyways of checking that your load was successful. SQL*Loader fails silently on almost every error bar ORA-01653- not enough space and puts all information about these errors in the log file. You will not know about them unless you check.

我还强烈建议在加载后始终检查日志文件。通常，这是检查加载是否成功的唯一方法之一。SQL*Loader 几乎在每个错误栏上都无声地失败ORA-01653- 没有足够的空间并将有关这些错误的所有信息放在日志文件中。除非你检查，否则你不会知道它们。

A typical ctl file would normally look something like this:

典型的 ctl 文件通常如下所示：

OPTIONS ( skip=1, errors=10, rows=10000, direct=True)
LOAD DATA
 INFILE 'd:\mydata.csv'
 TRUNCATE 
 INTO TABLE emp1
 FIELDS TERMINATED BY "," 
 OPTIONALLY ENLCOSED BY '"'
 TRAILING NULLCOLS
 (  empno
  , ename
  , job
  , mgr
  , hiredate "to_date(:hiredate,'dd/mm/yy')"
  , sal integer external
  , comm
  , deptno terminated by whitespace
 )

All these things, bar the column names and the table name are optional.

所有这些东西，bar 列名和表名都是可选的。

The ones I've added are:

我添加的有：

• skip- Number of rows at the top to skip.
• errors- The maximum number of errors before stopping.
• rows- The number of rows to load before committing.
• direct- Use direct path load.
• TRUNCATE- Truncate the table prior to loading
• TRAILING NULLCOLS- There are null columns at the end of your file.
• "to_date(..."- Specify an Oracle function to call when loading this column
• integer external- Force this column into a number datatype.
• terminated by whitespace- Remove whitespace at the end of the line or column.

• skip- 要跳过的顶部行数。
• errors- 停止前的最大错误数。
• rows- 提交前要加载的行数。
• direct- 使用直接路径加载。
• TRUNCATE- 在加载之前截断表格
• TRAILING NULLCOLS- 文件末尾有空列。
• "to_date(..."- 指定加载此列时要调用的 Oracle 函数
• integer external- 强制将此列转换为数字数据类型。
• terminated by whitespace- 删除行或列末尾的空格。

There's loads more.

还有更多。

Here are a few links that are great for further reading and more explanation of all the possible options available to you:

以下是一些非常适合进一步阅读和对您可用的所有可能选项的更多解释的链接：

http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_params.htm
http://www.orafaq.com/wiki/SQL*Loader_FAQ
http://www.oracleutilities.com/OSUtil/sqlldr.html

http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_params.htm
http://www.orafaq.com/wiki/SQL*Loader_FAQ
http://www.oracleutilities.com/OSUtil/ sqlldr.html