This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

how to remove trailing white spaces in columns while using Spool

4068702
4068702 Member Posts: 5
edited Aug 21, 2019 7:55AM in SQL & PL/SQL

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,

Tagged:
AndrewSayer
«1

Answers

  • Mustafa_KALAYCI
    Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown
    edited Aug 21, 2019 4:27AM

    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 .....

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Aug 21, 2019 4:43AM

    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.

  • 4068702
    4068702 Member Posts: 5
    edited Aug 21, 2019 5:29AM

    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.

  • 4068702
    4068702 Member Posts: 5
    edited Aug 21, 2019 5:31AM

    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.

  • Karthick2003
    Karthick2003 IndiaMember Posts: 13,711 Bronze Badge
    edited Aug 21, 2019 5:45AM

    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.

  • 4068702
    4068702 Member Posts: 5
    edited Aug 21, 2019 6:00AM

    Hi Karthick,

    Thanks for your help, but we use Toad in our organisation.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI TurkeyMember Posts: 3,405 Bronze Crown
    edited Aug 21, 2019 7:22AM

    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.

  • [Deleted User]
    [Deleted User] Jack of all (Geo-) trades Hoorn, The NetherlandsPosts: 0 Silver Trophy
    edited Aug 21, 2019 6:37AM
    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.

  • 4068702
    4068702 Member Posts: 5
    edited Aug 21, 2019 7:16AM

    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.

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Aug 21, 2019 7:28AM
    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?