Spool+sql splicing implementation exports the result set to a csv format file

Export csv format file from oracle (using plsql developer tool can easily export result set to csv file, but when there is no plsql tool, you can use the following methods to export query results to csv format file. )

:

one,

Oracle users log in to the linux system and edit the export file script csv_spool.sql

Set colsep

Set feedback off

Set heading off

Set trimout on

Spool / home/oracle/dept.csv

Select'"'| | DEPTNO | |'", "'| | DNAME | |'", "'| | LOC | |'" 'from scott.dept

Spool off

Exit

Spool / home/oracle/dept.csv-- here specify the path and file name of the exported csv file

Select'"'| | DEPTNO | |'", "'| | DNAME | |'", "'| | LOC | |'" 'from scott.dept

-- the export table name and field name are specified here

two,

The table name is formatted as username.table_name, use the following statement, and modify the corresponding table name and user name:

(dba permission is required to execute this statement)

SELECT 'select' | | wm_concat (cols) | | 'from' | | 'scott.dept' |'; 'FROM (

SELECT'"'| | column_name | |'"'AS cols from dba_col_comments WHERE table_name='DEPT' AND owner = 'SCOTT')

After modifying the statement, execute it in sqlplus, copy the result set to .sql, replace the select statement in the original file, and save exit.

three,

Execute >

Sqlplus-s username / password @ database name @ / path / csv_spool.sql

(the user here needs to have select access to the specified table in the sql script)

Fourth, check whether the dept.csv file is generated under the / home/oracle path.