Export data to a csv file using the sqlplus tool, requiring the file to be time-stamped

Now that the business department has a demand, it is necessary to export some specific data in the database at a fixed time every day, and it is best to distinguish and archive according to the date name.

Oracle's sqlplus tool is chosen here. The reason is that it is simple, fast and efficient, cross-platform, linux and win can be operated, and can be done directly with the help of the client of oracle, not as complex as sqlldr.

The parameters of the spool instruction will not be described here. You can easily find them on the Internet. Go directly to the script (I choose the windows platform here).

Scott.sql is as follows:

Set colsep, set feedback offset heading onset trimout onset pagesize 50set linesize 80set numwidth 10set termout offset trimout onset underline offcol datestr new_value filenameselect'D:\ test\ scott_' | | to_char (sysdate,'yyyymmdd') | | '.csv' datestr from dual;spool & filenameselect a.empnomema.enamemema.sal from emp a; spool off exit

Note:

Col datestr new_value filenameselect'd:\ test\ scott_' | | to_char (sysdate,'yyyymmdd') | | '.csv' datestr from dual;spool & filename

This part is the variable that defines the exported file, and the database time is obtained.

Also prepare a bat script to connect to the database, select.bat:

Sqlplus scott/scott@HSDB @ scott.sqlpause

The specific implementation effect is as follows. If you want to know more, welcome to comment and exchange.