Exporting tables to the TXT file method in Oracle

This article lists several ways to export data from an Oracle table to a TXT file, currently only three methods are listed, if the subsequent discovery of better methods will continue to be added.

1.plsqldev There is an option to put the table in EXECL format to the time

2. Using Spool

Sqlplus/as sysdbaset linesize 1000set pagesize 0set echo offset termout offset heading offset feedback offset trims ONse T term OffSET trimspool ONSET trimout onspool '/archlog/exp/test.txt '; Select Owner| | ', ' | | segment_name| | ', ' | | partition_name| | ', ' from dba_segments where rownum<100;spool off;

#输出的test. txt file to edit the tail
#set term off only works if the. SQL script file is used, as specified on the set terms off but still outputs the result, which means that the set term off setting is only useful for SQL scripts

3. Using the Utl_file Package

# #UTL_FILE. Fopen The first parameter is a file path, you cannot specify an absolute path directly, you need to create a directory, and then you specify the directory that we created
Sqlplus/as SYSDBA
Create directory My_dir as '/home/oracle/';
Grant Read,write on directory dir_dump to hr;# #也可以直接建立一个public directory

CREATE OR REPLACE PROCEDURE test istestjiao_handle utl_file.file_type; BEGIN  Test_handle: = Utl_file. FOPEN (' My_dir ', ' test.txt ', ' W ');    For x in (SELECT * from Testjiao) LOOP      utl_file. Put_Line (Test_handle,x.id | | ',' || X.rq | | ', ');    END LOOP;      Utl_file. FCLOSE (Test_handle); EXCEPTION when OTHERS and then  dbms_output. Put_Line (SUBSTR (sqlerrm,1,2000)); end;/

Exporting tables to the TXT file method in Oracle