Use Oracle to generate text files

1. Generate a file in the stored procedure of Oracle

It is convenient to generate files in Oracle, especially when an external interface is generated, it is completed through a scheduled task, it is more flexible than compiled languages.

When using PL/SQL to generate a file, you need to use two things: 1. Directory object, 2. File Toolkit

1. Directory object

Directory objects are created by creating an object in the database and pointing to the directories of all machines in the database. Note that they are not local directories. If the database is on the magnetic array, it is best to place the file on the magnetic array, so that the dual-machine hot backup will not cause problems.

You can create a directory by using PL/SQL developer or other visual tools, or by using scripts. The script is as follows:

-- Create directorycreate or replace directory BAS_BLACK_DATA  as '/data/appdata/metadata/dispatch/bas';

2. File toolkit utl_file

You can use the utl_file package to read and write files. The following is a document of The utf_file.fopen function that I have extracted.

Note that this version is 10 Gb. If 9i is used, the "WB" and "rb" and "AB" modes are not supported.

This means that files cannot be opened or written in two standard mode. Generally, when writing a file, the line break is related to the platform. If you need to generate a line break in Linux, you have to perform the following operations manually:

Utf_file.put_line (Line | CHR (13); -- add a carriage return by adding CHR (13) = 0x0d. If you are on a Windows platform, a carriage return line break is automatically generated. If you only want to generate a line break on Windows, you can only manually write the line break CHR (10) In utf_file.put mode ).

For details, you can click the package by pressing ctrl on pl/SQL developer to view the package source code.

/*  ** FOPEN - open file  **  ** As of 8.0.6, you can have a maximum of 50 files open simultaneously.  **  ** As of 9.0.2, UTL_FILE allows file system access for directories  ** created as database objects.  See the CREATE DIRECTORY command.  ** Directory object names are case sensitive and must match exactly  ** the NAME string in ALL_DIRECTORIES.  The LOCATION parameter may be  ** either a directory string from the UTL_FILE_DIR init.ora parameter  ** or a directory object name.  **  ** IN  **   location     - directory location of file  **   filename     - file name (including extention)  **   open_mode    - open mode ('r', 'w', 'a' 'rb', 'wb', 'ab')  **   max_linesize - maximum number of characters per line, including the  **                  newline character, for this file.  **                  Valid values are 1 through 32767 and NULL.  A NULL  **                  value for max_linesize indicates that UTL_FILE should  **                  calculate an operating system specific value at runtime.  ** RETURN  **   file_type handle to open file  ** EXCEPTIONS  **   invalid_path        - file location or name was invalid  **   invalid_mode        - the open_mode string was invalid  **   invalid_operation   - file could not be opened as requested  **   invalid_maxlinesize - specified max_linesize is too large or too small  **   access_denied       - access to the directory object is denied  */  FUNCTION fopen(location     IN VARCHAR2,                 filename     IN VARCHAR2,                 open_mode    IN VARCHAR2,                 max_linesize IN BINARY_INTEGER DEFAULT NULL)           RETURN file_type;  PRAGMA RESTRICT_REFERENCES(fopen, WNDS, RNDS, TRUST);

2. Notes for generating files

When generating a file, when generating a file with a Fixed Length byte, you must pay attention to the functions related to the character set. If an error occurs, it will directly cause problems to the interface file, this is my lesson!

For example, to generate a line of text, the limit must be within 100 bytes.

If I write

utf_file.put_line(rpad(content, 100));

Can this be the case? Is there a problem?

In most cases, there is no problem, but there must be a problem. If you view the rpad file, it will be described as follows:

RPADReturnsexpr1, Right-padded to lengthnCharacters
expr2, Replicated as your times as necessary. This function is useful for formatting the output of a query.

The argumentnIs the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in
Return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

It means that this function can generate the character you specified, and the width here generally refers to the width displayed on the screen, there will be problems in multiple character sets. If we want to solve this problem, we need to seek a function to get the number of bytes and generate it using the following method:

utf_file.put_line(content||rpad(' ', 100-lengthb(content)));

3. Comparison with text-related functions

As mentioned above, the precautions for using Oracle to generate files. Here, we will also list the differences between related functions. We only need to pay special attention to the handling of null in Oracle, generally, functions return NULL for null. Therefore, if the generated file item is null, special processing is required. For example, if the content is null, this row is empty. Therefore, the correct method is to determine whether the field is null or not.

utf_file.put_line(decode(content, null, ' ', content)||    rpad(' ', 100-lengthb(decode(content, null, ' ', content))));

1. Length Series

TheLENGTHFunctions return the lengthchar.LENGTHCalculates length using characters as defined by the input character set.LENGTHBUses bytes instead of characters.
LENGTHCUses Unicode complete characters.LENGTH2Uses ucs2 code points.
LENGTH4Uses ucs4 code points.

If you want to obtain the number of bytes, you can use lengthb. If Unicode is used, lengthc is used. If ucs2 is used, leng22. if length4 is used, ucs4.

So many character sets here, the others only need to use these are the relevant standards, and the corresponding character width, and the term here is also a bit vague.

In contrast to Chinese, in the case of GBK character sets, length returns 1, lengthb returns 2,

In the utf8 Character Set, length returns 1, lengthb returns 3, because utf8 encoding is like this, and other utf8 encoding is a unicode encoding scheme.

2. vsize Function

VSIZEReturns the number of bytes in the internal representationexpr.

Returns the number of bytes of a string in an international representation, which is also related to the character set.

The following address: http://space.itpub.net/16179598/viewspace-663045 on the length of Chinese Characters in Oracle for some comparisons and experiments, you can refer.

3. rpad Functions

Note.