在没有单独的列格式的情况下，防止列名的sqlplus截断 [英] Preventing sqlplus truncation of column names, without individual column formatting
问题描述
默认情况下，sqlplus将列名截断为基础数据类型的长度.数据库中的许多列名都以表名作为前缀，因此在被截断时看起来是相同的.
By default sqlplus truncates column names to the length of the underlying data type. Many of the column names in our database are prefixed by the table name, and therefore look identical when truncated.
我需要在锁定的生产环境中指定对远程DBA的select *查询，然后将假脱机的结果拖回以进行诊断.列太多，无法指定单个列的格式. sqlplus是否提供任何选项来统一消除列名截断?
I need to specify select * queries to remote DBAs in a locked down production environment, and drag back spooled results for diagnosis. There are too many columns to specify individual column formatting. Does sqlplus offer any option to uniformly defeat column name truncation?
(我正在使用SET MARKUP HTML ON，尽管我可以使用其他方式，例如csv等，只要它能产生未缩写的输出即可.)
(I am using SET MARKUP HTML ON, though I could use some other modality, csv, etc. as long as it yields unabbreviated output.)
推荐答案
提议的解决方案都无法显示原始列名，因此我不确定为什么人们会投票支持他们……我确实有一个"hack"，它适用于原始请求，但我真的不喜欢它.也就是说，您实际上在查询中为每个列追加或添加了字符串前缀，因此对于列标题而言，它们总是足够长.如果您使用的是HTML模式(如发布者一样)，则多余的白色空格几乎不会对您造成任何危害……这当然会减慢您的查询速度...
None of the proposed solutions work to show the original column names, so I'm not sure why people are voting them up... I do have a "hack" that works for the original request, but I really don't like it... That is you actually append or prefix a string onto the query for each column so they are always long enough for the column heading. If you are in an HTML mode, as the poster is, there is little harm by a bit of extra white spacing... It will of course slow down your query abit...
例如
SET ECHO OFF
SET PAGESIZE 32766
SET LINESIZE 32766
SET NUMW 20
SET VERIFY OFF
SET TERM OFF
SET UNDERLINE OFF
SET MARKUP HTML ON
SET PREFORMAT ON
SET WORD_WRAP ON
SET WRAP ON
SET ENTMAP ON
spool '/tmp/Example.html'
select
(s.ID||' ') AS ID,
(s.ORDER_ID||' ') AS ORDER_ID,
(s.ORDER_NUMBER||' ') AS ORDER_NUMBER,
(s.CONTRACT_ID||' ') AS CONTRACT_ID,
(s.CONTRACT_NUMBER||' ') AS CONTRACT_NUMBER,
(s.CONTRACT_START_DATE||' ') AS CONTRACT_START_DATE,
(s.CONTRACT_END_DATE||' ') AS CONTRACT_END_DATE,
(s.CURRENCY_ISO_CODE||' ') AS CURRENCY_ISO_CODE,
from Example s
order by s.order_number, s.contract_number;
spool off;
当然，您可以编写一个存储过程来做一些更好的事情，但是对于这种简单的情况来说，确实显得有些矫kill过正.
Of course you could write a stored procedure to do something better, but really it seems like overkill for this simple scenario.
这仍然不符合原始张贴者的要求.因为它需要手动在列上列出，而不使用select *.但是，至少当您愿意详细说明这些字段时，它才是可行的解决方案.
This still does not meet the original posters request either. In that it requires manually listing on the columns and not using select *. But at least it is solution that works when you are willing to detail out the fields.
但是，由于在HTML中使用太长的字段确实没有问题，因此有一种相当简单的方法来修复Chris的解决方案以使其在本示例中正常工作.那只是选择一个oracle允许的最大值.遗憾的是，除非您为每种数据类型显式添加格式，否则这仍然无法真正适用于每个表的每个字段.该解决方案也不适用于联接，因为不同的表可以使用相同的列名，但可以使用不同的数据类型.
However, since there really is no problem having too long of fields in HTML, there is an rather simple way to fix Chris's solution to work it this example. That is just pick a use the maximum value oracle will allow. Sadly this still won't really work for EVERY field of every table, unless you explicitly add formatting for every data type. This solution also won't work for joins, since different tables can use the same column name but a different datatype.
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 32766
SET LINESIZE 32766
SET MARKUP HTML OFF
SET HEADING OFF
spool /tmp/columns_EXAMPLE.sql
select 'column ' || column_name || ' format A32766'
from all_tab_cols
where data_type = 'VARCHAR2' and table_name = 'EXAMPLE'
/
spool off
SET HEADING ON
SET NUMW 40
SET VERIFY OFF
SET TERM OFF
SET UNDERLINE OFF
SET MARKUP HTML ON
SET PREFORMAT ON
SET WORD_WRAP ON
SET WRAP ON
SET ENTMAP ON
@/tmp/columns_EXAMPLE.sql
spool '/tmp/Example.html'
select *
from Example s
order by s.order_number, s.contract_number;
spool off;
这篇关于在没有单独的列格式的情况下，防止列名的sqlplus截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
