Export text data from Oracle tables (xls or TXT)
Two methods have been tested today. The record is as follows:
1. Method 1: Use the utl_file package
You can export data from a table as follows:
Create or replace procedure p_tabletoxls is
V_file utl_file.file_type;
Cursor cur_emp is
Select ename, deptno from EMP;
Begin
If utl_file.is_open (v_file) then
Utl_file.fclose (v_file );
End if;
V_file: = utl_file.fopen ('utl _ file_dir ', 'emp.xls', 'w ');
For I in cur_emp Loop
Utl_file.put_line (v_file, I. ename | CHR (9) | I. deptno); -- CHR (9) refers to the field column change
End loop;
Utl_file.fclose (v_file );
Exception
When others then
Dbms_output.put_line (sqlerrm); -- write data
If utl_file.is_open (v_file) then
Utl_file.fclose (v_file );
End if;
End p_tabletoxls;
Note: To use the ult_file package, you must first create a directory to store data.
Create or replace directory utl_file_dir as 'd: \ dir ';
Grant read, write on directory utl_file_dir to ltwebgis;
2. Method 2: Use the ociuldr Tool
Download the tool as follows:
D: \ ociuldr> ociuldr user = username/username @ orcl query = "select ename, deptno from EMP"
Field = 0x20 record = 0x0a file1_emp.xls
Command description:
User = username/password @ tnsname
SQL = SQL file name, one SQL per file, do not include ";"
Query = SELECT statement
Field = seperator string between fields
Record = seperator string between records
File = output file name (default: uldrdata.txt)
Field = 0x20 indicates that fields are expressed by spaces, or field = ''.
Field = 0x09 indicates that fields are displayed in different columns, that is, they are exported by column directly during Excel export. If only one column in Excel is exported using another separator, you can use Excel to separate the columns.