How to make sqlplus output appear in one line?
I have a table with 100 columns. When selecting data in
SQL Plusthe 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
I run following statements in SQLPlus -
SET LINESIZE 32000; SET PAGESIZE 40000; SET LONG 50000; SPOOL output.txt SELECT * FROM big_table;
bashI run -
The output still appears wrapped and unreadable.
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 to wrap lines. If your viewer is
-Sthe option you have to use according to https://superuser.com/questions/272818/how-to-turn-off-word-wrap-in-less . On Unix/Linux you can use
head -1 output.txtto get the first line of a file and so check if this is as expected or you can use
od -c output.txt|headto see where the line breaks are actually placed in your output file.
If you display LONG columns and their values contain line breaks then multiple lines will be printed for these column values and you cannot overrule this with sqlplus settings.
The following sqlplus commands maybe usefull:
SET LINESIZE linesizethe length of the line. In most cases the maximum value for
linesizeis 32767. You can find out your maximum value if you set LINESIZE to an invalid value and check the error message so
SET LINESIZE 0may give
SP2-0267: linesize option 0 out of range (1 through 32767)(1)
SET TRIMSPOOL ONotherwise every line in the spoolfile is filled up with blanks until the linesize is reached.
SET TRIMOUT ONotherwise every line in the output is filled up with blanks until the linesize is reached.
SET WRAP OFFTruncates the line if its is longer then LINESIZE. This should not happen if linesize is large enough.
SET TERMOUT OFFsuppresses 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 0to set an infinite pagesize and avoid headings , titles and so on.
- There are some other
SETparameters concerning output (NUMWIDTH, NUMFORMAT, LONG, COLSEP) and performance (ARRAYSIZE, LONGCHUNKSIZE).
You have to use the
COLUMNcommand to format individual columns.
column name format a30will format the column
namein the output to a maximum length of 30 characters.
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
select emp_id||' '||first_name||' '||last_name from emp;
A full description of all variable 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)"
`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()`
You should use `SET PAGESIZE 1000` instead of `0` otherwise columns headers will not print when issuing `SELECT" commands. Reference.