Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PL / SQL -> UTL_FILE use presentation package     - Asynchronous communication mechanism between the Android source code analysis thread (Programming)

- Delay for the specified IP port analog network to send and receive packets on Linux (Linux)

- Why you should choose Python Programming (Programming)

- Installation under Linux Mint system guidelines for Gtk (Linux)

- SQLite database commonly used sentences and visualization tools on MAC MeasSQLlite use (Database)

- Linux install Maven and SVN client (Linux)

- CentOS / Linux install VNC Server (Linux)

- 5 steps to help you become a good Docker contributors (Linux)

- Kali Linux virtualbox rc = Error 1908 workaround (Linux)

- Java to create a table in the database SYBase (Database)

- Help you make Git Bisect (Linux)

- MySQL high availability cluster fragmentation of deployment uses Fabric (Database)

- How to clear the DNS query cache under Linux / Unix / Mac (Linux)

- SecureCRT session buffer size settings (Linux)

- Analysis RabbitMQ cluster (Server)

- Linux Getting Started tutorial: Experience VirtualBox Virtual Machine chapter (Linux)

- Raspberry Pi 2 to install the latest version of the FPC and Lazarus 1.5 (Linux)

- Nginx installation configuration Zabbix (Server)

- Linux - Common process the command (Linux)

- MongoDB, Cassandra, HBase transaction design strategy (Database)

 
         
  PL / SQL -> UTL_FILE use presentation package
     
  Add Date : 2018-11-21      
         
       
         
  In PL / SQL in, UTL_FILE package provides a text file input and output functions each other. This means that we can achieve through this package from the operating system level to achieve the input file is read or written to the operating system files. You can also load data from other systems through the package to the database. If you load a web server logs, user login, database logs and the Oracle log files and so on. This paper describes the features and UTL_FILE by example demonstrates the use of this package and understand the relevant process functions.

1, UTL_FILE Introduction
  a, to achieve read and write functionality is based on the operating system level
  b, the mode is server-based text file access mode, does not support binary file
  c, can be set multiple paths pl / sql to access the operating system files by setting parameters utl_file_dir
  d, all users can read and write utl_file_dir parameter settings directory, so you should consider security issues
  e, may be arguments utl_file_dir blank, and by creating a directory and the directory permissions to access os file (the recommended way) granted

2, UTL_FILE package procedures and functions
a, file_type UTL_FILE defined in the record type follows its members are private and can not be referenced directly or change the recording components.

  TYPE file_type IS RECORD (
      id BINARY_INTEGER,
      datatype BINARY_INTEGER,
      byte_mode BOOLEAN);

b, function UTL_FILE related process function description
  FCLOSE Procedure Closes a file
  FCLOSE_ALL Procedure Closes all open file handles
  FCOPY Procedure Copies a contiguous portion of a file to a newly created file
  FFLUSH Procedure Physically writes all pending output to a file
  FGETATTR Procedure Reads and returns the attributes of a disk file
  FGETPOS Function Returns the current relative offset position within a file, in bytes
  FOPEN Function Opens a file for input or output
  FOPEN_NCHAR Function Opens a file in Unicode for input or output
  FREMOVE Procedure Deletes a disk file, assuming that you have sufficient privileges
  FRENAME Procedure Renames an existing file to a new name, similar to the UNIX mv function
  FSEEK Procedure Adjusts the file pointer forward or backward within the file by the number of bytes specified
  GET_LINE Procedure Reads text from an open file
  GET_LINE_NCHAR Procedure Reads text in Unicode from an open file
  GET_RAW Procedure Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
  IS_OPEN Function Determines if a file handle refers to an open file
  NEW_LINE Procedure Writes one or more operating system-specific line terminators to a file
  PUT Procedure Writes a string to a file
  PUT_LINE Procedure Writes a line to a file, and so appends an operating system-specific line terminator
  PUT_LINE_NCHAR Procedure Writes a Unicode line to a file
  PUT_NCHAR Procedure Writes a Unicode string to a file
  PUTF Procedure A PUT procedure with formatting
  PUTF_NCHAR Procedure A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
  PUT_RAW Procedure Accepts as input a RAW data value and writes the value to the output buffer

3, Demos ULT_FILE usage

The main steps a, use UTL_FILE (using directory mode)
  - Create a directory for storing files os
  scott @ USBO> ho mkdir -p / u03 / database / usbo / db_utl_dir
  
  - Add directory database level
  scott @ USBO> create directory db_utl_dir as '/ u03 / database / usbo / db_utl_dir';
  
  - Grant
  scott @ USBO> grant read, write on directory db_utl_dir to public;

b, from a SQL query to write to the data file
  DECLARE
    Type ---> defined for receiving a file handle; vsfile UTL_FILE.file_type
    v_cnt PLS_INTEGER: = 0;
  BEGIN
    vsfile: =
        UTL_FILE.fopen ( 'DB_UTL_DIR', ---> use fopen to open the file, define the file path, file name, reading and writing as well as the maximum length of each line of characters, the default is 1024
                        'Emp.txt',
                        'W',
                        200);
  
    FOR i IN (SELECT t.ename || ',' || t.job AS msg ---> use a for loop to read scott.emp table
                FROM scott.emp t WHERE t.sal> 2000)
    LOOP
        UTL_FILE.put_line (vsfile, i.msg); ---> will query for loop content written to a file using put_line
        v_cnt: = v_cnt + 1; ---> counter for recording the number of write statistics
    END LOOP;
  
    UTL_FILE.fflush (vsfile);
    UTL_FILE.fclose (vsfile);
    DBMS_OUTPUT.put_line (v_cnt || 'rows unloaded');
  END;
  /
  
  6 rows unloaded
  
  PL / SQL procedure successfully completed.

  - See the file generated
  scott @ USBO> ho more /u03/database/usbo/db_utl_dir/emp.txt
  JONES, MANAGER
  BLAKE, MANAGER
  CLARK, MANAGER
  SCOTT, ANALYST
  KING, PRESIDENT
  FORD, ANALYST

c, from the data file read and write to the table
  scott @ USBO> create table tb_emp (val varchar2 (30), file_name varchar2 (10));
  
  scott @ USBO> exec read_demo ( 'emp.txt', 'db_utl_dir'); -> call to the procedure, the code tail See article
  
  PL / SQL procedure successfully completed.
  
  scott @ USBO> select * from tb_emp;
  
  VAL FILE_NAME
  ----------------------------- ---------------------
  JONES, MANAGER emp.txt
  BLAKE, MANAGER emp.txt
  CLARK, MANAGER emp.txt
  SCOTT, ANALYST emp.txt
  KING, PRESIDENT emp.txt
  FORD, ANALYST emp.txt
  
  6 rows selected.

d, read mixed-mode example
  scott @ USBO> set serveroutput on;
  scott @ USBO> exec rw_demo; -> call to the procedure, the code tail See article
  14
  14
  28
  42
  56
  71
  84
  
  PL / SQL procedure successfully completed.
  
  scott @ USBO> ho ls
  out.txt x.txt
  
  scott @ USBO> ho more out.txt
  JONES, MANAGER
  JONES, MANAGER
  BLAKE, MANAGER
  CLARK, MANAGER
  SCOTT, ANALYST
  KING, PRESIDENT
  FORD, ANALYST

e, used in the demo process
  - The following is the process of reading mode code
  CREATE OR REPLACE PROCEDURE read_demo (file_name_in VARCHAR2, utl_dir_in VARCHAR2)
  - Passed two parameters, one is used to specify the file name, it is used to specify a directory utl_file_dir
  --Author: Leshami
  --Blog: Http://www.linuxidc.com
  IS
    vsfile UTL_FILE.file_type;
    vnewline VARCHAR2 (200);
    v_utl_dir VARCHAR2 (30);
  BEGIN
    v_utl_dir: = UPPER (utl_dir_in);
    vsfile: = UTL_FILE.fopen (v_utl_dir, file_name_in, 'r'); ---> Open File
  
    IF UTL_FILE.is_open (vsfile)
    THEN
        LOOP
          BEGIN
              UTL_FILE.get_line (vsfile, vnewline); -> read lines from file
  
              IF vnewline IS NULL
              THEN
                EXIT;
              END IF;
  
              INSERT INTO tb_emp (val, file_name) ---> will read rows into a table
                  VALUES (vnewline, file_name_in);
          EXCEPTION
              WHEN NO_DATA_FOUND
              THEN
                EXIT;
          END;
        END LOOP;
  
        COMMIT;
    END IF;
  
    UTL_FILE.fclose (vsfile); ---> close open files
    UTL_FILE.frename (v_utl_dir, ---> here renamed
                      file_name_in,
                      v_utl_dir,
                      'X.txt',
                      TRUE);
  EXCEPTION ---> defines the relevant exception information
    WHEN UTL_FILE.invalid_mode
    THEN
        raise_application_error (-20051, 'Invalid Mode Parameter');
    WHEN UTL_FILE.invalid_path
    THEN
        raise_application_error (-20052, 'Invalid File Location');
    WHEN UTL_FILE.invalid_filehandle
    THEN
        raise_application_error (-20053, 'Invalid Filehandle');
    WHEN UTL_FILE.invalid_operation
    THEN
        raise_application_error (-20054, 'Invalid Operation');
    WHEN UTL_FILE.read_error
    THEN
        raise_application_error (-20055, 'Read Error');
    WHEN UTL_FILE.internal_error
    THEN
        raise_application_error (-20057, 'Internal Error');
    WHEN UTL_FILE.charsetmismatch
    THEN
        raise_application_error (-20058, 'Opened With FOPEN_NCHAR
      But Later I / O Inconsistent ');
    WHEN UTL_FILE.file_open
    THEN
        raise_application_error (-20059, 'File Already Opened');
    WHEN UTL_FILE.invalid_maxlinesize
    THEN
        raise_application_error (-20060, 'Line Size Exceeds 32K');
    WHEN UTL_FILE.invalid_filename
    THEN
        raise_application_error (-20061, 'Invalid File Name');
    WHEN UTL_FILE.access_denied
    THEN
        raise_application_error (-20062, 'File Access Denied By');
    WHEN UTL_FILE.invalid_offset
    THEN
        raise_application_error (-20063, 'FSEEK Param Less Than 0');
    WHEN OTHERS
    THEN
        raise_application_error (-20099, 'Unknown UTL_FILE Error');
  END read_demo;
  /
  
  - Here is the code read-write mode process, the process to achieve a read from a file and write data to another data file
  CREATE OR REPLACE PROCEDURE rw_demo
  IS
    infile UTL_FILE.file_type;
    outfile UTL_FILE.file_type;
    vnewline VARCHAR2 (4000);
    i PLS_INTEGER;
    j PLS_INTEGER: = 0;
    seekflag BOOLEAN: = TRUE;
  BEGIN
    - Open a file to read
    infile: = UTL_FILE.fopen ( 'DB_UTL_DIR', 'x.txt', 'r'); -> open the source file for reading data
    - Open a file to write
    outfile: = UTL_FILE.fopen ( 'DB_UTL_DIR', 'out.txt', 'w'); -> to create the target file is used to store data
  
    - If the file to read was successfully opened
    IF UTL_FILE.is_open (infile)
    THEN
        - Loop through each line in the file
        LOOP
          BEGIN
              UTL_FILE.get_line (infile, vnewline); -> read from the source file lines
  
              i: = UTL_FILE.fgetpos (infile); -> the position of the line and output assignment
              DBMS_OUTPUT.put_line (TO_CHAR (i));
  
              UTL_FILE.put_line (outfile, vnewline, FALSE); -> The resulting rows of data written to a file handle buffer
              UTL_FILE.fflush (outfile); -> the data rows from the buffer is written to the file
  
              IF seekflag = TRUE
              THEN
                UTL_FILE.fseek (infile, NULL, -30); -> to adjust the file pointer, the offset
                seekflag: = FALSE;
              END IF;
          EXCEPTION
              WHEN NO_DATA_FOUND
              THEN
                EXIT;
          END;
        END LOOP;
  
        COMMIT;
    END IF;
  
    UTL_FILE.fclose (infile); -> Close the source file
    UTL_FILE.fclose (outfile); -> Close the target file
  EXCEPTION
    WHEN OTHERS
    THEN
        raise_application_error (-20099, 'Unknown UTL_FILE Error');
  END rw_demo;
  /
  
Note that when using UTL_FILE package that uses DIRECTORY database objects, the name must be capitalized, otherwise they will encounter "ORA-29280: invalid directory path" error
Main reference:
http://psoug.org/reference/utl_file.html http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#BABGGEDF

Linux, five kinds of dynamic library search path runtime methods:

As we all know, the default search path Linux dynamic library is / lib and / usr / lib. After the dynamic library is created, usually copied to both directories. When the program executes a required dynamic libraries and the DLL has not been loaded into memory, the system will automatically default to the two search path to find the appropriate DLL file and then load the file into memory so that the program you can use the dynamic library functions, and other resources that the dynamic library. In Linux, dynamic library search path in addition to the default search path, you can also specify the following three methods.

 

Method One: dynamic library search path specified in the configuration file /etc/ld.so.conf in.
 

/etc/ld.so.conf Can be specified dynamic library search path by editing the configuration file, which is a behavior for each dynamic library search path. Every time you finish editing the file, you must run the command ldconfig make the modified configuration take effect. Let's illustrate this method, as in Example 1.

example 1:

We (see procedure 1) to create a dynamic library libpos.so, please refer to the process of creating a detailed paper [1] by using the following command source pos_conf.c.

# Gcc -c pos_conf.c
      # Gcc -shared -fPCI -o libpos.so pos_conf.o
      #


#include
          void pos ()
          {
                  printf ( "/ root / test / conf / lib / n");
          }
      Program 1: pos_conf.c


Then the following command to compile main.c (see procedure 2) to generate the target program pos.

# Gcc -o pos main.c -L. -lpos
      #


void pos ();
          int main ()
          {
              pos ();
              return 0;
          }
      Program 2: main.c


Then move the library file to the directory / root / test / conf / lib in.

# Mkdir -p / root / test / conf / lib
      # Mv libpos.so / root / test / conf / lib
      #


Finally, edit the configuration file /etc/ld.so.conf, add an additional row "/ root / test / conf / lib" in the file.

Try to run the program pos.

# ./pos
        ./pos: error while loading shared libraries: libpos.so: can not open shared object file: No such file or directory
      #

Wrong, the system is not dynamic libraries libpos.so found. Find out why the original after you have finished editing the configuration file /etc/ld.so.conf, do not run the command ldconfig, so the changes just yet to take effect. We then try to run ldconfig.

# Ldconfig
      # ./pos / Root / test / conf / lib
      #

 

Pos program runs successfully, and prints out the correct result.

Method Two: LD_LIBRARY_PATH specified dynamic library search path environment variable.

By setting the environment variable LD_LIBRARY_PATH you can also specify a dynamic library search path. When you specify multiple dynamic library search path through the environment variable path between the colon ":" separator. By following the process described in Example 2.

Example 2:

We (see program 3) to create a dynamic library with the following command libpos.so source pos_env.c.

# Gcc -c pos_env.c
      # Gcc -shared -fPCI -o libpos.so pos_env.o
      #


#include
          void pos ()
          {
                printf ( "/ root / test / env / lib / n");
          }
      Program 3: pos_env.c


Pos test executable can use the procedure in Example 1 target pos obtained, without re-compilation. Because pos_conf.c the function pos and pos_env.c the function pos function prototype is consistent, and the same dynamic library name, which is like re-create dynamically modified as the library after library pos. This is also one of the advantages of the use of dynamic libraries.

Then the dynamic library libpos.so move to the directory / root / test / conf / lib in.

# Mkdir -p / root / test / env / lib
      # Mv libpos.so / root / test / env / lib
      #


We can use export to set the environment variable in all command sets the environment variable, the environment variable is valid.

E.g:

# Export LD_LIBRARY_PATH = / root / test / env / lib
      #

But this article for example convenience, use another method to set the environment variable, both Canadian environment variable before the command, the environment variable is only valid for the command, when the command execution is complete, the environment variable is invalid. Such as the following command:

# LD_LIBRARY_PATH = / root / test / env / lib ./pos / root / test / env / lib
      #

Pos program runs successfully, and the print result is "/ root / test / env / lib", is the result of running the program pos_env.c function of pos. So the program pos searched dynamic library is /root/test/env/lib/libpos.so.

Method three: a dynamic library search path when compiling the program in object code.

You can also specify a dynamic library search path to the program in object code compile time. This is done by gcc arguments "-Wl, -rpath," specified (as shown in Example 3). When you specify multiple dynamic library search path, the path between the colon ":" separator.

Example 3:

We use the following command source pos.c (see Procedure 4) to create a dynamic library libpos.so.

# Gcc -c pos.c
      # Gcc -shared -fPCI -o libpos.so pos.o
      #


#include
          void pos ()
          {
                printf ( ".// n");
          }
      Program 4: pos.c

Because we need to specify the dynamic library search path for executable files when compiling object code, you need to recompile the source code with the command gcc main.c (see procedure 2) to generate an executable file pos.

# Gcc -o pos main.c -L. -lpos -Wl, -rpath,. /
      #

And then try to run the program pos.

# ./pos ./
      #


Pos program runs successfully, the resulting output is the result of running the function in pos of pos.c. So the program pos searched dynamic library is ./libpos.so.

It introduces three above specified dynamic library search path method, with the default dynamic library search path / lib and / usr / lib, a total of five types of dynamic library search path, then they search for the order, what is it?

In introducing the above three methods, namely to create a dynamic library ./libpos.so, /root/test/env/lib/libpos.so and /root/test/conf/lib/libpos.so. We then source pos_lib.c (see program 5) to create a dynamic library /lib/libpos.so, with source pos_usrlib.c (see Program 6) to create a dynamic library /usr/lib/libpos.so.

#include
          void pos ()
          {
                  printf ( "/ lib / n");
          }
      Program 5: pos_lib.c


#include
          void pos ()
          {
                printf ( "/ usr / lib / n");
          }
      Program 6: pos_usrlib.c


Thus we get five dynamic library libpos.so, these dynamic libraries of the same name, and both contain the same function prototype public function pos. But the storage position and results of different utility functions of different print pos. Each dynamic library of common functions are pos output of the dynamic library is located. The results so we can Example 3 executable file to perform pos obtained different informed of its search to which dynamic library to obtain first a dynamic library search order, and then remove the DLL, and then execute the program pos, won the first two dynamic library search path, and then delete the first two dynamic library to be searched, and so forth, the order will be available Linux dynamic library search. Correspondence between the output pos program execution and dynamic library search as shown in Table 1:

Way dynamic library search path specified dynamic library program corresponding output using pos
Dynamic library search path ./libpos.so compiled object code specified ./
Dynamic library search path / root / test / env / lib /root/test/env/lib/libpos.so specified environment variable LD_LIBRARY_PATH
Dynamic library search path / root / test / conf / lib /root/test/conf/lib/libpos.so specified in the configuration file /etc/ld.so.conf
/ Lib /lib/libpos.so default dynamic library search path / lib
/ Usr / lib /usr/lib/libpos.so default dynamic library search path / usr / lib
Table 1: correspondence program pos output and dynamic libraries
Create various dynamic library and placed in the appropriate directory. Test environment is ready. Executive pos, and set the environment variable in the command line LD_LIBRARY_PATH.

# LD_LIBRARY_PATH = / root / test / env / lib ./pos ./
      #


The output indicates that the program pos we can see that the first search is a dynamic library search path specified compiled object code. We then dynamic libraries ./libpos.so delete, and then try to run the above command.


# Rm libpos.so
        rm: remove regular file `libpos.so 'y?
      # LD_LIBRARY_PATH = / root / test / env / lib ./pos / root / test / env / lib
      #


The output indicates that the program pos we can see that the first two dynamic library path search is specified by the environment variable LD_LIBRARY_PATH. We then /root/test/env/lib/libpos.so delete, run the above command.


# Rm /root/test/env/lib/libpos.so
        rm: remove regular file `/root/test/env/lib/libpos.so 'y?
      # LD_LIBRARY_PATH = / root / test / env / lib ./pos / root / test / conf / lib
      #

Search Path 3 dynamic library configuration file /etc/ld.so.conf specified path. Remove dynamic library /root/test/conf/lib/libpos.so then run the above command.


# Rm /root/test/conf/lib/libpos.so
        rm: remove regular file `/root/test/conf/lib/libpos.so 'y?
      # LD_LIBRARY_PATH = / root / test / env / lib ./pos / lib
      #


Chapter 4 Dynamic search path is the default library search path / lib. We then remove the dynamic library /lib/libpos.so, run the above command.


# Rm /lib/libpos.so
        rm: remove regular file `/lib/libpos.so 'y?
      # LD_LIBRARY_PATH = / root / test / env / lib ./pos / usr / lib
      #


Last dynamic library search path is the default search path / usr / lib.

Based on the above results, the search path for dynamic library search is the order:

1. dynamic library search path specified compiled object code;

2. dynamic library search path environment variable LD_LIBRARY_PATH specified;

3. dynamic library search path specified in the configuration file /etc/ld.so.conf;

4. The default dynamic library search path / lib;

5. The default dynamic library search path / usr / lib.

In the above 1,2,3 specify dynamic library search path can be specified multiple dynamic library search path, the search path specified in the order is the order of the search. This paper will not illustrated, the interested reader can verify reference methods herein.
     
         
       
         
  More:      
 
- Oracle 12C modify spfile path (Database)
- Python class of operator overloading (Programming)
- Install the latest development version of Wine on RedHat and Debian-based systems (Linux)
- Create the best introductory tutorial REST API (Linux)
- The Java utility, JavaMail (Programming)
- Jump table (skiplist) of code (Programming)
- grep search from files and display the file name (Linux)
- PostgreSQL procedural language learning (Database)
- Iptables command in detail (Linux)
- Use PuTTY key authentication mechanism for remote login Linux (Linux)
- iptraf: A Practical TCP / UDP network monitoring tools (Linux)
- shell script: a key to install LAMP, LNMP script (Server)
- Ubuntu and derivative system users how to install Pinta 1.5 (Linux)
- Java code JIT compiler-friendly Mody (Programming)
- 5 interesting Linux command line tips (Linux)
- How to write a new Git protocol (Linux)
- Java source implementation of the observer pattern instance (Programming)
- Use MySQL optimization of security to prevent misuse of aliases (Database)
- OpenSUSE installation on CentOS6 (GUI) (Linux)
- VMware difference in three network connection (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.