数据未从 sql 加载器填充

发布于 2025-01-02 02:54:09 字数 547 浏览 0 评论 0原文

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

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 )

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

我从 sqlldr 执行了 loader.ctl：

在此处输入图像描述

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

原文

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

Content of loader.ctl file is

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

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

I executed loader.ctl from sqlldr:

enter image description here

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

神仙妹妹 2025-01-09 02:54:09

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

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

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

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

正如 @JustinCave 所建议的，您只是从错误的表中进行选择。我们暂时将其放在一边。
您已经注意到您已到达提交点，因此表中应该有数据。事实并非如此。您已达到提交点，但是根据您发布的 ctl 文件，您尚未指定允许的错误数。这意味着 SQL*Loader 使用默认值 - 50。有可能到达一个提交点，在该点之前加载的所有内容都是错误的；即您没有提交任何内容。

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

发生第二个问题的原因有很多，因此这里列出了 SQL*Loader 可能出现的问题：

您的 ctl 列和表列没有通过完全相同相同的名称来调用。
你的文件不存在。
您的表不存在。
文件末尾有一个分隔符，这意味着您需要添加选项 TRAILING NULLCOLS。
你在一行的中间有一个新行 - 你有大麻烦了。您需要提出另一个问题，其中包含完整的 ctl 和表格描述以及示例数据。
表中的一列是日期数据类型。由于 csv 中的每条数据根据定义都是字符串，因此 SQL*Loader 无法将其转换为日期。我假设这是 hiredate，它将在 ctl 文件中变为 hiredate "to_date(:hiredate,'yyyy/mm/dd')"，其中 < code>yyyy/mm/dd 更改为您需要的任何日期格式。有关详细列表，请参阅此处。当然，您始终可以将此列更改为字符并稍后处理转换。
表中的一列是数字数据类型，您正尝试将非数字加载到其中。抱歉，在这种情况下，您需要将列的数据类型更改为字符。
表中的一列是数字，您正尝试将格式化的数字插入其中。请记住，逗号和小数点不是数字，在这种情况下，您可以使用 to_number 函数：sal "to_number(:sal,'999.99')"。与日期一样，您始终可以将此列更改为字符并稍后处理转换。
csv 中每行的末尾都有一个新行，该行使列的长度超过最大值。将 deptno 更改为 以空格终止的deptno。
您表中的字段不够大。
您正在将多字节数据（例如 UTF-8）加载到字节语义表中，这意味着字符数相同但字节数太少。将其更改为 char 语义。
数字末尾有一个空格，假设这也是 sal，您应该将其更改为 sal integer external，这会明确告诉 SQL*Loader 它是一个数字。
您的文件称为 csv 但实际上不是。有人将管道分隔的文本文件重命名为 csv（这只是我可以提供的数百个示例中的一个示例 - .txt 给 .exe 任何人？
）原因可能应该在顶部，因为 csv 中的数据与表的规格无关。
您的 csv 文件中的字符集与数据库的字符集不同，Oracle 在转换它时遇到问题。使用字符集选项。

在我的脑海中，这就是像你这样简单的负载可能出现的大部分问题。

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

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

典型的 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
 )

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

我添加的内容是：

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

这里有

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

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

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.

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:

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

There are two probable reasons for your problem.

As @JustinCave suggested you're simply selecting from the wrong table. We'll put this aside for now.
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.

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.

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:

Your ctl columns and table columns are not called by exactly the same names.
Your file doesn't exist.
Your table doesn't exist.
You have a delimiter at the end of your file, which means you need to add the option TRAILING NULLCOLS.
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.
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/dd is changed to whatever date format you need. See here for a good list. Of course you could always change this column to a char and deal with the transformation later.
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.
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_number function: sal "to_number(:sal,'999.99')". As with dates you could always change this column to a char and deal with the transformation later.
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 deptno to deptno terminated by whitespace.
The fields in your table aren't big enough.
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.
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.
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 - .txt to .exe anyone? )
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.
The characterset in your csv file is different to that of your database and Oracle is having problems translating it. Use the characterset option.

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.

I would also highly recommend ALWAYS checking the log file after a load. Normally this is one of the only ways 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.

A typical ctl file would normally look something like this:

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.

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.