Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read.

What I'd rather like is either a horizontal scroll bar to appear or somehow send the output to less

I run following statements in SQLPlus -

SET LONG 50000;
SPOOL output.txt
SELECT * FROM big_table;

Then in bash I run -

less output.txt

The output still appears wrapped and unreadable.

share|improve this question
I'd also like the columns to auto-expand instead of me having to set the column size for each individually. – Kshitiz Sharma Nov 29 '13 at 3:51

3 Answers 3

up vote 14 down vote accepted

It is not enough to force sqlplus not to wrap lines. It is also necessary to tell the viewer that you use to view the spool file not tow wrap lines. If your viewer is lessthen -S the option you have to use according to . On Unix/Linux you can use head -1 output.txt to get the first line of a file and so check if this is as expected or you can use od -c output.txt|head to see where the line breaks are actually placed in your output file.

If you display LONG columns that and their values contain line breaks then multiple lines will be printed for these column values and you cannot overrule this by sqlplus settings.

The following sqlplus commands maybe usefull:

  • SET LINESIZE linesize the length of the line. In most cases the maximum value for linesize is 32767. You can find out your maximum value if you set LINESIZE to an invalid value and check the error message so SET LINESIZE 0 may give SP2-0267: linesize option 0 out of range (1 through 32767) (1)
  • SET TRIMSPOOL ON otherwise every line in the spoolfile is filled up with blanks until the linesize is reached.
  • SET TRIMOUT ON otherwise every line in the output is filled up with blanks until the linesize is reached.
  • SET WRAP OFF Truncates the line if its is longer then LINESIZE. This should not happen if linesize is large enough.
  • SET TERMOUT OFF suppresses the printing of the results to the output. The lines are still written to the spool file. This may accelerate the exectution time of a statement a lot.
  • SET PAGESIZE 0 to set an infinite pagesize and avoid headings , titles and so on There are some other SETparamters concering output (NUMWIDTH, NUMFORMAT, LONG, COLSEP) and performance (ARRAYSIZE,LONGCHUNKSIZE)

You have to use the COLUMNcommand to format individual columns.

If you want the display size not be fix but it should be equal to the size of the actual value of a columns in a row then the only way I know is that you change the select clause of your statement to get the desired result and use the string concatenation operator || , e.g.

select emp_id||' '||first_name||' '||last_name
from emp;

A full description of all varaible can be found in SQL*Plus User's Guide and Reference.

If you want to reuse some settings (or COLUMN definitions) you can store them in a file and run this file when you need them again. You even can run this file automatically if you start sqlplus.

(1) "How to Find the LINESIZE Maximum Value (It is System Dependent) (Doc ID 1547262.1)"

share|improve this answer
LONGCHUNKSIZE is not just about performance, it appears to be needed to avoid undesirable line-wrapping in addition to LINESIZE, just experimented this with unusable output from DBMS_METADATA.get_ddl() – Daniel Vérité Jan 7 at 13:36

You need to set the below:

share|improve this answer

you can set like this


share|improve this answer

protected by Paul White Aug 5 at 7:51

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.