I’ve successfully created an error logging table
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)
But i get an error when I try to run this query:
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:
Line 79 is the LOG ERRORS INTO line.
If i delete the LOG ERRORS INTO ERR$ _ENZYME ('INSERT')
part, this command functions perfectly.