Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
how to remove trailing white spaces in columns while using Spool

SET PAGESIZE 1000
SET COLSEP ,
SET WRAP OFF
SET HEADSEP OFF
SET UNDERLINE OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 2000
SET TERMOUT OFF
SET FEEDBACK OFF
SPOOL C:\Oracle\documents\emp.csv
SELECT * FROM EMP;
SPOOL OFF
Output is as following
EmployeeNumber,Employeename,EmployeeDepartment,EmployeeDesignation,EmployeeSalary
7657, Andrew, Accounting, , 50000,
Answers
-
what is your client version? if it is 12c and above you can simply use:
<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">set</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> markup csv </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">on</span>
select .....
-
Use
set markup csv on
if 12c or above
or
You'll have to do something like...
select EmployeeNumber||','||Employeename||','||','||EmployeeDepartment||','||EmployeeDesignation||','||EmployeeSalary
from emp
and remember to use to_char on fields if it's a number or a date.
-
Hello Paul,
I have already tried Set markup csv , it is not working, I am using oracle 11g. I cannot use every column separately as I have to use it for a large database and am looking for a dynamic query as i will use it on certain views.
-
Hi Mustafa,
It is not working, already tried. I cannot change the query I have to use just select * from table name. I cannot use every other column names separately as it is a reusable code.
-
Try sqlcl tool.
https://www.oracle.com/database/technologies/appdev/sqlcl.html
In this tool all you need is this
select /*csv*/ * from emp;
This tool has lots of interesting features.
-
Hi Karthick,
Thanks for your help, but we use Toad in our organisation.
-
your example commands are sqlplus commands! so those are application specific. Toad, I assume, allows you to run sqlplus commands. I simply suggest you install an oracle 12c client. you don't need to have a 12c database. just use new client ans your sqlplus will be able to run set markup csv command.
-
8132c5da-3111-4c9a-865a-bcfd06ff02ac wrote:we use Toad in our organisation.
A quick search using Google gave me this: https://forums.toadworld.com/t/export-the-results-of-a-query-to-csv/29959 ...
That said: What exactly are you trying to achieve?
I cannot use every column separately as I have to use it for a large database and am looking for a dynamic query as i will use it on certain views.
Why is the size of the database an issue? Why would you need something "dynamic" (I hate that word...) for views?
I cannot change the query I have to use just select * from table name. I cannot use every other column names separately as it is a reusable code.
Reusable code? What code? If you need help with code you need to show us that code.
-
Hi Stefan,
What exactly are you trying to achieve?
I want to create a csv file from DB which will be transferred in between app and DB servers.
Why is the size of the database an issue? Why would you need something "dynamic" (I hate that word...) for views?
Using all column names is not possible for as not every view might contain all.
Reusable code? What code?
One single procedure which for all views. This particular set of select statement I have to use inside the procedure.
code is confidential.
Thanks for your help.
-
8132c5da-3111-4c9a-865a-bcfd06ff02ac wrote:Hi Stefan,What exactly are you trying to achieve?I want to create a csv file from DB which will be transferred in between app and DB servers.
..Why?
Your app should be using SQL to get data. It should not go back to the stone ages and parse a CSV file...
What's the real business problem you're trying to solve here?