sql*plus is truncating the columns names according to the values in that columns

Question

I have two broad questions:

Q1. Are executing the lines of code in Oracle SQL Developer and executing the same code in sqlplus command prompt same things? (reason I ask this that I heard that not all the sqlplus commands are executable in SQL Developer. If answer to above question is yes then please then few useful links will help).

Q2. I am spooling the results of a sql query to a .csv file, but the thing is that columns names are truncated according the maximum length of the values in that column. My code is:

set colsep ","
spool C:\Oracle\sample.csv
select * from acct_clas_rule;
spool off;

Output of above code is (middle column is having null values)

 ABCD_,ABCD_,ABC
-----,-----,---
AB   ,     ,WSD
ABCD ,     ,WSD
ABCD ,     ,WSD
SG   ,     ,WSD
KD   ,     ,WSD
WD   ,     ,LKJ
KLHGF,     ,LKO
WSDFG,     ,LOK
WSDF ,     ,LKO
WS   ,     ,GH

In above output, columns names have been truncated. I want full names of the columns to be displayed. Can anyone help? I have seen the question in this link, but I didn't understand how to apply the answers provided there as there was no particular example cited. I am new to these things so I couldn't understand.

Original names of my columns (from left to right in above table) are : ABCD_DFGT_SDF, ABCD_EDF_GH, ABCD_DFRE

PS -

1. I am using Oracle SQL developer to run sqlplus commands. I think because of which few of my commands are not working (like set underline, set linesize etc.).Please let me know if this is the case. I actually want remove those underlines beneath the columns names.

2. Also let me know that whether you answer is applicable to Oracle SQL Developer or sqlplus. Thank You

Answer 1

use the sql plus set command -----> set underline off

Answer 2

Q1: TI'm not an expert of SQL Developer. There might be a mode like "command line" or something where you could get similar result, but unsure.

Q2: You have to set the right option to sqlplus: here is a trick I know of (it will also remove the --- --- --- that will cause other issue):

1. SET HEADING OFF`, to avoid column name to be printed

2. Union all the column names at the beginning of your script:

   set colsep ","
   spool C:\Oracle\sample.csv
   select 'ABCD_DFGT_SDF', 'ABCD_EDF_GH', 'ABCD_DFRE' from dual
   UNION ALL
   select * from acct_clas_rule;
   spool off;
   

Answer 3

There are a couple of things you can do, in addition to @JChomel's approach - that will work in either SQL Develoepr or SQL*Plus, while these suggestions are specific to SQL Developer.

Let's start with a dummy query based on a CTE to get something like your situation:

set colsep ","
with acct_clas_rule (abdc_1, abcd_2, abcd_3) as (
  select cast('AB' as varchar2(5)), cast(null as varchar2(5)), cast('WSD' as varchar2(4)) from dual
  union all select 'ABCD', null, 'WSD' from dual
  -- ...
  union all select 'WS', null, 'GH' from dual
)
select * from acct_clas_rule;

When run as a script in SQL Developer (from the document+arrow icon, or F5) the output is:

ABDC_,ABCD_,ABCD
-----,-----,----
AB   ,     ,WSD 
ABCD ,     ,WSD 
WS   ,     ,GH  

If you change the query to include the SQL Developer-specific formatting hint /*csv*/ then you get the output you want:

select /*csv*/ * from acct_clas_rule;

"ABDC_1","ABCD_2","ABCD_3"
"AB","","WSD"
"ABCD","","WSD"
"WS","","GH"

except that the strings are all enclosed in double-quotes, which might not be what you really want. (It depends what you're doing with the spooled file and whether any of your string values contain commas, which would confuse Excel for instance).

With more recent versions of SQL Developer you can get exactly the same result without modifying the query using the sqlformat option:

set sqlformat csv
select * from acct_clas_rule;

but again you get the double-quotes. You could change the double-quotes to different enclosure characters, but that probably doesn't help.

A different approach is to use the built-in export tools instead of spool. If you run the query as a statement (green 'play button' icon, or control-enter) then rather than appearing in the Script Output panel, a new Query Result panel will open next to that, showing the results as a grid.

If you right-click on the results you'll get a contextual menu, and choosing Export... from that will give you a wizard to export the results in a format of your choice, including CSV:

You can leave the left and right enclosures as double-quotes to get the same results as the options above, except that null values use the word 'null' instead of an empty string:

"ABDC_1","ABCD_2","ABCD_3"
"AB",null,"WSD"
"ABCD",null,"WSD"
"WS",null,"GH

or you can change them, or remove them by choosing 'none', which gives you:

ABDC_1,ABCD_2,ABCD_3
AB,null,WSD
ABCD,null,WSD
WS,null,G

@thatjeffsmith has commented on how to change the null-replacement text for the /*csv*/ approach. For export it looks like having the word 'null' might have been a bug in 17.2.0; in 17.3.1 that does not appear, and instead you see:

ABDC_1,ABCD_2,ABCD_3
AB,,WSD
ABCD,,WSD
WS,,GH

or enclosed empty strings ("") if you leave the enclosures set.