Instance for reading data from the oracle utl_file package

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