Oracle内建包UTL_FILE,UTL_HTTP,UTL_SMTP,UTL_TCP使用说明

原创

mb649d3a75b51a2 2023-07-28 16:14:13 ©著作权

文章标签 file oracle tcp exception integer 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者mb649d3a75b51a2的原创作品，请联系作者获取转载授权，否则将追究法律责任

REVOKE EXECUTE ON UTL_FILE FROM PUBLIC;

REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;

REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC;

REVOKE EXECUTE ON UTL_TCP FROM PUBLIC;

包UTL_FILE 提供了在操作系统层面上对文件系统中文件的读写功能。非超级用户在使用包UTL_FILE中任何函数或存储过程前必须由超级用户授予在这个包上的EXECUTE权限。例如：我们使用下列命令对用户mary进行授权：

GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TO mary;

在引用文件的时候，要使用到一个文件句柄，来表示对文件的读或写。文件句柄是通过包UTL_FILE中名称为UTL_FILE.FILE_TYPE的公有变量来定义的。我们必须声明一个类型为FILE_TYPE的变量来接收通过函数FOPEN返回的文件句柄。这个文件句柄将用于随后在文件上的所有操作。

对于文件系统上目录的引用是通过使用目录名称，或者由CREATE DIRECTORY命令为目录分配的化名来实现的。

UTL_FILE常用方法：

FOPEN
IS_OPEN
GET_LINE
PUT
NEW_LINE
PUT_LINE
PUTF
FFLUSH
FCLOSE
FCLOSE_ALL

FRENAME

FREMOVE

FFLUSH

FCOPY

UTL_FILE.FOPEN 用法

适用范围：

函数原型：

FUNCTION UTL_FILE.FOPEN (      FUNCTION UTL_FILE.FOPEN (
    location     IN VARCHAR2,      location     IN VARCHAR2,
    filename     IN VARCHAR2,      filename     IN VARCHAR2,
    open_mode    IN VARCHAR2)      open_mode    IN VARCHAR2,
RETURN file_type;                 max_linesize IN BINARY_INTEGER)

参数
   location    文件地址
   filename    文件名
   openmode    打开文件的模式（参见下面说明）

              3种文件打开模式：
              R 只读模式。一般配合UTL_FILE的GET_LINE来读文件。

PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用

PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用
max_linesize 文件每行最大的字符数，包括换行符。最小为1，最大为32767。

打开文件时注意以下几点：
   *   文件路径和文件名合起来必须表示操作系统中一个合法的文件。
   *   文件路径必须存在并可访问；FOPEN并不会新建一个文件夹。
   *   如果你想打开文件进行读操作，文件必须存在；如果你想打开文件进行写操作，文件不存在时，会新建一个文件。
   *

*   FOPEN 会抛出以下异常
            UTL_FILE.INVALID_MODE
            UTL_FILE.INVALID_OPERATION
            UTL_FILE.INVALID_PATH

UTL_FILE.IS_OPEN用法
如果文件句柄指定的文件已打开，返回TRUE，否则FALSE

函数原型：

UTL_FILE.GET_LINE用法

函数原型：
PROCEDURE UTL_FILE.GET_LINE (file IN UTL_FILE.FILE_TYPE,

参数说明：

file 由FOPEN返回的文件句柄
buffer 读取的一行数据的存放缓存

buffer必须足够大。否则，会抛出VALUE_ERROR 异常。行终止符不会被传进buffer。

异常：
    NO_DATA_FOUND
    VALUE_ERROR
    UTL_FILE.INVALID_FILEHANDLE
    UTL_FILE.INVALID_OPERATION
    UTL_FILE.READ_ERROR

UTL_FILE.PUT用法

函数原型：

参数说明：
file 由FOPEN返回的文件句柄
buffer

UTL_FILE.PUT输出数据时不会附加行终止符。

异常：
    UTL_FILE.INVALID_FILEHANDLE
    UTL_FILE.INVALID_OPERATION

UTL_FILE.NEW_LINE

函数原型：

参数说明：
file 由FOPEN返回的文件句柄
lines 要插入的行数

注意：

   如果不指定lines参数，NEW_LINE会使用默认值1，在当前行尾换行。如果要插入一个空白行，可以使用以下语句：
      UTL_FILE.NEW_LINE (my_file, 2);

异常：

   VALUE_ERROR
    UTL_FILE.INVALID_FILEHANDLE
    UTL_FILE.INVALID_OPERATION
    UTL_FILE.WRITE_ERROR
例子：
如果要在UTL_FILE.PUT后立刻换行，可以如下例所示：
PROCEDURE add_line (file_in IN UTL_FILE.FILE_TYPE, line_in IN VARCHAR2)
IS
BEGIN
   UTL_FILE.PUT (file_in, line_in);
   UTL_FILE.NEW_LINE (file_in);
END;

UTL_FILE.PUT_LINE

函数原型：

参数说明：
file 由FOPEN返回的文件句柄
buffer 包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B
注意：

在调用UTL_FILE.PUT_LINE前，必须先打开文件。
异常：
   UTL_FILE.INVALID_FILEHANDLE
   UTL_FILE.INVALID_OPERATION

例子：
这里利用UTL_FILE.PUT_LINE从表emp读取数据到文件：
PROCEDURE emp2file
IS
   fileID UTL_FILE.FILE_TYPE;
BEGIN
   fileID := UTL_FILE.FOPEN ('/tmp', 'emp.dat', 'W');


   FOR emprec IN (SELECT * FROM emp)
   LOOP
      UTL_FILE.PUT_LINE
         (TO_CHAR (emprec.empno) || ',' ||
          emprec.ename || ',' ||
          ...
          TO_CHAR (emprec.deptno));
   END LOOP;

   UTL_FILE.FCLOSE (fileID);
END;
PUT_LINE相当于PUT后加上NEW_LINE；也相当于PUTF的格式串"%s\n"。

UTL_FILE.PUTF

函数原型：

PROCEDURE UTL_FILE.PUTF
    (file IN FILE_TYPE
    ,format IN VARCHAR2
    ,arg1 IN VARCHAR2 DEFAULT NULL
    ,arg2 IN VARCHAR2 DEFAULT NULL
    ,arg3 IN VARCHAR2 DEFAULT NULL
    ,arg4 IN VARCHAR2 DEFAULT NULL

参数说明：
    file   由FOPEN返回的文件句柄
    format   决定格式的格式串
    argN

格式串可使用以下样式
%s

%s会被后面的参数依次填充，如果没有足够的参数，%s会被忽视，不被写入文件
\n

异常：
    UTL_FILE.INVALID_FILEHANDLE
    UTL_FILE.INVALID_OPERATION

UTL_FILE.FFLUSH

函数原型：
PROCEDURE UTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE);
参数说明：

file

注意：

   操作系统可能会缓存数据来提高性能。因此可能调用put后，打开文件却看不到写入的数据。在关闭文件前要读取数据的话可以使用UTL_FILE.FFLUSH。
    典型的使用方法包括分析执行进度和调试纪录。
异常：
    UTL_FILE.INVALID_FILEHANDLE
    UTL_FILE.INVALID_OPERATION

UTL_FILE.FCLOSE

函数原型：

参数说明：
file

注意：

异常：
UTL_FILE.INVALID_FILEHANDLE

UTL_FILE.FCLOSE_ALL

原型：

在结束程序时要确保所有打开的文件已关闭，可使用FCLOSE_ALL
也可以在EXCEPTION使用，当异常退出时，文件也会被关闭。
EXCEPTION
   WHEN OTHERS

THEN
      UTL_FILE.FCLOSE_ALL;
      ... other clean up activities ...
END;

注意：

当使用FCLOSE_ALL关闭所有文件时，文件句柄并不会标记为NULL，使用IS_OPEN会返回TRUE。但是，那些关闭的文件不能执行读写操作（除非你再次打开文件）。
异常：

UTL_FILE.FCOPY

函数原型：

FCOPY(location VARCHAR2, filename VARCHAR2,dest_dir VARCHAR2, dest_file VARCHAR2[, start_line PLS_INTEGER [, end_line

参数

location

filename

dest_dir

dest_file

start_line

end_line

示例：

CREATE DIRECTORY empdir AS 'C:/TEMP/EMPDIR'; DECLARE v_empfile UTL_FILE.FILE_TYPE; v_src_dir VARCHAR2(50) := 'empdir'; v_src_file VARCHAR2(20) := 'empfile.csv'; v_dest_dir VARCHAR2(50) := 'empdir'; v_dest_file VARCHAR2(20) := 'empcopy.csv'; v_emprec VARCHAR2(120); v_count INTEGER := 0; BEGIN UTL_FILE.FCOPY(v_src_dir,v_src_file,v_dest_dir,v_dest_file); v_empfile := UTL_FILE.FOPEN(v_dest_dir,v_dest_file,'r'); DBMS_OUTPUT.PUT_LINE('The following is the destination file, ''' || v_dest_file || ''''); LOOP UTL_FILE.GET_LINE(v_empfile,v_emprec); DBMS_OUTPUT.PUT_LINE(v_emprec); v_count := v_count + 1; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE(v_empfile); DBMS_OUTPUT.PUT_LINE(v_count || ' records retrieved'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE); END; The following is the destination file, 'empcopy.csv' 7369,SMITH,CLERK,7902,17-DEC-80,800,,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30 7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30 7566,JONES,MANAGER,7839,02-APR-81,2975,,20 7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30 7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30 7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10 7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20 7839,KING,PRESIDENT,,17-NOV-81,5000,,10 7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30 7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20 7900,JAMES,CLERK,7698,03-DEC-81,950,,30 7902,FORD,ANALYST,7566,03-DEC-81,3000,,20 7934,MILLER,CLERK,7782,23-JAN-82,1300,,10 14 records retrieved

UTL_FILE.FFLUSH

存储过程FFLUSH强制缓冲区中未写入磁盘的内容写到磁盘文件中，并将缓冲区的内容清空。

函数原型：FFLUSH(file FILE_TYPE)

参数：

file 包含一个文件句柄的变量，类型为FILE_TYPE。

示例：

调用存储过程NEW_LINE后，将缓冲区中的每一行记录强制写到磁盘中。

DECLARE v_empfile UTL_FILE.FILE_TYPE; v_directory VARCHAR2(50) := 'empdir'; v_filename VARCHAR2(20) := 'empfile.csv'; CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno; BEGIN v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'w'); FOR i IN emp_cur LOOP UTL_FILE.PUT(v_empfile,i.empno); UTL_FILE.PUT(v_empfile,','); UTL_FILE.PUT(v_empfile,i.ename); UTL_FILE.PUT(v_empfile,','); UTL_FILE.PUT(v_empfile,i.job); UTL_FILE.PUT(v_empfile,','); UTL_FILE.PUT(v_empfile,i.mgr); UTL_FILE.PUT(v_empfile,','); UTL_FILE.PUT(v_empfile,i.hiredate); UTL_FILE.PUT(v_empfile,','); UTL_FILE.PUT(v_empfile,i.sal); UTL_FILE.PUT(v_empfile,','); UTL_FILE.PUT(v_empfile,i.comm); UTL_FILE.PUT(v_empfile,','); UTL_FILE.PUT(v_empfile,i.deptno); UTL_FILE.NEW_LINE(v_empfile); UTL_FILE.FFLUSH(v_empfile); END LOOP; DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename); UTL_FILE.FCLOSE(v_empfile); END;

UTL_FILE.FREMOVE
存储过程FREMOVE用于从系统中删除一个文件。
函数原型：

FREMOVE(location VARCHAR2, filename VARCHAR2)

如果要删除的文件不存在，那么会产生一个异常。
参数：
 location
目录名称，存放在pg_catalog.edb_dir.dirname中，这个目录包含要删除的文件。
 filename
要删除文件的名称。
示例：
下面的示例删除了文件 empfile.csv：

DECLARE v_directory VARCHAR2(50) := 'empdir'; v_filename VARCHAR2(20) := 'empfile.csv'; BEGIN UTL_FILE.FREMOVE(v_directory,v_filename); DBMS_OUTPUT.PUT_LINE('Removed file: ' || v_filename); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE); END; Removed file: empfile.csv

UTL_FILE.FRENAME

存储过程FRENAME修改一个文件的名称，这样我们可以把一个文件从一个位置移动到另外一个位置。

函数原型：

FRENAME(location VARCHAR2, filename VARCHAR2, dest_dir VARCHAR2, dest_file VARCHAR2, [ overwrite BOOLEAN ])

参数：
 location
目录名称，存放在pg_catalog.edb_dir.dirname中，这个目录包含要改名的文件。
 filename
要改名的源文件名称。
 dest_dir
目录名称，存放在pg_catalog.edb_dir.dirname中，这个目录是被改名文件所在的目录。
 dest_file
原始文件的新名称。
 overwrite
如果设置为”true”,在dest_dir目录中覆盖任何名为dest_file的文件。若设置为”false”，就会产生异常。这是缺省情况。
示例：
下面我们将文件C:\TEMP\EMPDIR\empfile.csv重新命名，这个文件包含一个逗号分隔的列表，内容是表emp中雇员的信息。然后列出重新命名后的文件 C:\TEMP\NEWDIR\newemp.csv的内容。

CREATE DIRECTORY "newdir" AS 'C:/TEMP/NEWDIR'; DECLARE v_empfile UTL_FILE.FILE_TYPE; v_src_dir VARCHAR2(50) := 'empdir'; v_src_file VARCHAR2

==============

http://oracle.chinaitlab.com/exploiture/842965.html

在oracle中通过utl_http获得指定网页内容

SET serveroutput ON SIZE 40000
　　DECLARE
　　v_text varchar2(100) := '试验%S已ok';--不能有空白
　　req    utl_http.req;
　　resp   utl_http.resp;
　　value  VARCHAR2(1000);
　　BEGIN
　　v_text := replace(v_text,'%','%25');
　　v_text := replace(utl_encode.text_encode(v_text), '=', '%');
　　req    := utl_http.begin_request('http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q=' ||
　　v_text || '&langpair=zh|en');
　　resp   := utl_http.get_response(req);
　　LOOP
　　utl_http.read_line(resp, value, TRUE);
　　value := replace(value,'% S','%S');
　　value := replace(value,'{"responseData": {"translatedText":"','');
　　value := replace(value,'"}, "responseDetails": null, "responseStatus": 200}','');
　　dbms_output.put_line(value);
　　END LOOP;
　　utl_http.end_response(resp);
　　EXCEPTION
　　WHEN utl_http.end_of_body THEN
　　utl_http.end_response(resp);
　　END;

=-================

关于Oracle的UTL_TCP

（在Oracle9.2下，link：http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_tcp.htm#ARPLS075） With the UTL_TCP package and its procedures and functions, PL/SQL applications can communicate with external TCP/IP-based servers using TCP/IP. Because many Internet application protocols are based on TCP/IP, this package is useful to PL/SQL applications that use Internet protocols and e-mail. （通过使用UTL_TCP包和他的过程与函数，PL/SQL程序能够使用TCP/IP协议与其他的基于TCP/IP的服务器进行通信。这个包对于使用网络和email服务的PL/SQL程序非常有用，因为许多网络服务程序协议都是基于TCP/IP协议的。） Oracle的UTL_TCP包提供了一种额外的方法来主动与应用程序来进行通信。比如我们可以通过一个触发器，当某张表的数据被更改时，即时通知应用程序基础的通知应用程序服务，当然，这个应用程序服务需要开监听。写了个小测试东西来测试了下UTL_TCP： 1）以下是一个PL/SQL过程，使用UTL_TCP包，与ip为xxx.xxx.xxx.xxx:1234进行通信。只是简单的发送"Result has changed":

1: CREATE OR REPLACE procedure USERID.P_SendNotice  2: IS
  3:     conn utl_tcp.connection;
  4:     ret_val pls_integer; 
  5: Begin
  6:     conn := utl_tcp.open_connection(remote_host  => 'xxx.xxx.xxx.xxx', 
  7:                                     remote_port  => 1234,  8:                                     charset => 'US7ASCII');
  9:                                     
 10:     ret_val:=utl_tcp.write_line(conn, 'Result has changed');
 11:     dbms_output.put_line(to_char(ret_val));
 12: EXCEPTION
 13:     when utl_tcp.NETWORK_ERROR then
 14:     dbms_output.put_line('network error');
 15: End;

2）在一张表上建个触发器，以便对该表数据进行更改时候，发送通知消息给外部服务器：

1: CREATE OR REPLACE TRIGGER USERID.noticesend_trg
  2:    AFTER INSERT OR UPDATE OR DELETE
  3:    ON USERID.TableName  REFERENCING NEW AS NEW OLD AS OLD
  4: BEGIN
  5:    P_SendNotice ();
  6: END;

3）在应用程序中，应该监听我在上面包中所写ip为xxx.xxx.xxx.xxx的1234端口，以完成对相应消息的接收至此，完成一个简单的Oracle UTL_TCP的小应用。当表数据中发生改变的时候，通过触发器来调用过程，发送通知给应用服务器，应用服务器再做相应的相应。 UTL_TCP的限制：The UTL_TCP package provides TCP/IP client-side access functionality in PL/SQL. The API provided in the package only allows connections to be initiated by the PL/SQL program. It does not allow the PL/SQL program to accept connections initiated outside the program.

==============

http://biancheng.dnbcw.info/oracle/204191.html

使用utl_smtp从Oracle中发送电子邮件

假如你目前运行的是Oracle10g或更高的版本，那么你现在就可以用新的utl_mail工具包从你的PL/SQL应用程序来发送电子邮件了。（注释：如果你的应用程序使构建在Oracle 8i的基础之上，前提是运行维护在Oracle 10g或以上版本，那么你也可以通过旧的utl_smtp工具包来发送电子邮件。）

utl_smtp代码的一个优点就是可以在Oracle 10g上正常运行，所以我们不需要用utl_mail来替代utl_smtp工具包。虽然utl_mail完全替代utl_smtp的一天勿庸置疑会降临，不过现在utl_smtp还能够满足我们的需求。

首先，确认utl_smtp工具包已经安装在你的系统里(当然是在SYS架构里)。如果你还没有安装这个工具包，可以在你的ORACLE_HOME\RDBMS\admin文件夹中找到utlsmtp.sql脚本。你还需要utl_tcp包;同样的，如果你发现utl_tcp包还没有加载，也可以从跟utlsmtp.sql脚本相同的路径找到utltcp.sql脚本。最后，你还需要知道你的企业SMTP服务器的URL。(注意，下面的例子不适用于对SMTP进行了安全设置的服务器，如Gmail)

程序包说明语句：

以下是引用片段：

create or replace PACKAGE sendmail IS 
procedure send (p_sender varchar2, 
p_recipient varchar2, 
p_subject varchar2, 
p_body varchar2 default null); 
end sendmail;

仔细观察以上语句的主体和下面的程序包，你可能会发现send的公共模式(public method)依赖于被称为common的私有模式(private method)，这样以后我们可以对这个程序包进行扩展，并为大家展示怎样发送二进制大对象(blob)附件。例如，如果你生成了一份PDF文档，并把它保存在你的数据库里，你可以会想要把它作为电子邮件的附件发送出去， common模式就是用来完成这个任务的。将要用到的代码来自于基本send模式和send_blob模式。

以下就是程序包的主体：

以下是引用片段：

create or replace PACKAGE BODY sendmail IS 
procedure common (p_sender varchar2, 
p_recipient varchar2, 
p_subject varchar2, 
c out utl_smtp.connection) is 
v_recipient varchar2(1000); 
begin 
--make connection to smtp 
c := utl_smtp.open_connection('smtp.example.com'); 
--identify the domain of the sender 
utl_smtp.helo(c, 'example.com'); 
--start a ma

使用utl_smtp从Oracle中发送电子邮件第2部分：

il, specify the sender 
utl_smtp.mail(c, p_sender); 
--identify recipient 
utl_smtp.rcpt(c, v_recipient); 
--start the mail body 
utl_smtp.open_data(c); 
utl_smtp.write_data(c, 'From: ' || p_sender || utl_tcp.crlf); 
utl_smtp.write_data(c, 'To: ' || p_recipient || utl_tcp.crlf); 
utl_smtp.write_data(c, 'Subject: ' || p_subject || utl_tcp.crlf); 
exception 
when utl_smtp.transient_error or utl_smtp.permanent_error then 
utl_smtp.quit(c); 
raise; 
when others then 
raise; 
end common; 
procedure send (p_sender varchar2, 
p_recipient varchar2, 
p_subject varchar2, 
p_body varchar2 default null) is 
c utl_smtp.connection; 
begin 
common(p_sender, p_recipient, p_subject, c); 
utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf); 
utl_smtp.write_data(c, utl_tcp.crlf || p_body); 
utl_smtp.close_data(c); 
utl_smtp.quit(c); 
exception 
when utl_smtp.transient_error or utl_smtp.permanent_error then 
utl_smtp.quit(c); 
raise; 
when others then 
raise; 
end send; 
end sendmail;

上面的这个程序里有些地方是需要用具体的信息来替代的。第一个是提供SMTP服务器的地方，需要添加你自己要添加的任何企业STMP服务器：

--make connection to smtp
　　c := utl_smtp.open_connection('smtp.example.com');

　　其次是需要验证域的地方，需要用你的确切域名来替代：

--identify the domain of

使用utl_smtp从Oracle中发送电子邮件第3部分：

the sender 
　　utl_smtp.helo(c, 'example.com');

以上就是获取基本的电子邮件功能所需要的所有程序，你可以通过以下的语句来调用此程序：

下面是引用的实例片段：

begin 
sendmail.send ('sender@example.com', 
'recipient@example.com', 
'Subject: Hello', 
'How about your trip?'); 
end;

你可以发现上面在邮件的主体部分(body)的字符串嵌入了超文本编辑模式效果(字体为粗体)。这是因为前面在send模式下将内容类型设置为text/html：

utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);

注意：请不要把chr(10)作为换行符嵌入到带格式的电子邮件中发送出去，因为HTML模式会把它忽略掉。你可以使用break或段落标签来改变信息主体的格式。

上一篇：担保债务凭证(Collateralized Debt Obligation，简称CDO) CLO/CBO

下一篇：如何使用Proxy模式及Java内建的动态代理机制

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯