使用sqlplus检索大型Clob数据 [英] Retrieve large clob data using sqlplus

问题描述

如何使用sqlplus到stdout从表中完全检索大型Clob数据?有一种使用特定于语言的数据库API完全获取它的方法.但是，当我尝试使用纯sqlplus来获取它时，我遇到了一些问题，例如

• 输出缓冲区太小(最多4000个)
• 字符字符串缓冲区太小

由于oracle clob字段可以包含4GB(最大)数据，因此，有没有使用sqlplus获取完整数据块的正确方法?我可以将其下载为文件吗?

我希望这个问题很清楚.我更愿意在不向数据库注入PL/SQL过程的情况下做到这一点.

解决方案

1)第一个表和Clob.

 创建表large_clob(clob);插入large_clob值(dbms_xmlgen.getXml('从dba_objects中选择*)); 

2)在sqlplus中运行代码

  set linessize 32767 long 2000000000 longchunksize 32767 PAGESIZE 0 FEEDBACK OFF ECHO OFF TERMOUT OFF假脱机output_file.txt从large_clob中选择一个；假脱机 

所有变量的描述是此处

• long 2000000000 -指定要检索的CLOB字节数.(最大2GB)
• linesize 线的大小(最大32k).线的大小.如果行超出大小，则该行将被换行到下一行
• longchunksize 32k -块将以块的形式检索，块的大小为32k
• PAGESIZE 0 -违约结果页fomrationg
• FEEDBACK，ECHO，TERMOUT -禁用所有这些.
• 假脱机将输出重定向到output_file.txt

How to completely retrieve large clob data from a table using sqlplus to the stdout? There is a way to get it completely using language specific DB APIs. But when I try to get it using purely sqlplus, I've faced several problems such as,

• Output buffer is too small (4000 is max)
• Character string buffer too small

Since oracle clob fields can contain 4GB (max) of data, is there any correct way to get the complete data chunk using sqlplus? Can I download it as a file?

I hope that the question is clear. I prefer if I can do it without injecting PL/SQL procedures to the database.

解决方案

1) First table and clob.

create table large_clob(a clob);
insert into large_clob values( dbms_xmlgen.getXml('select * from dba_objects'));

2) Run code in sqlplus

set linesize 32767 long 2000000000 longchunksize 32767 PAGESIZE 0 FEEDBACK OFF ECHO OFF TERMOUT OFF
Spool output_file.txt
  select a from large_clob;
spool off

Description of all variables is here

• long 2000000000 - specifies how many bytes of CLOB to retrieve. (2gb is max)
• linesize size of line (32k is max). size of line. If line exceeds the size , the line will be wrapped to next row
• longchunksize 32k - clob will be retrieved in chunks, where the chunk size is 32k
• PAGESIZE 0 - disbale result page fomrationg
• FEEDBACK,ECHO,TERMOUT - disable all of this.
• Spool redirect output to output_file.txt

这篇关于使用sqlplus检索大型Clob数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！