The oracle utl_file package is expected to be well understood. The following describes how to read data from the oracle utl_file package. If you are interested in the oracle utl_file package, take a look.
- Create or replace directory MY_DIR as '/usr/test /';
- Create or replace function f_exportTxt (
- -- Input parameters
- I _query in varchar2,
- I _separator in varchar2,
- I _dir in varchar2,
- I _filename in varchar2
- ) Return number
- Is
- /**
- ** Function name: f_exportTxt
- ** Parameter: 1. I _query SQL statement; 2. I _separator separator; default value ',';
- ** 3. I _dir directory; 4. I _filename file name. By default, yyyymmddhh24mi is added before the file name.
- ** Return value: If the value is greater than or equal to 0, it indicates the number of records written to the file. If the value is negative, it indicates an exception.
- ** Function: extract user data and generate a file to the specified directory.
- ** Note:
- ** Author: lingo
- ** Modification date:
- **/
- -- Define parameters
- V_file utl_file.file_type;
- V_theCursor integer default dbms_ SQL .open_cursor;
- V_columnValue varchar2 (2000); -- temporary (column value)
- V_colCnt number default 0; -- total number of Columns
- V_separator varchar2 (10) default ','; -- delimiter, default #@
- V_cnt number default 0; -- total number of records
- V_filename varchar2 (100); -- Time
- V_status integer; -- status value returned after SQL Execution
- V_count number default 10000; -- the number of queries each time. If the number is greater than this, data is read to the cursor multiple times.
- V_tmp number; -- temporary (total number of records, calculated using SQL statistics, if v_cnt is not equal to v_tmp, the export is incorrect)
- V_ SQL varchar2 (2000); -- combines SQL statements
- V_loops number; -- number of cycles
- Begin
- -- Select to_char (sysdate, 'yyyymmddhh24mi ') into v_filename from dual; -- prefix the file name by year, month, and day
- V_filename: = '';
- V_filename: = v_filename | I _filename; -- creates a file name.
- V_ SQL: = 'select count ('x') from ('| I _query |') '; -- total number of statistics
- Execute immediate v_ SQL into v_tmp;
- Select trunc (v_tmp/v_count) into v_loops from dual; -- number of cycles
- If mod (v_tmp, v_count)> 0 then
- V_loops: = v_loops + 1;
- End if;
- V_file: = utl_file.fopen (I _dir, v_filename, 'w'); -- open the file
- For I in 1 .. v_loops loop
- V_ SQL: = 'select * from (select m. *, rownum r fr ......
Oracle creates and deletes user instances
Entire Process of creating Oracle Materialized View
ORACLE instance creation process
Statement syntax for oracle time addition and subtraction
How to check the oracle deadlock