how to increase sqlplus column output length?
SqlOracleSqlplusColumn WidthSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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.
Solution 3 - Sql
Try this
> COLUMN col_name FORMAT A24
where 24 is you width.
Solution 4 - Sql
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
Solution 5 - Sql
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;
Solution 6 - Sql
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.
Solution 7 - Sql
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?
Solution 8 - Sql
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;
Solution 9 - Sql
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.
Solution 10 - Sql
On Windows you may try this:
- right-click in the sqlplus window
- select properties ->layout
- increase screen buffer size width to 1000