Shulou(Shulou.com)06/01 Report--

This article mainly shows you how Oracle SQLPlus exports data to csv files, the content is easy to understand, I hope you can learn, after learning there will be gains, the following let the editor to take a look at it.

Every now and then we need to export some data for backup, viewing reports, and so on, which can be very slow to export with Sql Developer. With SqlPlus, the speed is very fast.

Prepare the SQL execution file export.sql:

Set colsep, set feedback offset heading offset newp noneset pagesize 0set linesize 200set trimout onspool / data/export.csvselect t.name | |','| | t.age | |','| | t.salary | |','| | t.email | |','| | t.titlefrom employee twhere t.age < 50order by t.salary desc; spool off exit

Description:

Set colsep, # separator

Set feedback off # echo the number of records processed by the sql command

Set heading off # output title

Set newp none # sets the number of pages to display the queried data. If you need continuous data, set newp to none without blank lines in the middle.

Set pagesize 0 # outputs lines per page, in order to avoid paging set to 0

Set linesize 200 # each line size, if the setting is too small, it will branch, preferably super good output maximum value

Set trimout on # removes trailing spaces from each line of standard output

Set termout off # displays the execution results of commands in the script

Set echo on # sets whether the running command displays statements

Set numwidth 12 # output number type field length

Execute:

Sqlplus user/pass@db @ export.sql

You can specify a file name through SQL:

Col datestr new_value filenameselect'/ data/export.' | | to_char (sysdate,'yyyymmdd') | | '.csv' datestr from dual;spool & filename

Ps: let's look at an example of exporting data to csv through sqlplus

Examples

[oracle@localhost ~] $cat data.sqlset echo offset feedback offset linesize 100set pagesize 0set sqlprompt''set trimspool onspool data.csvselect id | |','| | val from testtab;spool offexit [oracle@localhost ~] $is about how Oracle SQLPlus exports data to csv files. If you have learned knowledge or skills, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.