However, you will have to put quotes around strings on any columns that might contain embedded comma characters. Use sed to edit the output file. You can find it on my githup repository. You should be aware that values of fields could contain commas and quotation characters, so some of the suggested answers would not work, as the CSV output file would not be correct.
To replace quotation characters in a field, and replace it with the double quotation character, you can use the REPLACE function that oracle provides, to change a single quote to double quote. Use vi or vim to write the sql, use colsep with a control-A in vi and vim precede the ctrl-A with a ctrl-v. Be sure to set the linesize and pagesize to something rational and turn on trimspool and trimout. That sed thing can be turned into a script.
Isn't it great that they bothered to enable html output from sqlplus but NOT native csv????? There is a problem using sqlplus to create csv files. If you want the column headers only once in the output and there are thousands or millions of rows, you cannot set pagesize large enough not to get a repeat. See bash script below:. As noted in the script comments, someone at Oracle put my script in an Oracle Support note, but without attribution.
Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 12 years, 10 months ago. Active 1 year, 6 months ago.
Viewed k times. How do I do it? Daniel C. Sobral Daniel C. Sobral k 83 83 gold badges silver badges bronze badges. After inserting your query, you also need to halt spool in order to close the output file.
This can be done by using the spool off command. You can now finally insert your query. Instead of manually entering every line, you can also enter all the settings into a new script file.
Hevo can help you Integrate your data from numerous sources like Oracle and load them into a destination to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free.
It is user-friendly, reliable, and secure. We would love to hear from you! Your email address will not be published. You may use these HTML tags and attributes:. Save my name, email, and website in this browser for the next time I comment.
Skip to content. Introduction Oracle is a database that is widely used in the IT industry due to its extensive data management capabilities. Table of Contents What is Oracle? The last step after the settings are modified and spool is running is to insert your query. Now paste the entire script command list into your new file and save.
The full contents of our example script can be found below. To execute the script, simply use the symbol followed by the file name:. Email Required, but never shown. The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses. Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually.
Visit chat. Related Hot Network Questions. Question feed.
0コメント