Implementation of outputting ORACLE database records to text files

How can ORACLE database records be output to text files? The following describes how to output an ORACLE database record to a text file. It is helpful for you to learn about ORACLE database records.

In some cases, for example, to exchange data with other databases, we need to output the data in the ORACLE database, save it to an intermediate file, and then process it. So how to output data in the ORACLE database?

Assume that the data is text-type data characters, numbers, and dates. We have at least two ways to output the data. One is to use the tabulation function to output all the data in the form of tables, save the table to a text file. Another method is simpler: Use the UTL_FILE package to directly input data to a text file.

Before using the UTL_FILE package, you must define the writable directory of UTL_FILE in the ORACLE database initialization file (initsid. ora), for example

 
 

  
  
UTL_FILE=/eXPort/home/oracle/output 
 
 

In addition, it is important to note that the maximum size of a field in the output record is 1023 bytes. If a field exceeds this limit, it must be truncated into several parts, as shown in the following example.
Here is an example. You can copy it first, and then use it after modification based on the actual situation of your database.

 
 

  
  
DECLARE
  
  
OutputFile UTL_FILE.FILE_TYPE;
  
  
Vart_title perd11.ART _ TITLE % TYPE;
  
  
Vart_author perd11.ART _ AUTHOR % TYPE;
  
  
Vart_keyWord perd11.ART _ KEYWORD % TYPE;
  
  
Vart_abstract perd11.ART _ ABSTRACT % TYPE;
  
  
Vart_abs1 perd11.ART _ ABSTRACT % TYPE;
  
  
Vart_abs2 perd11.ART _ ABSTRACT % TYPE;
  
  
Vart_authorinstitute perd11.ART _ AUTHORINSTITUTE % TYPE;
  
  
Vperd_volume perd11.PERD _ VOLUME % TYPE;
  
  
Vperd_issue perd11.PERD _ ISSUE % TYPE;
  
  
Vperd_date perd11.PERD _ DATE % TYPE;
  
  
Vpub_name perd11.PUB _ NAME % TYPE;
  
  
Vpub_issn perd11.PUB _ ISSN % TYPE;
  
  
Vclass_name perd11.CLASS _ NAME % TYPE;
  
  
 
  
  
CURSOR cperd11 IS
  
  
Select * from perd11;
  
  
 
  
  
BEGIN
  
  
OutputFile: = UTL_FILE.FOPEN
  
  
('/Export/home/oracle/output', 'perd11. out', 'A ');
  
  
Open cperd11;
  
  
LOOP
  
  
Fetch cperd11 into vart_no, vart_title,
  
  
Vart_author, vart_keyword, vart_abstract,
  
  
Vart_authorinstitute, vperd_volume,
  
  
Vperd_issue, vperd_date, vpub_name,
  
  
Vpub_issn, vclass_name;
  
  
IF length (vart_abstract)> 1000 THEN
  
  
Vart_abs1: = substr (vart_abstract, 0,1000 );
  
  
Vart_abs2: = substr (vart_abstract, 1001 );
  
  
ELSE
  
  
Vart_abs1: = vart_abstract;
  
  
Vart_abs2: = ";
  
  
End if;
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Journal category] \ n % s \ n', vclass_name );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Journal publication number] \ n % s \ n', vpub_issn );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Journal name] \ n % s \ n', vpub_name );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Journal volume number] \ n % s \ n', vperd_volume );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Journal id] \ n % s \ n', vperd_issue );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Publication date] \ n % s \ n', vperd_date );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Chinese title: \ n % s \ n', vart_chntitle );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
【Author】 \ n % s \ n', vart_chnauthor );
  
  
 
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Author unit: \ n % s \ n', vart_authorinstitute );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Chinese keywords: \ n % s \ n', vart_chnkeyword );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Chinese Abstract: \ n % s \ n', vart_engabs1 );
  
  
UTL_FILE.PUTF (OutputFile ,'
  
  
Chinese digest 2] \ n % s \ n', vart_engabs2 );
  
  
UTL_FILE.FFLUSH (OutputFile );
  
  
Exit when cperd11 % NOTFOUND;
  
  
End loop;
  
  
Close cperd11;
  
  
 
  
  
END;
  
  
 
  
  
-
 
 

Implementation of Oracle's fixed number of records

Explain how to modify the table structure in Oracle

The section of the Oracle database

Oracle STARTUP script in Linux

Summary of Oracle Database startup and shutdown Methods