ORCLE exports a large amount of data to txt

ORCLE to export a large amount of data to txt Method 1: Use the SQLPlus spool command operation step 1. Create a script file and store it, such as D: \ spool. SQL. The specific code is as follows:

SQL code set echo off -- when you use the start command to execute an SQL script, whether to display the SQL statement set feedback off being executed in the script -- whether to display the number of rows queried or modified by the current SQL statement set newpage none -- no interval is set verify off between the page and the page -- set pagesize 0 -- number of pages divided

Set term off -- when the spool command is used to output the content in a large table to a file, it takes a lot of time to output the content on the screen. After setting set termspool off, the output content is saved only in the output file and not displayed on the screen, greatly improving the spool speed. Set trims on -- Remove unnecessary spaces behind each row in the SPOOL output from the set linesize 600 -- set the width of the display line on the screen, the default value is 100 set heading off. The output column title is set timing off. The default value is OFF. The query time is set to estimate the execution time of SQL statements. The test performance is set numwidth 38 SPOOL D: \ aaa.txt select UserTelNo | ',' | to_char (ReceiveTime, 'yyyy-MM-dd HH24: MI: ss') | ',' | UserContent | ', '| ReplyContent FROM LogSMSHall_Mutual WHERE rownum <= 100; SPOOL OFF

 

Note that if there is a clob field, it is best to use the to_char function for conversion. 2. use sqlplus to log on to the oracle database. If it is on the local database, enter "SQLPlus [user name]/[Password]" in the command line (WIN + R-> CMD) window. If you log on remotely, enter "SQLPlus [user name]/[Password] @ [database IP Address: Listener port number]/[database instance name]" in the command line, and press Enter. Such as "sqlplus Ajita/1@192.168.85.136: 1521/orcl ". 3. Execute the script file in step 1 in the SQLPlus command window. The command method is "@ [script file location]", for example, "@ D: \ spool. SQL "advantages: easy to use disadvantages: not easily encapsulated into a Stored Procedure Method 2: using the UTL_FILE package file operation command operation steps (only one step is required, can also be encapsulated into a stored procedure) compile the PL/SQL process and export it to txt.

SQL code DECLARE row_result varchar2 (1024); selectsql varchar2 (1024); qrycursor SYS_REFCURSOR; txt_handle UTL_FILE.file_type; BEGIN selectsql: = 'select bizname | ''' | bizstatus from bizbusinateaseinfo'; txt_handle: = UTL_FILE.FOPEN ('d:/utl', 'a.txt ', 'w'); open qrycursor for selectsql; loop fetch qrycursor into row_result; exit when qrycursor % notfound; UTL_FILE.PUT_LINE (txt_handle, row_result); end loop; -- close cursor close qrycursor; UTL_FILE.FCLOSE (txt_handle); end;

 

Note: If the utl_file_dir parameter is not set, this method will report the ora 29280 error. There are two solutions. 1. Set the utl_file_dir parameter in two ways: a) set in init. ora and modify the file directly. B) set it in the sqlplus command line.

alter system set utl_file_dir=* scope=spfile; 

 

Note: utl_file_dir = * indicates that you can operate any directory. Do not use utl_file_dir = d: \ to indicate that you can operate files under the d: \ directory, but you cannot operate the subdirectories in the d: \ directory. Note that when utl_file_dir = is set, if the path is a long path name, such as c: \ my temp directory, you must add '', for example: utl_file_dir = 'C: \ my temp 'utl_file_dir can be multiple paths utl_file_dir = c: \, d: \, d: \ temp, 'c: \ my temp: the database must be restarted after the settings are complete. 2. create your own directory (no need to restart the database) and execute the command create or replace directory MY_DIR as 'C:/abc'; on the command line. Then, call frw: = utl_file.fopen ('C: \ abc', 'emp.txt ', 'w'); change it to frw: done', 'w'); note that MY_DIR should be capitalized. Advantage: it is easy to encapsulate into a stored procedure. disadvantage: UTL_FILE must be used for configuration, which is a little complicated.