Oracle 处理外部表错误

Oracle 处理外部表错误

在将数据文件中的数据转换为表中列数据时，不可避免会现一些错误。当出现错误时，用户就需要收集错误信息，从中找到导致出现错误的原因并加以纠正。在创建外部表时，关于错误处理的子句包括：REJECT LIMIT、BADFILE和LOGFILE子句。

1．REJECT LIMIT子句

如果在创建外部表时，使用了REJECT LIMIT子句，则在将数据文件中的数据转换为表定义的列数据期间，数据库将允许出现特定数量的错误。如果用户在执行一个查询，而Oracle遇到了超过这个转换数量的错误，那么查询将会失败。

在默认情况下，REJECT LIMIT子句指定的数值为0。可以使用REJECT LIMIT子句设置允许出现的错误数为UNLIMITED，这样查询就不会失败。如果外部数据文件中的所有记录都由于转换错误而失败，那么查询这个外部表时将返回0行。

例如，在下面的示例中将重新创建的外部表EMPLOYEES_EXTERNAL，并在指定字段分隔符时使用分号“;”，很显然这在转换数据时将发生错误。但是，由于在其中使用了REJECT LIMIT子句指定错误数为无限UNLIMITED，所以要成功创建外部表：

SQL> create table ext_emp

2 (

…

)

10 organization external

11 (type oracle_loader

12 default directory exterior_data

13 access parameters(

14 records delimited by newline

15 fields terminated by ',')

16 location('employees.csv'));

17 reject limit unlimited;

表已创建。

虽然忽略了在创建外部表时发生的错误，但是，这意味着Oracle可能无法读取数据文件中的数据。如果这时用户使用SELECT语句查询该外部表，则无法返回正常的数据。

2．BADFILE和NOBADFILE子句

当读取外部表的数据文件时，数据库可能会遇到数据类型转换错误，不能够将源文件转换成数据库中为外部表定义的列。这时可以在创建外部表时使用了BADFILE子句，将所有不能转换的数值都会被写入BADFILE指定的文件中。

SQL> create table ext_emp

2 (empno number(4),

…

9 deptno number(3))

10 organization external

11 (type oracle_loader

12 default directory exterior_data

13 access parameters(

14 records delimited by newline

15 badfile exterior_data:'emp.text'

16 fields terminated by ',')

17 location('employees.csv'))

18 reject limit unlimited;

表已创建。

根据前面的示例，如果在外部OS文件中有这样一条记录：

79814,A TG,MANAGER,7839,1990-12-26,2500,0,10

则Oracle就不能读取它，因为79814为五位数，而EMPNO的定义允许4位数。查询该表将返回能够成功转换的所有记录，而不能转换的记录则会被记录到BADFILE文件中。

与BADFILE子句相反，如果在建立外部表时使用NOBADFILE子句，Oracle将会忽略数据类型转换错误。如果用户在创建表时没有规定BADFILE和NOBADFILE，在默认情况下，Oracle将使用名称与外部表相同，但扩展名为.BAD的文件，并且该文件位于数据文件所处的目录中。

BADFIL可以为表的所有者提供一个可供分析的文件，找到发生错误的记录，以便DBA 调整数据或者表定义，确保源文件中的所有数据都可以由Oracle读取。

3．LOGFILE和NOLOGFILE

在第一次建立表时，外部表中会经常发生错误。例如，操作系统限制Oracle读取文件，或者数据文件不存在等。当发生错误时，Oracle将在日志文件中记录这些错误。LOGFILE 子句用于指定记录错误信息的日志文件。如果要忽略访问外部数据源时所遇到的错误，则可以使用NOLOGFILE子句，这样Oracle将不会将错语信息写入任何日志文件中。

如果在创建外部表时没有规定LOGFILE或者NOLOGFILE，Oracle将会使用默认情况建立一个LOGFILE文件，该文件的名称与外部表相同，扩展名为.LOG，该文件与数据文件位于同一个目录。