从插入到外部表中删除EOL定界符-oracle [英] removing EOL delimiter from inserting into external table -oracle

查看：138
本文介绍了从插入到外部表中删除EOL定界符-oracle的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

按照Alex的建议，我在外部表的 rowdata 列中加入了 notrim (这是

I have included notrim for rowdata column in external table as suggesterd by Alex (This is a continuation of this question,),

但是现在行尾字符也附加在rowdata列上，我的意思是，行尾(CR-LF)也连接在rowdata末尾.

But now End of Line character is also appending at the rowdata column, I mean , End of line (CR-LF) is also joins at the end of rowdata.

我不想使用substr()或translate()，因为文件大小约为1GB，

I don't want to use substr() or translate() , since file size is around 1GB,

我的外部表创建过程:

'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'

我是否可以添加其他参数，以删除行尾字符.谢谢.

Is there any other paramenter I can add , to remove the End-of-line character. Thanks.

我的文件:

某些第一行的末尾有空格
第二行末尾有空格

Some first line with spaces at end
Some second line with spaces at end

我的Ext表:

Some first line with spaces at end    <EOL>
Some second line with spaces at end   <EOL>

更清楚地说，我将在java中进行解释(当我将列值分配给string时，如下所示)，

to be more clear , I will explain in java (when I assign column values to string , it is something like below),

没有notrim:

rowdata[1]="Some first line with spaces at end";
rowdata[2]="Some second line with spaces at end";

带有notrim:

rowdata[1]="Some first line with spaces at end    \n";
rowdata[2]="Some second line with spaces at end   \n";

我希望它是什么:

rowdata[1]="Some first line with spaces at end    ";
rowdata[2]="Some second line with spaces at end   ";

分隔符也是行数据的一部分，因为未指定修剪.

行尾:CRLF

Line-Endings : CRLF

平台:

Oracle Database 12c企业版12.1.0.1.0版-64位

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit

生产PL/SQL版本12.1.0.1.0-生产

Production PL/SQL Release 12.1.0.1.0 - Production

Solaris 的"CORE 12.1.0.1.0 Production" TNS:版本12.1.0.1.0-

"CORE 12.1.0.1.0 Production" TNS for Solaris: Version 12.1.0.1.0 -

生产NLSRTL版本12.1.0.1.0-生产

Production NLSRTL Version 12.1.0.1.0 - Production

从EXT_TABLE WHERE ROWNUM = 1;

Typ = 1 Len = 616 CharacterSet = AL32UTF8: 41,30,30,30,30,30,30,30,30,30,30,30,31,30,30,30,30,37,36,36,36,36,44,30,30,30,30， 31,32,35,30,38,31,36,32,35,30,38,31,36,31,33,34,37,30,39,44,42,20,41,30,36， 31,30,30,30,30,30,30,30,30,30,30,30,30,30,32,30,30,4d，59,52,20,32,5a，20,30,31， 36,30,30,30,31,32,31,32,33,34,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30， 30,52,49,42,46,50,58,30,30,30,31,30,30,30,30,30,30,30,30,30,31,30,36,32,38,30， 31,30,32,30,30,47,20,20,20,20,53,20,20,30,30,30,30,30,30,30,30,30,30,30,30,20， 20,20,20,20,20,20,20,4e，39,32,37,32,20,20,20,20,20,20,30,30,30,30,30,30,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,30,30,39,39,38,54,45,53,54,52,52,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53， 54,4f，50,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,54,52,41,4e， 53,49,44,20,20,20,20,20,2 0,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,52,52,52,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53， 54,4f，50,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,54,52,41,4e， 53,49,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20， 20,20,20,20,20,20,20，d

Typ=1 Len=616 CharacterSet=AL32UTF8: 41,30,30,30,30,30,30,30,30,30,30,31,30,30,30,30,37,36,36,36,44,30,30,30,30,31,32,35,30,38,31,36,32,35,30,38,31,36,31,33,34,37,30,39,44,42,20,41,30,36,31,30,30,30,30,30,30,30,30,30,30,30,30,32,30,30,4d,59,52,20,32,5a,20,30,31,36,30,30,30,31,32,31,32,33,34,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,52,49,42,46,50,58,30,30,30,31,30,30,30,30,30,30,30,30,31,30,36,32,38,30,31,30,32,30,30,47,20,20,20,20,53,20,20,30,30,30,30,30,30,30,30,30,30,30,20,20,20,20,20,20,20,4e,39,32,37,32,20,20,20,20,20,20,30,30,30,30,30,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,30,30,39,39,38,54,45,53,54,52,52,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,4f,50,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,54,52,41,4e,53,49,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,52,52,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,4f,50,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,54,52,41,4e,53,49,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,d

Len应该是615

推荐答案

您的文件行结尾是CRLF(建议文件是在Windows中创建的吗?)，但是您的数据库正在Solaris上运行.作为文档说:

Your file line endings are CRLF (suggesting the file is created in Windows?), but your database is running on Solaris. As the documentation says:

如果指定了DELIMITED BY NEWLINE，则使用的实际值是特定于平台的.在UNIX平台上，假定NEWLINE为"\ n".在Windows操作系统上，假定NEWLINE为"\ r \ n".

If DELIMITED BY NEWLINE is specified, then the actual value used is platform-specific. On UNIX platforms, NEWLINE is assumed to be "\n". On Windows operating systems, NEWLINE is assumed to be "\r\n".

由于您的数据库平台是Unix，因此仅使用LF(\n)作为记录定界符.您可以更改文件中的定界符，也可以更改terminated by子句以查找Windows行尾:

As your database platform is Unix it's only using the LF (\n) as the record delimiter. You can either change the delimiter in your file, or change the terminated by clause to look for the Windows line-ending:

,,,
records delimited by "\r\n" nobadfile ...

如果您可能会获得两种行尾都不能控制的文件，则可以添加

If you might get files with either type of line ending and can't control that, you could add a preprocessor step to strip any that do exist. If you create an executable script file, either in the same directory as the file or (as Oracle recommends) in a different Oracle-accessible directory, say called remove_cr which contains:

/usr/bin/sed -e "s/\\r$//" $1

您可以在外部表定义中添加对该调用的调用，并保留newline终止符:

you can add a call to that in your external table definition, and keep the newline temrinator:

...
records delimited by newline nobadfile nodiscardfile
preprocessor 'remove_cr'
...

请确保您已阅读文档中的安全警告.

Make sure you read the the security warnings in the documentation though.

演示文件为temp.txt且结尾为CRLF的行

Demo with a temp.txt file with CRLF line endings:

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

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

'<'||ROW_DATA||'>'                                                             
--------------------------------------------------------------------------------
<Line1sometext       >                                                          
<Line2sometext       >                                                          
<Line3sometext       >

这篇关于从插入到外部表中删除EOL定界符-oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆