ITPub博客

首页 > Linux操作系统 > Linux操作系统 > File I/O with Oracle PL/SQL[akadia]

File I/O with Oracle PL/SQL[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-07-05 19:00:07 0 删除 编辑

The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).

Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed. Accessible directories must be specified in the instance parameter initialization file (INIT.ORA).

Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter. For example:

UTL_FILE_DIR =

If the initialization file for the instance contains the line UTL_FILE_DIR = /u01/oracle/db, then the directory /u01/oracle/db is accessible to the FOPEN function. Note that a directory named /u01/oracle/DB would not be accessible on case-sensitive operating systems.

The parameter specification UTL_FILE_DIR = * has a special meaning. This entry turns off directory access checking, and it makes any directory accessible to the UTL_FILE functions.

The ' * ' option should be used with great caution. Oracle does not recommend that you use this option in production systems. Also, do not include ' . ' (the current directory for UNIX) in the accessible directories list. To ensure security on file systems that enable symbolic links, users must not be allowed WRITE permission to directories accessible by PL/SQL file I/O functions. The symbolic links and PL/SQL file I/O could be used to circumvent normal operating system permission checking and allow users read/write access to directories to which they would not otherwise have access.

Here is a simple example

CREATE OR REPLACE
PROCEDURE file_io_with_plsql IS
l_fhd utl_File.File_Type;
l_fdir v$parameter.value%Type;
l_fname VARCHAR2(30) := 'myfile.log';
l_fmode VARCHAR2(1) := 'W';
l_buf VARCHAR2(100);
BEGIN
--
-- Open file for write to location 'utl_file_dir'
-- specified in v$parameter
--

BEGIN
SELECT value
INTO l_fdir
FROM v$parameter
WHERE name = 'utl_file_dir';
IF (l_fdir IS NULL) THEN
RAISE NO_DATA_FOUND;
END IF;
l_fhd := utl_File.FOpen(l_fdir,l_fname,l_fmode);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(
-20101,'ERROR: file_io_with_plsql(): utl_File.FOpen()'
);
END;
--
-- Write some text to the file
--

utl_file.Putf(l_fhd,'Hello World ...');
utl_file.FFlush(l_fhd);
utl_file.FClose(l_fhd);
--
-- Read the text back from file
--

l_fmode := 'R';
l_fhd := utl_File.FOpen(l_fdir,l_fname,l_fmode);
utl_file.Get_Line(l_fhd,l_buf);
dbms_output.put_line('Readed from OS-file: ' || l_buf);
utl_file.FClose(l_fhd);
END file_io_with_plsql;
/

SQL> set serveroutput on;
SQL> exec file_io_with_plsql;

Readed from OS-file: Hello World ...
PL/SQL procedure successfully completed.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60591/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    240
  • 访问量
    172298