how to increase sqlplus column output length?

Question

I have some queries to find out the ddl of some objects from a schema. I am getting the result columns are truncated in middle of the queries.

How can I increase the with of the column?

I tried with

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

But, still getting the same result.

Any help indeed.

Thanks

Answer 1

Try this

COLUMN col_name FORMAT A24

where 24 is you width.

Answer 2

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.

Answer 3

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;

Answer 4

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.

Answer 5

On Windows you may try this:

• right-click in the sqlplus window
• select properties ->layout
• increase screen buffer size width to 1000

Answer 6

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?