sql * plus根据该列中的值将其截断 [英] sql*plus is truncating the columns names according to the values in that columns

问题描述

我有两个广泛的问题:

Q1.在Oracle SQL Developer中执行代码行并在sqlplus命令中执行相同的代码是否提示相同的事情? (原因是我听说并不是所有的sqlplus命令都可以在SQL Developer中执行.如果对上述问题的回答是是"，那么请几个有用的链接会有所帮助).

Q2.我将sql查询的结果假脱机到.csv文件，但事实是，列名根据该列中值的最大长度而被截断了. 我的代码是:

 set colsep ","
spool C:\Oracle\sample.csv
select * from acct_clas_rule;
spool off;
 

以上代码的输出为(中间列为空值)

  ABCD_,ABCD_,ABC
-----,-----,---
AB   ,     ,WSD
ABCD ,     ,WSD
ABCD ,     ,WSD
SG   ,     ,WSD
KD   ,     ,WSD
WD   ,     ,LKJ
KLHGF,     ,LKO
WSDFG,     ,LOK
WSDF ,     ,LKO
WS   ,     ,GH
 

在上面的输出中，列名已被截断.我希望显示列的全名.有人可以帮忙吗? 我在此链接中看到了问题 ，但我不明白如何应用此处提供的答案，因为没有引用任何具体示例.我对这些东西比较陌生，所以我听不懂.

我的列的原始名称(在上表中从左到右)是: ABCD_DFGT_SDF, ABCD_EDF_GH, ABCD_DFRE

PS-

1.我正在使用Oracle SQL Developer运行sqlplus命令.我想是因为我的命令中有哪几条不起作用(例如set underline，set linesize等).如果是这种情况，请告诉我.我实际上想删除列名称下的那些下划线.

2.还请告知我您的答案适用于Oracle SQL Developer还是sqlplus. 谢谢

解决方案

除了@JChomel的方法外，您还可以做其他几件事-可以在SQL Develoepr或SQL * Plus中使用，而这些建议是特定于SQL Developer.

让我们从基于CTE的虚拟查询开始，以获得类似您的情况的信息:

set colsep ","
with acct_clas_rule (abdc_1, abcd_2, abcd_3) as (
  select cast('AB' as varchar2(5)), cast(null as varchar2(5)), cast('WSD' as varchar2(4)) from dual
  union all select 'ABCD', null, 'WSD' from dual
  -- ...
  union all select 'WS', null, 'GH' from dual
)
select * from acct_clas_rule;

当在SQL Developer中作为脚本运行时(通过文档+箭头图标或F5)，输出为:

ABDC_,ABCD_,ABCD
-----,-----,----
AB   ,     ,WSD 
ABCD ,     ,WSD 
WS   ,     ,GH  

如果将查询更改为包括特定于SQL Developer的格式提示/*csv*/，则将获得所需的输出:

select /*csv*/ * from acct_clas_rule;

"ABDC_1","ABCD_2","ABCD_3"
"AB","","WSD"
"ABCD","","WSD"
"WS","","GH"

，除了所有字符串都用双引号引起来外，这可能并不是您真正想要的. (这取决于您对假脱机文件的处理方式，以及您的任何字符串值是否包含逗号(例如，会使Excel迷惑).

使用SQL Developer的最新版本，您可以得到完全相同的结果，而无需使用sqlformat选项修改查询:

set sqlformat csv
select * from acct_clas_rule;

但是您再次得到双引号.您可以将双引号更改为不同的附件字符，但这可能无济于事.

另一种方法是使用内置的导出工具代替假脱机.如果您将查询作为语句运行(绿色的播放按钮"图标或控制输入)，而不是出现在脚本输出"面板中，则将在其旁边打开一个新的查询结果"面板，将结果显示为网格. /p>

如果右键单击结果，将显示一个上下文菜单，然后从中选择导出..."，将为您提供一个向导，以您选择的格式导出结果，包括CSV:

您可以将左和右括号用双引号引起来，以得到与上述选项相同的结果，不同之处在于空值使用单词"null"而不是空字符串:

"ABDC_1","ABCD_2","ABCD_3"
"AB",null,"WSD"
"ABCD",null,"WSD"
"WS",null,"GH

或者您可以更改它们，或通过选择无"将其删除，这将为您提供:

ABDC_1,ABCD_2,ABCD_3
AB,null,WSD
ABCD,null,WSD
WS,null,G

@thatjeffsmith评论了如何为/*csv*/方法更改空值替换文本.对于导出，在17.2.0中，使用"null"一词似乎是一个错误.在17.3.1中没有出现，而是看到:

ABDC_1,ABCD_2,ABCD_3
AB,,WSD
ABCD,,WSD
WS,,GH

或封闭的空字符串("")(如果保留机箱设置).

I have two broad questions:

Q1. Are executing the lines of code in Oracle SQL Developer and executing the same code in sqlplus command prompt same things? (reason I ask this that I heard that not all the sqlplus commands are executable in SQL Developer. If answer to above question is yes then please then few useful links will help).

Q2. I am spooling the results of a sql query to a .csv file, but the thing is that columns names are truncated according the maximum length of the values in that column. My code is:

set colsep ","
spool C:\Oracle\sample.csv
select * from acct_clas_rule;
spool off;

Output of above code is (middle column is having null values)

 ABCD_,ABCD_,ABC
-----,-----,---
AB   ,     ,WSD
ABCD ,     ,WSD
ABCD ,     ,WSD
SG   ,     ,WSD
KD   ,     ,WSD
WD   ,     ,LKJ
KLHGF,     ,LKO
WSDFG,     ,LOK
WSDF ,     ,LKO
WS   ,     ,GH

In above output, columns names have been truncated. I want full names of the columns to be displayed. Can anyone help? I have seen the question in this link, but I didn't understand how to apply the answers provided there as there was no particular example cited. I am new to these things so I couldn't understand.

Original names of my columns (from left to right in above table) are : ABCD_DFGT_SDF, ABCD_EDF_GH, ABCD_DFRE

PS -

1. I am using Oracle SQL developer to run sqlplus commands. I think because of which few of my commands are not working (like set underline, set linesize etc.).Please let me know if this is the case. I actually want remove those underlines beneath the columns names.

2. Also let me know that whether you answer is applicable to Oracle SQL Developer or sqlplus. Thank You

解决方案

There are a couple of things you can do, in addition to @JChomel's approach - that will work in either SQL Develoepr or SQL*Plus, while these suggestions are specific to SQL Developer.

Let's start with a dummy query based on a CTE to get something like your situation:

set colsep ","
with acct_clas_rule (abdc_1, abcd_2, abcd_3) as (
  select cast('AB' as varchar2(5)), cast(null as varchar2(5)), cast('WSD' as varchar2(4)) from dual
  union all select 'ABCD', null, 'WSD' from dual
  -- ...
  union all select 'WS', null, 'GH' from dual
)
select * from acct_clas_rule;

When run as a script in SQL Developer (from the document+arrow icon, or F5) the output is:

ABDC_,ABCD_,ABCD
-----,-----,----
AB   ,     ,WSD 
ABCD ,     ,WSD 
WS   ,     ,GH  

If you change the query to include the SQL Developer-specific formatting hint /*csv*/ then you get the output you want:

select /*csv*/ * from acct_clas_rule;

"ABDC_1","ABCD_2","ABCD_3"
"AB","","WSD"
"ABCD","","WSD"
"WS","","GH"

except that the strings are all enclosed in double-quotes, which might not be what you really want. (It depends what you're doing with the spooled file and whether any of your string values contain commas, which would confuse Excel for instance).

With more recent versions of SQL Developer you can get exactly the same result without modifying the query using the sqlformat option:

set sqlformat csv
select * from acct_clas_rule;

but again you get the double-quotes. You could change the double-quotes to different enclosure characters, but that probably doesn't help.

A different approach is to use the built-in export tools instead of spool. If you run the query as a statement (green 'play button' icon, or control-enter) then rather than appearing in the Script Output panel, a new Query Result panel will open next to that, showing the results as a grid.

If you right-click on the results you'll get a contextual menu, and choosing Export... from that will give you a wizard to export the results in a format of your choice, including CSV:

You can leave the left and right enclosures as double-quotes to get the same results as the options above, except that null values use the word 'null' instead of an empty string:

"ABDC_1","ABCD_2","ABCD_3"
"AB",null,"WSD"
"ABCD",null,"WSD"
"WS",null,"GH

or you can change them, or remove them by choosing 'none', which gives you:

ABDC_1,ABCD_2,ABCD_3
AB,null,WSD
ABCD,null,WSD
WS,null,G

@thatjeffsmith has commented on how to change the null-replacement text for the /*csv*/ approach. For export it looks like having the word 'null' might have been a bug in 17.2.0; in 17.3.1 that does not appear, and instead you see:

ABDC_1,ABCD_2,ABCD_3
AB,,WSD
ABCD,,WSD
WS,,GH

or enclosed empty strings ("") if you leave the enclosures set.

这篇关于sql * plus根据该列中的值将其截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！