外部表从文件读取时截断尾随空格 [英] External table truncates trailing whitespace while reading from file

问题描述

我正在尝试将文件内容加载到外部表中.在执行此操作时，尾随空格将被截断.

I'm trying to load the file contents to an external table. While doing this, trailing spaces are truncated.

'CREATE TABLE ' || rec.ext_table_name || ' (ROW_DATA VARCHAR2(4000)) ORGANIZATION EXTERNAL ' ||
     '(TYPE ORACLE_LOADER DEFAULT DIRECTORY ' || rec.dir_name || ' ACCESS ' || 'PARAMETERS (RECORDS ' ||
     'DELIMITED by NEWLINE NOBADFILE NODISCARDFILE ' ||
     'FIELDS REJECT ROWS WITH ALL NULL FIELDS (ROW_DATA POSITION(1:4000) char)) LOCATION (' || l_quote ||
     'temp.txt' || l_quote || ')) REJECT LIMIT UNLIMITED'

例如，以B代表空白，我的文件具有:

For example, with B representing a blank space, my file has:

Line1sometextBBBBBBB

我的外部表将其读取为:

My external table reads that as just:

Line1sometext

我也希望文件也加载空白.如何停止删除尾随空格?

I want the file to be loaded with blanks too. How can I stop it removing the trailing whitespace?

当前问题是:

如果文件具有:

"这是带有

"this is a test value with

"

 ^   this new line is also a part of the row_data.

推荐答案

添加 a trim_spec 子句，特别是此处的NOTRIM，以更改默认的字段修剪行为:

Add a trim_spec clause, specifically here NOTRIM, to change the default field trimming behaviour:

... FIELDS REJECT ROWS WITH ALL NULL FIELDS (ROW_DATA POSITION(1:4000) char NOTRIM)) LOCATION (...
                                                                            ^^^^^^

快速演示:

create table t42_ext (
  row_data varchar2(4000)
)
organization external
(
  type oracle_loader default directory mydir access parameters
  (
    records delimited by newline nobadfile nodiscardfile
    fields reject rows with all null fields
    (
      row_data position(1:4000) char notrim
    )
  )
  location ('temp.txt')
)
reject limit unlimited;

Table T42_EXT created.

select '<'|| row_data ||'>' from t42_ext;

'<'||ROW_DATA||'>'                                                             
--------------------------------------------------------------------------------
<Line1sometext       >                                                          

这篇关于外部表从文件读取时截断尾随空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！