Oracle exports the table as a txt file, oracletxt

Oracle exports the table as a txt file, oracletxt

This article lists several methods to export data from an oracle table to a txt file. Currently, only three methods are listed. If better methods are found later, they will be added continuously.

 

1. In plsqldev, there is an option to convert the table to execl format.

 

 

2. Use spool

sqlplus / as sysdbaset linesize 1000set pagesize 0set echo offset termout offset heading offset feedback offSET trims ONset term offSET trimspool ONSET trimout ONspool '/archlog/exp/test.txt';select OWNER||' , '||SEGMENT_NAME||' , '||PARTITION_NAME||' , ' from dba_segments where rownum<100;spool off;

Edit the beginning and end of the test.txt file output by the supervisor.
# Set term off takes effect only when the. SQL script file is used. Although set term off is specified above, the result is still output. That is to say, the set term off setting is only useful for SQL scripts.

 

3. Use the UTL_FILE package

# UTL_FILE.FOPEN the first parameter is the file path. You cannot directly specify the absolute path. You need to create a directory and then specify the directory we created.
Sqlplus/as sysdba
Create directory MY_DIR as '/home/oracle /';
Grant read, write on directory dir_dump to HR; # You can also directly create a 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 THEN  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));END;/

 

 

 

Oracle exports the data in Table A as A txt file. How can I use the stored procedure?

Several steps
1. Create an output path. For example, if you want to output data in the test directory of drive C, you must first create the test path.

2. Log On With sysdba under sqlplus and execute the following statement:

3, create or replace directory TMP as 'C: \ test ';

4. grant read, write on directory TMP to the user you want to generate the file;

5. alter system set utl_file_dir = 'C: \ test' scope = spfile;
After completing the preceding steps, you must restart the database.

6. The most important step is to create a stored procedure.
Create or replace PROCEDURE into rows; Write_content VARCHAR2 (1024); Write_file_name VARCHAR2 (50); v_id int; v_form varchar2 (10); cursor cur_sp_outisselect id, form from a; beginopen cur_sp_out; loop fetch cur_sp_out into v_id, v_form; exit when cur_sp_out % notfound; write_file_name: = to_char (SYSDATE, 'yyyymmd') | '.txt '; file_handle: = utl_file.fopen ('tmp ', write_file_name, 'A'); write_content: = v_id | ''| v_form; -- write file IF utl_file.is_open (file_handle) THEN utl_file.put_line (file_handle, write_content); end if; -- close file utl_file.fclose (file_handle); end loop; close cur_sp_out; end;
I have created a test table like you. The data is as follows:
Create table a (id int, form varchar2 (10); insert into a values (1, ''); insert into a values (2,' ');
Then execute the Stored Procedure
Begin SP_OUTPUT; end;
After the execution is completed, you will find that a new file has been generated in the C drive test path.

File Content

If the table name and field name are the same as those of the stored procedure, you do not need to modify them.



How to export data from an Oracle table to a Txt format

During the system demonstration last week, some text data needs to be manually created to act as the data source. If you want to be lazy, you can use the Toad tool to directly query and export the data to the csv format. Today is just a bit of time. I sorted out my ideas and used SQL to generate text data. I have written a general framework. If you have time, let's improve it. Create an SQL script d: \ czrk. the Code is as follows: SET echo off SET feedback off SET newpage none SET pagesize 50000 SET linesize 20000 SET verify off SET pagesize 0 SET term off SET trims on set heading off SET trimspool on set trimout on set timing off SET verify off SET colsep | spool d: \ czrk.txt SELECT sfzh | ',' | xm | ',' | xb | ',' | csrq | ',' | mz | ', '| xzqh |', '| jzdz FROM m_czrk WHERE rown Um <= 10000; spool off sqlplus connects to the database, execute the script sqlplus dc/dc @ mydb; sqlplus> @ d: \ czrk. in this way, SQL generates 10 thousand data records under drive D.