ORACLE external table (外部表) ORA-29913 ORA-30653 处理

[fs03@ibmtest tables]$ cat nation.tbl 
|ARGENTINA|1|al foxes promise slyly according to the regular accounts. bold requests alon|
0|ALGERIA|0| haggle. carefully final deposits detect slyly agai|
b|ARGENTINA|1|al foxes promise slyly according to the regular accounts. bold requests alon|
2|BRAZIL|1|y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special |
3|CANADA|1|eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold|
4|EGYPT|4|y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d|

oracle数据库：

SQL> create or replace directory SAMDATA as '/home/oracle/tables';

Directory created.

SQL>grant read，write on directory     SAMDATA  to public;

SQL> CREATE TABLE H_NATION1
  (
   N_NATIONKEY INTEGER ,
   N_NAME CHAR(25) ,
   N_REGIONKEY INTEGER ,
   N_COMMENT VARCHAR(152))
  ORGANIZATION EXTERNAL
  (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY SAMDATA 
     ACCESS PARAMETERS
             (
                     FIELDS TERMINATED BY '|'
                     OPTIONALLY ENCLOSED BY '"'                     
                     (
                     N_NATIONKEY,
                     N_NAME,
                     N_REGIONKEY,
                     N_COMMENT
                     )
  )
  LOCATION ('nation.tbl')
  );

Table created.

SQL> select * from h_nation1;
select * from h_nation1
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

SQL>  alter table h_nation1 reject limit unlimited;

Table altered.

SQL> select * from h_nation1;

N_NATIONKEY N_NAME               N_REGIONKEY N_COMMENT
----------- -------------------- ----------- ----------------------------------------------------------------------------------------------------
          0 ALGERIA                        0 haggle. carefully final deposits detect slyly agai
          2 BRAZIL                         1 y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special
          3 CANADA                         1 eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
          4 EGYPT                          4 y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d
4 rows selected.

updb数据库

SQL> conn sys/updb
Connected to UPDB.

SQL> create or replace directory SAMDATA as '/home/fs03/tables';

Directory 'SAMDATA' created.

SQL> grant read,write on directory SAMDATA to guodian;

SQL> conn guodian/guodian
Connected to UPDB.

SQL> CREATE TABLE H_NATION1
 (
 "N_NATIONKEY" INTEGER ,
 "N_NAME" CHAR(25) ,
 "N_REGIONKEY" INTEGER ,
 "N_COMMENT" VARCHAR(152))
 ORGANIZATION EXTERNAL
 (
    DEFAULT DIRECTORY SAMDATA 
    ACCESS PARAMETERS
            (
                    LOAD DATA INTO TABLE H_NATION1
                    FIELDS TERMINATED BY '|'
                    OPTIONALLY ENCLOSED BY '"'
                    ESCAPED BY '\\'
                    LINES TERMINATED BY '\n'
                    IGNORE 0 LINES
                    (
                    N_NATIONKEY,
                    N_NAME,
                    N_REGIONKEY,
                    N_COMMENT
                    )
 )
 LOCATION ('nation.tbl')
 );

Table 'H_NATION1' created.

SQL>select * from H_NATION1;

N_NATIONKEY N_NAME                    N_REGIONKEY N_COMMENT                                                                                           
----------- ------------------------- ----------- ----------------------------------------------------------------------------------------------------
          0 ALGERIA                             0 haggle. carefully final deposits detect slyly agai
          1 ARGENTINA                           1 al foxes promise slyly according to the regular accounts. bold requests alon
          2 BRAZIL                              1 y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special
          3 CANADA                              1 eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
          4 EGYPT                               4 y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d
5 rows selected.
 
SQL> select * from user_external_locations;

TABLE_NAME           DIRECTORY_NAME                 LOCATION                                          
-------------------- ------------------------------ --------------------------------------------------
H_NATION1            SAMDATA                        nation.tbl

1 row selected.

SQL> select * from H_NATION1; 
TBR-10075: Exceeds the reject limit.   
TBR-5074: Given string does not represent a number in proper format.   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29289867/viewspace-1991622/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29289867/viewspace-1991622/