Skip to Main Content
  • Questions

  • Prevent /*csv*/ Hint From Putting Quotes Around All Text Fields

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, BARRY.

Asked: October 16, 2015 - 11:07 am UTC

Last updated: October 17, 2015 - 3:54 am UTC

Version: 3.1

Viewed 10K+ times! This question is

You Asked

Hi,

In SQL Developer 3.1 I am using the select /*csv*/ hint with spool to output a select query to a file.
This works fine, however every text field has double quotes around it and I am having to manaully edit the output and run a replace command to fix.
Otherwise the csv hint outputs the data extactly how I want it.

I have tried to replicate the csv hint options without using it using the set options but I cannot seem to find the combination that will work.

Please advise what the csv hint sets so I can try and work it out from there?

Cheers

Baz

and Connor said...

I spoke to Jeff Smith, the product manager for SQL Dev.

For the Export facility, you can choose delimiters under Preferences.

For the CSV hint, you get a fixed set of functionality.

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

BARRY EVERTON, October 16, 2015 - 12:14 pm UTC

Hi

Export is fine but I am not using that here.

I have a number of sql files that I am launching from another master sql. It's a semi-automated facility as I will eventually trigger all from stored procedures, however for now I need it this way.

Each of the launnhed sql files performs a select query using dynamic dates and uses the spool command to send to the file.
The /*csv*/ hint lays out the file OK, however with the quotes around the text.

I need to know what settings are set by the /*csv*/ hint

Baz
Connor McDonald
October 17, 2015 - 3:54 am UTC

As the product manager for SQL Developer said:

"For the CSV hint, you get a *fixed* set of functionality."

Nothing you can to change it.

The only thing I could suggest is some post-processing with 'sed' or equivalent.

Hope this helps.

CSV output from SQL Dev 4.1.1

Rajeshwaran, Jeyabal, October 16, 2015 - 12:50 pm UTC

Barry,

I tried this from Sql Developer 4.1.1 goto Tools->perference->Database->Utilities->Export->Comma Seperate value(CSV) and set "Left Enclosure" , "Right Enclosure" to None and uncheck the "Right enclosure in data is doubled"

and tried running this query (select /*csv*/ * from dept) from sql worksheet

Here is the output, i still see double quotes around Text

"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"


Here is one of the custom utility that can unload the data

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9518534700346581975#9519711200346877394


It produces the output like this, does this helps you ?

rajesh@ORA11G>
rajesh@ORA11G> @delim_data "select * from dept"
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

PL/SQL procedure successfully completed.

rajesh@ORA11G>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.