Showing only actual column data in SQL*Plus
I'm spooling out delimited text files from SQL*Plus, but every column is printed as the full size per its definition, rather than the data actually in that row.
For instance, a column defined as 10 characters, with a row value of "test", is printing out as "test " instead of "test". I can confirm this by selecting the column along with the value of its LENGTH function. It prints "test |4".
It kind of defeats the purpose of a delimiter if it forces me into fixed-width. Is there a SET option that will fix this, or some other way to make it print only the actual column data.
I don't want to add TRIM to every column, because if a value is actually stored with spaces I want to be able to keep the开发者_开发问答m.
Thanks
I have seen many SQL*plus script, that create text files like this:
select A || ';' || B || ';' || C || ';' || D
from T
where ...
It's a strong indication to me that you can't just switch to variable length output with a SET command.
Instead of ';' you can of course use any other delimiter. And it's up to your query to properly escape any characters that could be confused with a delimiter or a line feed.
Generally, I'd forget SQL Plus as a method for getting CSV out of Oracle.
Tom Kyte has written a nice little Pro-C unloader
Personally I've written a utility which does similar but in perl
精彩评论