主页 / dba / 问题 / 273416
Accepted
ellie-lumen
Asked: 2020-08-08 09:23:04 +0800 CST

当我从外部表加载时尝试记录错误时，为什么会出现“缺少右括号”错误？

  • 772

我已成功创建错误记录表

BEGIN
    DBMS_ERRLOG.create_error_log(
    dml_table_name  => 'enzyme',
    skip_unsupported => TRUE);
END;
/

desc ERR$_ENZYME;

Name            Null? Type           
--------------- ----- -------------- 
ORA_ERR_NUMBER$       NUMBER         
ORA_ERR_MESG$         VARCHAR2(2000) 
ORA_ERR_ROWID$        UROWID         
ORA_ERR_OPTYP$        VARCHAR2(2)    
ORA_ERR_TAG$          VARCHAR2(2000) 
ENZ_NAME              VARCHAR2(4000)

但是当我尝试运行此查询时出现错误：

insert /*+ ignore_row_on_dupkey_index ( enzyme ( enz_name ) ) */
into enzyme
SELECT enz_name FROM EXTERNAL ((
  construct_id NUMBER(10),
  n_term VARCHAR2 (50),
  enz_name VARCHAR2 (3),
  c_term VARCHAR2 (50),
  cpp VARCHAR2 (50),
  mutations VARCHAR2 (50),
  mw_kda NUMBER (7, 3))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        skip 1
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('CONSTRUCT.CSV')
    LOG ERRORS INTO ERR$_ENZYME ('INSERT') REJECT LIMIT UNLIMITED) ext
    where not exists (
        select * from enzyme e
        where e.enz_name = ext.enz_name
    );

Error at Command Line : 79 Column : 5
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

第 79 行是 LOG ERRORS INTO 行。

如果我删除该LOG ERRORS INTO ERR$_ENZYME ('INSERT')部分，此命令将完美运行。

oracle insert

1 个回答

  1. Best Answer

    因为 DML 错误记录 ( LOG ERRORS INTO ...) 部分属于外部插入，而不是内联外部表。将其从内部移出。

    外部表具有不同类型的错误日志记录，可以使用BADFILE记录有错误行的子句指定另一个文件。

    更新：

    像这样的东西：

    insert /*+ ignore_row_on_dupkey_index ( enzyme ( enz_name ) ) */
into enzyme
SELECT enz_name FROM EXTERNAL ((
  construct_id NUMBER(10),
  n_term VARCHAR2 (50),
  enz_name VARCHAR2 (3),
  c_term VARCHAR2 (50),
  cpp VARCHAR2 (50),
  mutations VARCHAR2 (50),
  mw_kda NUMBER (7, 3))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        skip 1
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('CONSTRUCT.CSV')
    ) ext
    where not exists (
        select * from enzyme e
        where e.enz_name = ext.enz_name
    )
LOG ERRORS INTO ERR$_ENZYME ('INSERT') REJECT LIMIT UNLIMITED;
    • 1

相关问题