Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PL / SQL -> UTL_FILE use presentation package     - Docker use Dockerfile created since the launch of the service support SSH container mirror (Server)

- Python Django direct implementation of sql statement (Programming)

- How to troubleshoot error java.lang.NoSuchMethodError (Programming)

- Linux system security audit tools scan nessus installation tutorial (Linux)

- Linux user status query, and to unlock locked user (Linux)

- Manually create Oracle Database Explanations (Database)

- Vim Getting Started Tutorial (Linux)

- PostgreSQL query result area is removed and precision (Database)

- Configuring the PXE server in Ubuntu 14.04 (Server)

- Linux netstat command to get started (Linux)

- Log in CentOS 6.5 Multi-user setting VNC (Server)

- Linux Nginx installation and configuration instructions (Server)

- Ubuntu 14.04 Trusty Tahr User How to install Banshee 2.9.1 (Linux)

- Ubuntu 14.04 LTS to compile the source code Android4.4.2 (Linux)

- How to turn Java String into Date (Programming)

- PHP parsing algorithm of the interview questions (Programming)

- ORA-00845: MEMORY_TARGET not supported on this system Problem (Database)

- LAN Deployment Docker-- from scratch to create your own private warehouse Docker (Linux)

- Nginx high concurrency optimization ideas (Server)

- Read and write files efficiently from Apache Kafka (Server)

 
         
  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:      
 
- Configuring DNS process under CentOS 6.5 (Server)
- Partition contrast manifestations under Windows and Linux (Linux)
- Install the latest ATI / Nvidia graphics driver on Ubuntu (Linux)
- GO five stages of language learning (Programming)
- Oracle 11g RMAN cross-platform transfer table space (Database)
- Let OpenCV face detection score output codes (Programming)
- How to install Linux Go Language (Linux)
- What happens after the MySQL disk space is full (Database)
- Linux common network tools: batch scanning of hosting services netcat (Linux)
- Linux LVM Logical Volume Management to resize partitions (Linux)
- To disable the function by pressing Ctrl-Alt-Del to restart the system in RHEL / CentOS 5/6 (Linux)
- Applications Linux leap second problem caused (Linux)
- Bash difference in single quotes and double quotes (Programming)
- Linux System Getting Started Learning: In RedHat Linux driver compiled Ixgbe (Linux)
- Four Methods of Self - Learning Linux (Linux)
- Linux simple commands (Linux)
- Linux Mint brightness adjustment --xrandr command learning (Linux)
- Linux (CentOS) SSH login without password authentication (Linux)
- MySQL to recover the data through binlog (Database)
- Intel Graphics Installer 1.0.3 released, support for Ubuntu (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.