oracle 防止 sqlplus 截断列名，而无需单独的列格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/351657/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Preventing sqlplus truncation of column names, without individual column formatting
提问by Chris Noe
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.
默认情况下，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?
我需要在锁定的生产环境中为远程 DBA 指定 select * 查询，并拖回假脱机结果以进行诊断。列太多，无法指定单独的列格式。sqlplus 是否提供任何选项来统一击败列名截断？
(I am using SET MARKUP HTML ON, though I could use some other modality, csv, etc. as long as it yields unabbreviated output.)
（我正在使用 SET MARKUP HTML ON，但我可以使用其他一些模态、csv 等，只要它产生未缩写的输出。）
回答by user6856
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...
建议的解决方案都不能显示原始列名称，所以我不确定人们为什么要投票……我确实有一个适用于原始请求的“黑客”，但我真的不喜欢它...也就是说，您实际上在查询的每一列中附加或前缀一个字符串，以便它们对于列标题来说总是足够长。如果您使用的是 HTML 模式，就像海报一样，那么一点额外的空白几乎没有什么害处……它当然会减慢您的查询速度……
e.g.
例如
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;
Of course you could write a stored procedure to do something better, but really it seems like overkill for this simple scenario.
当然，您可以编写一个存储过程来做更好的事情，但对于这个简单的场景来说，这似乎有点矫枉过正。
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.
这仍然不符合原始海报的要求。因为它需要在列上手动列出而不是使用 select *。但至少当您愿意详细说明字段时，它是有效的解决方案。
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.
但是，由于 HTML 中的字段太长确实没有问题，因此有一种相当简单的方法可以修复 Chris 的解决方案以在此示例中工作。那只是选择一个使用 Oracle 允许的最大值。遗憾的是，这仍然不适用于每个表的每个字段，除非您为每种数据类型明确添加格式。此解决方案也不适用于连接，因为不同的表可以使用相同的列名但使用不同的数据类型。
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;
回答by IK.
One thing you can try is to dynamically generate "column x format a20" commands. Something like the following:
您可以尝试的一件事是动态生成“列 x 格式 a20”命令。类似于以下内容：
set termout off
set feedback off
spool t1.sql
select 'column ' || column_name || ' format a' || data_length
from all_tab_cols
where table_name='YOUR_TABLE'
/
spool off
@t1.sql
set pagesize 24
set heading on
spool result.txt
select *
from YOUR_TABLE;
and rownum < 30;
spool off
Note that this sample will only work with VARCHAR2. You would need to add decode for example to change the generated "column" command for DATEs or NUMBERs.
请注意，此示例仅适用于 VARCHAR2。例如，您需要添加 decode 以更改为 DATE 或 NUMBER 生成的“列”命令。
UPDATE: It turns out the original SQL doesn't really change the behaviour of the SQL*Plus. The only thing I could think of is to rename the field names to one character values A, B, C, etc.. in the following way:
更新：原来的 SQL 并没有真正改变 SQL*Plus 的行为。我唯一能想到的就是将字段名称重命名为一个字符值 A、B、C 等，如下所示：
select 'column ' || column_name ||
' heading "' ||
chr(ascii('A') - 1 + column_id) ||
'"'
from all_tab_cols
where table_name='YOUR_TAB_NAME'
It will generate the output similar to:
它将生成类似于以下内容的输出：
column DEPT_NO heading "A"
column NAME heading "B"
column SUPERVIS_ID heading "C"
column ADD_DATE heading "D"
column REPORT_TYPE heading "E"
回答by srmeyer
This should provide some reasonable formatting. You are, of course, free to substitute your own preferences for the maximum width of char columns, and what to do with LONG, RAW and LOB columns.
这应该提供一些合理的格式。当然，您可以自由地将自己的偏好替换为 char 列的最大宽度，以及如何处理 LONG、RAW 和 LOB 列。
SELECT 'COLUMN ' || column_name || ' FORMAT ' ||
CASE
WHEN data_type = 'DATE' THEN
'A9'
WHEN data_type LIKE '%CHAR%' THEN
'A' ||
TRIM(TO_CHAR(LEAST(GREATEST(LENGTH(column_name),
data_length), 40))) ||
CASE
WHEN data_length > 40 THEN
' TRUNC'
ELSE
NULL
END
WHEN data_type = 'NUMBER' THEN
LPAD('0', GREATEST(LENGTH(column_name),
NVL(data_precision, data_length)), '9') ||
DECODE(data_scale, 0, NULL, NULL, NULL, '.' ||
LPAD('0', data_scale, '0'))
WHEN data_type IN ('RAW', 'LONG') THEN
'A1 NOPRINT'
WHEN data_type LIKE '%LOB' THEN
'A1 NOPRINT'
ELSE
'A' || TRIM(TO_CHAR(GREATEST(LENGTH(column_name), data_length)))
END AS format_cols
FROM dba_tab_columns
WHERE owner = 'SYS'
AND table_name = 'DBA_TAB_COLUMNS';
回答by Justin Cave
It's a bit of a hack if you don't need or want XML formatting, but you should be able to use the DBMS_XMLGEN package. This script should give you an XML file for an arbitrary query with the full column name as the tag name.
如果您不需要或不想要 XML 格式，这有点麻烦，但您应该能够使用DBMS_XMLGEN 包。此脚本应该为您提供一个 XML 文件，用于以完整的列名称作为标记名称的任意查询。
VARIABLE resultXML clob;
SET LONG 100000; -- Set to the maximum size of the XML you want to display (in bytes)
SET PAGESIZE 0;
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
BEGIN
qryCtx := dbms_xmlgen.newContext('SELECT * from scott.emp');
-- now get the result
:resultXML := DBMS_XMLGEN.getXML(qryCtx);
--close context
DBMS_XMLGEN.closeContext(qryCtx);
END;
/
print resultXML
回答by m0j0
I don't think sqlplus offers the functionality you are requesting. You might be able to automate the formatting, using some sort of scripting language such as Perl or Python. In other words, query the ALL_TAB_COLSview for the schema and table, and then create the script dynamically with a format column attribute. This will only work, of course, if you have permission to query the ALL_TAB_COLS view (or some other equivalent).
我认为 sqlplus 没有提供您要求的功能。您或许能够使用某种脚本语言（例如 Perl 或 Python）自动设置格式。换句话说，查询ALL_TAB_COLS模式和表的视图，然后使用格式列属性动态创建脚本。当然，这只有在您有权查询 ALL_TAB_COLS 视图（或其他等效项）时才有效。
This is a quick proof-of-concept I threw together:
这是我拼凑起来的一个快速概念验证：
#!/usr/bin/python
import sys
import cx_Oracle
response=raw_input("Enter schema.table_name: ")
(schema, table) = response.split('.')
schema = schema.upper()
table = table.upper()
sqlstr = """select column_name,
data_type,
data_length
from all_tab_cols
where owner = '%s'
and table_name = '%s'""" % ( schema, table )
## open a connection to databases...
try:
oracle = cx_Oracle.Connection( oracleLogin )
oracle_cursor = oracle.cursor()
except cx_Oracle.DatabaseError, exc:
print "Cannot connect to Oracle database as", oracleLogin
print "Oracle Error %d: %s" % ( exc.args[0].code, exc.args[0].message )
sys.exit(1)
try:
oracle_cursor.execute( sqlstr )
# fetch resultset from cursor
for column_name, data_type, data_length in oracle_cursor.fetchmany(256):
data_length = data_length + 0
if data_length < len(column_name):
if data_type == "CHAR" or data_type == "VARCHAR2":
print "column %s format a%d" % ( column_name.upper(), len(column_name) )
else:
print "-- Handle %s, %s, %d" % (column_name, data_type, data_length)
except cx_Oracle.DatabaseError, e:
print "[Oracle Error %d: %s]: %s" % (e.args[0].code, e.args[0].message, sqlstr)
sys.exit(1)
try:
oracle_cursor.close()
oracle.close()
except cx_Oracle.DatabaseError, exc:
print "Warning: Oracle Error %d: %s" % ( exc.args[0].code, exc.args[0].message )
print "select *"
print "from %s.%s" % ( schema, table )
回答by talek
I had the same problem trying to implement this feature in VoraX. In the next version I have in mind the following solution:
我在尝试在VoraX 中实现此功能时遇到了同样的问题。在下一个版本中，我想到了以下解决方案：
set feedback off
set serveroutput on
declare
l_c number;
l_col_cnt number;
l_rec_tab DBMS_SQL.DESC_TAB2;
l_col_metadata DBMS_SQL.DESC_REC2;
l_col_num number;
begin
l_c := dbms_sql.open_cursor;
dbms_sql.parse(l_c, '<YOUR QUERY HERE>', DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(l_c, l_col_cnt, l_rec_tab);
for colidx in l_rec_tab.first .. l_rec_tab.last loop
l_col_metadata := l_rec_tab(colidx);
dbms_output.put_line('column ' || l_col_metadata.col_name || ' heading ' || l_col_metadata.col_name);
end loop;
DBMS_SQL.CLOSE_CURSOR(l_c);
end;
Instead of tweaking column sizes, formatting and stuff just enforce the column heading with the column name you want. I think the same approach would work also with DBA_TAB_COLUMNS solution but I prefer the DBMS_SQL one as it also considers the aliases and it gets only the columns you query.
无需调整列大小、格式和内容，只需使用您想要的列名强制列标题。我认为同样的方法也适用于 DBA_TAB_COLUMNS 解决方案，但我更喜欢 DBMS_SQL 解决方案，因为它也考虑别名并且它只获取您查询的列。
EDIT:Using just "column heading" doesn't work. It's still needed to use "column format" statements. So, please ignore my previous answer.
编辑：仅使用“列标题”不起作用。仍然需要使用“列格式”语句。所以，请忽略我之前的回答。
