csv oracle表中带有约束条件NOT NULL的sqlloader

4ngedf3f  于 2023-03-15  发布在  Oracle
关注(0)|答案(1)|浏览(113)

我正在使用SQL*Loader脚本将CSV文件中的值输入到Oracle表中。在此表中,有一些字段具有NOT NULL约束条件。在我的CSV文件中,对应的字段为"",当发生这种情况时,我希望将一个空字符串输入到Oracle表中。
这是我的控制文件:

LOAD DATA
    infile 'F:\tar.csv'
    REPLACE
    INTO TABLE tar
    fields terminated by ',' optionally enclosed by '"'
    TRAILING NULLCOLS
    (IDTAR   ,
DATABACKUP   DATE "YYYY-MM-DD",
PAESE ,
R_ELEM  NULLIF (R_ELEM=BLANKS))

这是日志文件中的错误:
ORA-01400:无法将NULL插入(“MY_SCHEMA”.“TAR”.“帕塞”)
如何通过提供不同的值来避免错误?

zte4gxcn

zte4gxcn1#

您可以应用SQL运算符,如NVL(:PAESE, 'XXX')。请注意字段名引用前面的冒号。在原处:

LOAD DATA
  infile 'gian.csv'
  REPLACE
  INTO TABLE tar
  fields terminated by ',' optionally enclosed by '"'
  TRAILING NULLCOLS
  (
    IDTAR,
    DATABACKUP  DATE "YYYY-MM-DD",
    PAESE "NVL(:PAESE, 'XXX')",
    R_ELEM  NULLIF (R_ELEM=BLANKS)
  )

使用虚拟表:
创建tar表(idtar编号、数据备份日期、帕塞varchar 2(10)非空、r_elem varchar 2(10));
和CSV,其中第3行和第4行的尾随空格用于nullif()子句:

1,2017-08-01,A,B
2,2017-08-02,C,
3,2017-08-03,,    
4,2017-08-04,"",

则使用该控制文件运行将获得:

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Aug 4 19:39:23 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

日志上写着:

...
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
IDTAR                               FIRST     *   ,  O(") CHARACTER
DATABACKUP                           NEXT     *   ,  O(") DATE YYYY-MM-DD
PAESE                                NEXT     *   ,  O(") CHARACTER
    SQL string for column : "NVL(:PAESE, 'XXX')"
R_ELEM                               NEXT     *   ,  O(") CHARACTER
    NULL if R_ELEM = BLANKS

Table TAR:
  4 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
...

查询该表显示所有四行都已加载:

set null "<null>"
select * from tar;

     IDTAR DATABACKU PAESE      R_ELEM    
---------- --------- ---------- ----------
         1 01-AUG-17 A          B         
         2 02-AUG-17 C          <null>    
         3 03-AUG-17 XXX        <null>    
         4 04-AUG-17 XXX        <null>

很明显,用你想使用的实际默认字符串替换'XXX'。你说的是'一个空字符串',所以你可以用"NVL(:PAESE, ' ')"插入一个空格字符。但是你不能用一个 empty 字符串,因为就Oracle而言,这和null是一样的。

相关问题