Two Methods for outputting Oracle character files

Two Methods for outputting Oracle character files

Let's sum up a few simple and convenient experiences. A few days ago, a colleague worked with the author to discuss how to output the database search results for a project. In order to be simple and clear, I have prepared two strategies for my colleagues to choose based on different needs and scenarios, recording them and retaining them as necessary.

Text File output, especially the output of large data files, is a common scenario in the field of actual needs. Text files are the first file format we have come into contact with. The format is single and the content is simple. However, it is precisely because of its simple structure that many "intermediate structure files" are usually chosen as carriers. From the earliest simple txt and csv files to now more and more xml files, they are essentially saved in text format.

The so-called "intermediate structure file" is what we usually call an interface file. If large-scale data transmission, access, or interaction is required between systems, the "protocol" recognized by both parties is the key to the problem. The interface file format, coupled with the automatic upload, locating, and retrieval mechanism, enables data interaction between systems in a decoupled manner.

In the Oracle environment, there are two traditional text generation methods. One is to use the native sqlplus command line tool to transfer data to the client directory. Note: The default output of sqlplus command line series is the screen. The other is to use the utl_file toolkit to output data to the Server Side of the database ). The two methods have their own advantages and disadvantages and have their own adaptation scenarios. We will discuss it separately below.

1. experiment environment Introduction

I used Oracle 11gR2 for testing. The specific version is 11.2.0.4.

SQL> select * from v $ version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

2. Sqlplus Spool Method

The Spool method is a common strategy for script engineers. In fact, the idea is also relatively simple: in the traditional programming structure, the output of data processing results is oriented to the positioning mechanism. By default, the Sqlplus tool outputs a screen window. The Spool method is to reset the output mode and output the result text to the disk file.

There are two aspects to use the spool file: first, the file location. The generated files, including Linux/AIX files and Windows files, are generated on the computer where the client is located (the machine on which the program is executed. The other is the limit on the amount of text. Whether using sqlplus or the Command Windows window of PL/SQL Developer, there is a potential risk of buffer overflow. This limits the size of the generated file.

The following is a simple experiment. The structure of the experiment data table is as follows:

SQL> desc test_user;

Name Type Nullable Default Comments

---------------- ------------ -------- ------- Begin -----------------------------------------------------------------------------------------------------------------------------------------------------------

OBJECT_ID CHAR (32) String-Object Id

USER_ID CHAR (32) Y String-Unique User ID

Firstname char (50) Y String-Users first name

Lastname char (50) Y String-Users last name

Shortname char (5) Y String-Users short name

IS_ACTIVE CHAR (1) Y Character-Boolean

Execute the following commands in the sqlplus command line:

SQL> spool d: \ spool_test.txt

Started spooling to d: \ spool_test.txt

SQL>

SQL> set echo off;

SQL> set feedback off;

SQL> set newpage none;

SQL> set linesize 1000;

SQL> set pagesize 0;

SQL> set term off;

SQL> set timing off;

SQL> set verify off;

SQL> select trim (USER_ID) | ',' | trim (FIRSTNAME) | ',' | trim (LASTNAME) | ',' | trim (IS_ACTIVE)

2 from test_user;

SQL> spool off;

Stopped spooling to d: \ spool_test.txt

Pay attention to several details:

First, use the spool command at the SQL> command prompt to start/Close file write operations to the disk. The Spool on command is enabled by default, and the file directory name is specified when spool is added to the path. When spool off is disabled, the file write operation stops automatically. Note: From the Perspective of file size, the results are usually written from the buffer to the file only when spool is off.

The second is sqlplus parameter configuration. Sqlplus is easy to use and difficult to use. There are many control parameters used to output input data. For general users, a fixed control parameter template can be directly retained, and commands with the same requirements can be directly used.

After the file is generated, we can find the corresponding file in the directory of the client machine. The content exactly meets the requirements for comma-separated data.

111,222,222, Y

111,2222, 33, Y

11,222, 33, Y

(The reason for the length is omitted .....)

The advantage of the Spool method is that it is easy to use and has low requirements on user permissions. As long as you can use the sqlplus tool, you can generate files. At the same time, the generated file is on the client, and the DBA and System Administrator do not need to set the directory permission management space consumption.

At the same time, the spool method has obvious disadvantages, namely the file size limit. After entering 11 GB, sqlplus has expanded the buffer, but it is still a great hidden danger. From the perspective of System Automation, it is not a standard practice to store the generated interface files on the client, which is not conducive to subsequent automated transmission and processing.

All in all, the spool method is applicable to the generation of small-scale and simple data files.

3. Use the utl_file package

UTL_FILE is a classic file generation method officially recommended by Oracle. The main principle is to use the utl_file package to read and write text files. The biggest difference with the spool method is that the utl_file package is mainly generated on the database server side (that is, the server where the Oracle Instance runs), and The utl_file package is more fine-grained in file read/write control, reads and writes files in a way similar to the C language.

Currently, utl_file is still a mature solution for generating files from the database, especially large interface files. In some high-performance scenarios, it is still competitive.

For the utl_file package, you cannot skip the utl_file_dir parameter. Before Oracle 9.2, this parameter is the most important parameter for generating read/write utl_file execution.

SQL> show parameter utl_file

NAME TYPE VALUE

-----------------------------------------------------------------------------

Utl_file_dir string

When we query information on the network, we can still see the setting requirements for the utl_file_dir parameter. In versions earlier than 9.2, this parameter is required for file read/write, and the directory where the read/write file is located is added to it. Otherwise, Oracle will not be able to recognize the file operation permissions under this directory. It is slightly troublesome to modify this parameter in spfile. to take effect, restart the server.

This scenario has changed since version 9.2. The emergence of directory objects provides better read/write directory management and permission management. One of the biggest advantages is that the directory structure can be written in the code without hard coding. Therefore, the current utl_file_dir directory is basically no longer used, just for the purpose of system compatibility.

Follow these steps to use the utl_file package:

Step 1: create a directory

[Oracle @ sicslife/] $ su-root

Password:

-- Ensure the permissions at the operating system level!

[Root @ sicslife ~] # Cd/

[Root @ sicslife/] # chown-R oracle: oinstall/upload/

[Root @ sicslife/] # ls-l | grep upload

Drwxr-xr-x. 4 oracle oinstall 4096 Aug 6 upload

SQL> create directory utl_path as '/upload ';

SQL>

SQL> select directory_name, directory_path from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH

------------------------------------------------------------------------------

UTL_PATH/upload

To use directory, two levels of permissions are required. One is the operating system level, so that the Oracle operating system users can use the directory. The other is the permission to use the directory object, which must be authorized for display.

SQL> grant write on directory utl_path to scott;

Grant succeeded

SQL> grant execute on utl_file to scott;

Grant succeeded

Finally, you can call it in the code.

SQL> set serveroutput on size 1000;

SQL> declare

2 out_file utl_file.file_type; -- file type, that is, the handle object

3 vc_file_name varchar2 (100 );

4 vc_line varchar2 (100 );

5 I number;

6 begin

7 vc_file_name: = 'utl_file_test.txt ';

8

9 out_file: = utl_file.fopen ('utl _ path', vc_file_name, 'w'); -- open the file by writing

10

11 if (utl_file.is_open (out_file) then

12 for I in 1 .. 100 loop

13 vc_line: = to_char (I) | ',' | I | 'Lines ~ ';

14 utl_file.put_line (out_file, vc_line );

15 end loop;

16 else

17 dbms_output.put_line ('Open Failure ~ ');

18 end if;

19

20 utl_file.fclose (out_file); -- similar to the C language, it must be displayed and closed.

21 end;

22/

PL/SQL procedure successfully completed

Finally, you can find the corresponding file at the operating system level.

[Root @ sicslife/] # cd/upload/

[Root @ sicslife upload] # ls-l

Total 12

Drwxr-xr-x. 7 oracle oinstall 4096 Aug 27 2013 database

Drwx ------. 3 oracle oinstall 4096 Aug 5 igb-5.3.2

-Rw-r --. 1 oracle oinstall 1184 Aug 7 utl_file_test.txt

[Root @ sicslife upload] # cat utl_file_test.txt

1, 1Lines ~

2, 2Lines ~

3, 3Lines ~

4, 4Lines ~

5, 5Lines ~

6, 6Lines ~

(The reason for the length is omitted .......)

[Root @ sicslife upload] #

The Utl_file package is a mature file read/write solution. In addition to the steps and methods in the instance, it also defines many useful read/write methods and exception types, this makes sense for us to complete programming. At the same time, in practical applications, utl_file has more detailed factors and restrictions. This article will not go into detail.

3. Conclusion

Using databases to generate files is a very common requirement. Spool and utl_file are good tools without using third-party tools.