how to increase sqlplus column output length

Question

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

User · Answer

On Windows you may try this     right-click in the sqlplus window  select properties - layout increase screen buffer size width to 1000

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try this     COLUMN col name FORMAT A24   where 24 is you width

User · Answer

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

[sql] how to increase sqlplus column output length?

Examples related to sql

Examples related to oracle

Examples related to sqlplus

Examples related to column-width