如何增加sqlplus列输出长度？

发布于 2024-12-03 20:22:22 字数 215 浏览 2 评论 0原文

我有一些查询要从架构中找出某些对象的 ddl 。我得到的结果列在查询中间被截断。

如何增加列的宽度？

我尝试过

SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 50000;
set pagesize 50000;
set long 50000;

但仍然得到相同的结果。

原文

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.

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

走野 2024-12-10 20:22:22

我刚刚使用了以下命令：（

SET LIN[ESIZE] 200

来自 http://ss64.com/ora/语法-sqlplus-set.html）。

编辑：为了清楚起见，有效命令是 SET LIN 200 或 SET LINESIZE 200。

这工作正常，但您必须确保控制台窗口足够宽。如果您直接从 MS Windows 命令提示符使用 SQL Plus，则控制台窗口将自动以“屏幕缓冲区大小宽度”属性设置的任何值自动换行，无论任何 SQL Plus LINESIZE 规范。

按照@simplyharsh的建议，您还可以使用COLUMN col_name FORMAT Ax配置各个列以显示设置的宽度（其中x是所需的长度，以字符为单位） - 这是如果您有一两个超大列并且您只想在控制台屏幕中显示其值的摘要，则非常有用。

I've just used the following command:

SET LIN[ESIZE] 200

(from http://ss64.com/ora/syntax-sqlplus-set.html).

EDIT: For clarity, valid commands are SET LIN 200 or 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 LINESIZE specification.

As suggested by @simplyharsh, you can also configure individual columns to display set widths, using COLUMN col_name FORMAT Ax (where x is 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.

回复收藏 0 原文

彼岸花似海 2024-12-10 20:22:22

此配置对我有用：

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

带有 linesize 选项的 column 格式定义有助于避免在 80 个字符处截断。

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 column format definition with the linesize option helped to avoid the truncation at 80 chars.

回复收藏 0 原文

爱已欠费 2024-12-10 20:22:22

试试这个

列列名称格式 A24

其中 24 是您的宽度。

回复收藏 0 原文

盛夏尉蓝 2024-12-10 20:22:22

在 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

On Linux try these:

set wrap off
set trimout ON
set trimspool on
set serveroutput on
set pagesize 0
set long 20000000
set longchunksize 20000000
set linesize 4000

回复收藏 0 原文

心的位置 2024-12-10 20:22:22

除了按照 LordScree 的建议设置 LINESIZE 之外，您还可以指定输出到文件，以克服控制台宽度的问题。我是这样做的：

set linesize 15000;
spool myoutput.txt;
SELECT 
...
spool off;

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:

set linesize 15000;
spool myoutput.txt;
SELECT 
...
spool off;

回复收藏 0 原文

Hello爱情风 2024-12-10 20:22:22

事实上，即使这样对我来说也不起作用。当我执行“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;

看起来确实应该有一种更简单的方法，但我似乎找不到它。

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 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.

回复收藏 0 原文

影子是时光的心 2024-12-10 20:22:22

我用什么：

set long 50000
set linesize 130

col x format a80 word_wrapped;
select dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA') x from dual;

或者我错过了什么？

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?

回复收藏 0 原文

温馨耳语 2024-12-10 20:22:22

这对我来说对 CLOB 专栏很有魅力：

set long 20000000
set linesize 32767
column YOUR_COLUMN_NAME format a32767
select YOUR_COLUMN_NAME from YOUR_TABLE;

This worked like a charm for me with a CLOB column:

set long 20000000
set linesize 32767
column YOUR_COLUMN_NAME format a32767
select YOUR_COLUMN_NAME from YOUR_TABLE;

回复收藏 0 原文

墨落成白 2024-12-10 20:22:22

这些建议都不适合我。我终于找到了我可以做的其他事情 - 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;
/

轰隆隆。它打印出了我想要的所有内容 - 没有截断或类似的东西。这可以直接在 sqlplus 中工作 - 无需将其放在单独的文件或任何内容中。

None of these suggestions were working for me. I finally found something else I could do - dbms_output.put_line. For example:

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.

回复收藏 0 原文