SQL 如何增加sqlplus列输出长度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7384789/
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
how to increase sqlplus column output length?
提问by Mohamed Saligh
I have some queries to find out the ddl of some objects from a schema. The result columns I am getting are truncated in the middle of the queries.
我有一些查询要从模式中找出某些对象的 ddl。我得到的结果列在查询中间被截断。
How can I increase the width of the column?
如何增加列的宽度？
I tried with
我试过
SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 50000;
set pagesize 50000;
set long 50000;
But I'm still getting the same result.
但我仍然得到相同的结果。
回答by oliver-clare
I've just used the following command:
我刚刚使用了以下命令：
SET LIN[ESIZE] 200
(from http://ss64.com/ora/syntax-sqlplus-set.html).
（来自http://ss64.com/ora/syntax-sqlplus-set.html）。
EDIT: For clarity, valid commands are SET LIN 200or SET LINESIZE 200.
编辑：为清楚起见，有效命令是SET LIN 200或SET LINESIZE 200。
This works fine, but you have to ensure your console window is wide enough. If you're using SQL Plus direct from MS Windows Command Prompt, the console window will automatically wrap the line at whatever the "Screen Buffer Size Width" property is set to, regardless of any SQL Plus LINESIZEspecification.
这工作正常，但您必须确保您的控制台窗口足够宽。如果您直接从 MS Windows 命令提示符使用 SQL Plus，控制台窗口将自动在“屏幕缓冲区大小宽度”属性设置为的任何位置换行，而不管任何 SQL PlusLINESIZE规范如何。
As suggested by @simplyharsh, you can also configure individual columns to display set widths, using COLUMN col_name FORMAT Ax(where xis the desired length, in characters) - this is useful if you have one or two extra large columns and you just wish to show a summary of their values in the console screen.
正如@simplyharsh 所建议的那样，您还可以使用COLUMN col_name FORMAT Ax（其中x是所需的长度，以字符为单位）配置单个列以显示设置的宽度- 如果您有一个或两个超大列并且您只想显示摘要，这将非常有用它们在控制台屏幕中的值。
回答by Martin Irigaray
This configuration is working for me:
此配置对我有用：
set termout off
set verify off
set trimspool on
set linesize 200
set longchunksize 200000
set long 200000
set pages 0
column txt format a120
The columnformat definition with the linesizeoption helped to avoid the truncation at 80 chars.
column带有linesize选项的格式定义有助于避免在 80 个字符处被截断。
回答by simplyharsh
Try this
尝试这个
COLUMN col_name FORMAT A24
COLUMN col_name FORMAT A24
where 24 is you width.
其中 24 是您的宽度。
回答by M.R
On Linux try these:
在 Linux 上试试这些：
set wrap off
set trimout ON
set trimspool on
set serveroutput on
set pagesize 0
set long 20000000
set longchunksize 20000000
set linesize 4000
回答by Marcel
Additionally to setting the LINESIZE, as LordScree suggested, you could also specify to output to a file, to overcome the problem with the console width. Here's how I do it:
除了设置 LINESIZE 之外，正如 LordScree 所建议的，您还可以指定输出到文件，以克服控制台宽度的问题。这是我的方法：
set linesize 15000;
spool myoutput.txt;
SELECT
...
spool off;
回答by William Fraser
Actually, even that didn't work for me. When I executed "select dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual;" I again got only the first three lines, but this time each line was padded out to 15,000 characters. I was able to work around this with:
事实上，即使那样对我也不起作用。当我执行“select dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual;”时 我再次只得到前三行，但这次每行被填充到 15,000 个字符。我能够解决这个问题：
select substr(dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA'),80) from dual;
select substr(dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA'),160) from dual;
select substr(dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA'),240) from dual;
It sure seemed like there ought to be an easier way, but I couldn't seem to find it.
看起来确实应该有更简单的方法，但我似乎找不到它。
回答by April Mc
What I use:
我使用的是：
set long 50000
set linesize 130
col x format a80 word_wrapped;
select dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA') x from dual;
Or am I missing something?
或者我错过了什么？
回答by ArtOfWarfare
None of these suggestions were working for me. I finally found something else I could do - dbms_output.put_line. For example:
这些建议都不适合我。我终于找到了我可以做的其他事情 - dbms_output.put_line。例如：
SET SERVEROUTPUT ON
begin
for i in (select dbms_metadata.get_ddl('INDEX', index_name, owner) as ddl from all_indexes where owner = 'MYUSER') loop
dbms_output.put_line(i.ddl);
end loop;
end;
/
Boom. It printed out everything I wanted - no truncating or anything like that. And that works straight in sqlplus- no need to put it in a separate file or anything.
繁荣。它打印出我想要的一切——没有截断或类似的东西。这直接起作用sqlplus- 无需将其放入单独的文件或任何内容中。
回答by Mitch1077487
This worked like a charm for me with a CLOB column:
这对我来说就像一个 CLOB 列的魅力：
set long 20000000
set linesize 32767
column YOUR_COLUMN_NAME format a32767
select YOUR_COLUMN_NAME from YOUR_TABLE;
回答by prasanna
On Windows you may try this:
在 Windows 上你可以试试这个：
- right-click in the sqlplus window
- select properties ->layout
- increase screen buffer size width to 1000
- 在 sqlplus 窗口中右键单击
- 选择属性->布局
- 将屏幕缓冲区大小宽度增加到 1000
