This example implements the Windows timed task to export the specified data table data in the database to TXT text format. The idea is to use the executable bat file to invoke the Export data script and then invoke the bat file in the Windows Timer task. This example requires a sqlplus environment that can run, so you need to install a running environment such as an Oracle client. implements A. log file that exports log table data from a database to a specified folder, and the file is named in a format that is exported by day (for example: C:\HttpLog\20130115.log), Of course, the specific path can be customized according to your needs. If you do not want to set up Windows timed tasks to export the log, you can also double-click the run Logout.bat file periodically. spool is the Sqlplus command, must be used in the sqlplus, mainly to complete the output of the standard output sqlplus command and execution results, usually user formatted export Oracle table data. for SQL statements with spool data, it is best to customize the format to facilitate direct import of the program, example SQL statements such as: Select Col1| | ', ' | | col2| | ', ' | | col3| | ', ' | | col4| | ', ' | | To_number (To_char (col5, ' YYYYMMDD ')) from Table_name; spool command common settings: Set Colsep ', '; -Field output delimiter set newp none//Set the query out of the number of pages displayed, if you need continuous data, do not appear in the middle of a blank line to the NEWP set to none, so that the output of the data rows are continuous, there is no empty row in the middle of the set echo off; Displays the start script for each SQL command, the default is On set echo on//set to run the command is whether to display the statement set feedback on; Set display "XX line selected" set feedback off; echo the number of records processed by this SQL command, the default is on to remove the last "10000 rows have been selected" set heading off; The output field header, which is set to off by default, removes the field name for the select result, displaying only the data set pagesize 0; Output number of rows per page, the default is 24, in order to avoid paging, can be set to 0. set Linesize 80; Output a line of characters, the default is 80 set numwidth 12; Output Number Type field length, default is 10 set termout off; Shows the execution result of the command in the script, the default is On set trimout on; Remove trailing spaces per line of standard output, default to Off set Trimspool on; Removes the trailing space for the redirect (spool) output per line, which defaults to Off set serveroutput on; Set allow display output similar to Dbms_output. set timing on; Set display "Time used: XXXX". set autotrace on; Settings allow parsing of executed SQL. set verify off//can turn off and turn on the display of the prompt confirmation information old 1 and new 1. 1. Create a script for the database log table exported as a text file Tabout.ctl or Tabout.sqlNote: After the log is exported in the C:\HttpLog directoryThe contents of the Tabout.ctl script are as follows:Set NEWPAGE NONE set HEADING OFF set term OFFSET SPACE 0 set PAGESIZE 0 set trimout on set Trimspool ONSET linesize 2500se T feedback offset echo offset VERIFY offcolumn v_date new_value filename; Select To_char (sysdate, ' YYYYMMDD ') | | '. Log ' v_date from dual; Spool. \httplog\&&filename Select Id| | ', ' | | Name from Orcluser.test; Spool Off;exit;2. Create a Logout.bat file that invokes the scriptLogout.bat content is as follows: mkdir. \httplog\c:\oracle\ora92\bin\sqlplus ORCLUSER/ORCL @c:oracleout.ctlDescription: The bat file creates a folder named Httplog directly under the current directory so that the files exported by the Tabout.ctl script are stored in the directory. It executes the Tabout.ctl script by invoking the Sqlplus executable in Oracle, so the file can be run directly by double-clicking without additional action.3. Automatic periodic execution through Windows timed tasksIn Control Panel-Task Scheduler-Add Task schedule, establish a regular export of the log table data from the database to text format (for example: You can set to export log table data on a daily basis as required). Depending on your current storage requirements for the log file path, modify the storage path in the two scripts above and invoke the Logout.bat file execution directly in the Windows Timer task.
How to export Oracle data tables to text files on a timed basis
