How to avoid split in output in sqlplus

sqlplus

I am running the following query to get list of all PDBs using SQLPLUS and calling each PDB NAME using for loop in shell script.
Unfortunately, few PDB NAMEs are split/broken in the output(for example, P DB1P016, PD B1P032 etc) so the variable is not assigning proper/correct value.
Any idea to fix this?

SQL> select LISTAGG(a.name, ' ') WITHIN GROUP (ORDER BY name) as pdbs from v$pdbs a;

PDBS
-------------------------------------------------------------------------------------------------------------------------------------------------
IOC1P002 PDB$SEED PDB1P001 PDB1P002 PDB1P003 PDB1P004 PDB1P005 PDB1P006 PDB1P007 PDB1P008 PDB1P009 PDB1P010 PDB1P012 PDB1P013 PDB1P014 PDB1P015 P
DB1P016 PDB1P017 PDB1P018 PDB1P019 PDB1P020 PDB1P021 PDB1P022 PDB1P023 PDB1P024 PDB1P025 PDB1P026 PDB1P027 PDB1P028 PDB1P029 PDB1P030 PDB1P031 PD
B1P032 PDB1P033 PDB1P034 PDB1P035 PDB1P036 PDB1P037 PDB1P038 PDB1P039 PDB1P040 PDB1P041 PDB1P042 PDB1P043 PDB1P044 PDB1P045 PDB1P046 PDB1P047 PDB
1P048 PDB1P049 PDB1P050 PDB1P051 PDB1P052 PDB1P053 PDB1P054 PDB1P055 PDB1P056 PDB1P057 PDB1P058 PDB1P059 PDB1P060 PDB1P061 PDB1P062 PDB1P063 PDB1
P064 PDB1P065 PDB1P066 PDB1P067 PDB1P068 PDB1P069 PDB1P070 PDB1P071 PDB1P072 PDB1P073 PDB1P074 PDB1P075 PDB1P076 PDB1P077 PDB1P078 PDB1P079 PDB1P
080 PDBCUS12

Best Answer

As with many things database-y, it's probably easier to extract them as rows and then reformat using your favourite shell-based text processor or, if you still want to try and get them all on one line, make sqlplus's "line" as big as it will go:

set linesize 32767

Related Solutions

How to limit select output from sqlplus

I don't think you can. The only thing I know that sort of does that (but is slightly annoying IMO) is the PAUSE option which waits for you to press enter between pages. The annoying part is that it pauses before the first row... You can Ctrl C to interrupt when you've seen enough.

SQL> set pause on
SQL> set pages 10 pause 'press enter to continue'
SQL> select object_name from dba_objects;
press enter to continue

OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
press enter to continue

OBJECT_NAME
--------------------------------------------------------------------------------
I_IND1
I_CDEF2
I_OBJ5
I_PROXY_ROLE_DATA$_1
FILE$
UET$
I_FILE#_BLOCK#
press enter to continue

I_FILE1
ERROR:
ORA-01013: user requested cancel of current operation

15 rows selected.

Oracle – How to Make SQLPlus Output Appear in One Line

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 less then -S the 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.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 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 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 SET parameters concerning output (NUMWIDTH, NUMFORMAT, LONG, COLSEP) and performance (ARRAYSIZE, LONGCHUNKSIZE).

You have to use the COLUMNcommand to format individual columns.

E.g. column name format a30 will format the column name in 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 || , e.g.

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)"

Best Answer

Related Solutions

How to limit select output from sqlplus

Oracle – How to Make SQLPlus Output Appear in One Line

Related Question