关于在Oracle中读写文件的操作

在存储过程中，常常需要对操作系统文件进行读写操作，以前的处理方法是直接将数据库spfle文件中的utl_file_dir参数的值修改为相应的物理路径（默认为空）。

 

但此方法有几个局限性：
1、当物理路径因某种原因需要更改的时候，需要修改程序。
2、当有不同类型的应用都有文件要操作时，不易区分出来（放在一个文件夹中，不利于管理）。
3、经常修改spfile参数文件，显然也不是个好事情。

 

Oracle 提供的 Create directory功能使得我们可以在Oracle数据库中灵活的对文件进行读写操作，极大的提高了Oracle的易用性和可扩展性。

其语法为:

 

1.创建目录：
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';

 

2.目录创建以后，就可以把读写权限授予特定用户，具体语法如下:
GRANT READ[,WRITE] ON DIRECTORY directory TO username;

 

看一个简单的测试:

SQL> create or replacedirectory PIC_FILE_DIR as '/home1/oracle/pic_file';

SQL> grant read, write ondirectory PIC_FILE_DIR to piccmsuser;

Directory created.

SQL> declare
 2    fhandleutl_file.file_type;
  3  begin
 4    fhandle :=utl_file.fopen('PIC_FILE_DIR', 'example.txt', 'w');
 5   utl_file.put_line(fhandle , 'piccmsuser test write one');
 6   utl_file.put_line(fhandle , 'piccmsuser test write two');
 7   utl_file.fclose(fhandle);
  8  end;
  9  /

PL/SQL procedure successfullycompleted.

SQL> !
[oracle@localhost]$ more /home1/oracle/pic_file/example.txt
piccmsuser test write one
piccmsuser test write two
[oracle@localhost]$
 
 

类似的我们可以通过utl_file来读取文件:

SQL> declare
 2   fhandle  utl_file.file_type;
 3    fp_buffervarchar2(4000);
  4  begin
 5    fhandle :=utl_file.fopen ('PIC_FILE_DIR','example.txt', 'R');
  6 
 7   utl_file.get_line (fhandle , fp_buffer );
 8   dbms_output.put_line(fp_buffer );
 9   utl_file.get_line (fhandle , fp_buffer );
 10   dbms_output.put_line(fp_buffer );
 11   utl_file.fclose(fhandle);
 12  end;
 13  /
 
piccmsuser test write one
piccmsuser test write two

PL/SQL procedure successfullycompleted.
 

 

3.可以查询dba_directories查看所有directory.

SQL> select * fromdba_directories;

OWNER                         DIRECTORY_NAME                DIRECTORY_PATH
------------------------------ ------------------------------------------------------------
SYS                           PIC_FILE_DIR                  /home1/oracle/pic_file
SYS                           BDUMP_DIR                     /opt/oracle/admin/conner/bdump
SYS                           EXP_DIR                       /opt/oracle/utl_file

 

 

4.可以使用drop directory删除这些路径.

SQL> drop directoryPIC_FILE_DIR;

Directory dropped

SQL> select * fromdba_directories;

OWNER                         DIRECTORY_NAME                DIRECTORY_PATH
------------------------------ ------------------------------------------------------------
SYS                           BDUMP_DIR                     /opt/oracle/admin/conner/bdump
SYS                           EXP_DIR                       /opt/oracle/utl_file

例子：

CREATE OR REPLACE PROCEDURE read_demo(file_name in VARCHAR2,smtpserver_param out varchar2,user_param out varchar2,password_param out varchar2,address_param out varchar2) IS
vSFile   utl_file.file_type;
vLine    NUMBER(3);
vNewLine VARCHAR2(200);
BEGIN
--打开AUDIT_FILE_DIR目录下的file_name文件
  vSFile := utl_file.fopen('AUDIT_FILE_DIR', file_name,'R');
  vLine:=1;
--判断是否打开成功
  IF utl_file.is_open(vSFile) THEN
--循环读取文件内一行的内容
    LOOP
      BEGIN
        utl_file.get_line(vSFile, vNewLine);
        if vLine=1 then
          smtpserver_param:=vNewLine;
        end if;  
        if vLine=2 then
          user_param:=vNewLine;
        end if;
        if vLine=3 then
          password_param:=vNewLine;
        end if;  
        if vLine=4 then
          address_param:=vNewLine;
        end if;
        
      EXCEPTION
         WHEN others THEN
         --dbms_output.put_line('x');
         vNewLine :=null;
      END;   
        IF vNewLine IS NULL THEN
          EXIT;
        END IF;
        dbms_output.put_line(vNewLine);
        vLine:=vLine+1;
    END LOOP;

  END IF;
--关闭文件
 utl_file.fclose(vSFile);
--utl_file.frename('ORALOAD','test.txt','ORALOAD','x.txt',TRUE);
--捕获各种可能出现的异常，如：路径不正确，文件格式无法识别，文件打开失败等等
EXCEPTION
  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
    DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
    DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
   
END read_demo;
--oracle 发邮件
CREATE OR REPLACE PROCEDURE PRO_SEND_MAIL(p_recipient VARCHAR2,-- 邮件接收人
                                          p_subject   VARCHAR2,- 邮件标题 
                                          p_message   VARCHAR2-- 邮件正文
                                          ) IS

--下面四个变量请根据实际邮件服务器进行赋值

  -- v_mailhost VARCHAR2(30) :='smtp.126.com'; --SMTP服务器地址  
  -- v_user     VARCHAR2(30):= 'takusho'; --登录SMTP服务器的用户名；只是用户名，
 --  v_pass     VARCHAR2(20):= 'wuzhongdi'; --登录SMTP服务器的密码 
 --  v_sender   VARCHAR2(50):= 'takusho@126.com'; --发送都邮箱，一般与 ps_user对应  
 

    v_mailhost VARCHAR2(30) ;
    v_user     VARCHAR2(30) ;
    v_pass     VARCHAR2(20) ;
    v_sender   VARCHAR2(50) ;

    v_conn UTL_SMTP.connection;--到邮件服务器的连接  
    v_msg  varchar2(4000);--邮件内容 

BEGIN
    read_demo('application.txt',v_mailhost,v_user,v_pass,v_sender);  
    v_conn := UTL_SMTP.open_connection(v_mailhost, 25);
    --UTL_SMTP.ehlo(v_conn, v_mailhost);

    UTL_SMTP.helo(v_conn,v_mailhost); --是用 ehlo()而不是 helo() 函数  
    --否则会报：ORA-29279: SMTP永久性错误: 503 5.5.2 Send hellofirst.  
    --UTL_SMTP.helo(v_conn,v_mailhost);未验证

 

    --免费邮箱需要服务器登录校验 ；公司邮箱可能不需要校验

    UTL_SMTP.command(v_conn, 'AUTH LOGIN'); --smtp服务器登录校验 

    UTL_SMTP.command(v_conn,
                     UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));
    UTL_SMTP.command(v_conn,
                     UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));

    UTL_SMTP.mail(v_conn, '<' || v_sender || '>'); --设置发件人
    UTL_SMTP.rcpt(v_conn, '<' || p_recipient || '>'); --设置收件人
-- 创建要发送的邮件内容注意报头信息和邮件正文之间要空一行
    v_msg :=  'MIME-Version: 1.0' ||
             UTL_TCP.CRLF ||  'Date:' || TO_CHAR(SYSDATE, 'dd mon yy hh24:mi:ss') ||
             UTL_TCP.CRLF || 'From: ' || v_sender || '<' || v_sender || '>' ||
         --    UTL_TCP.CRLF || 'From: MOL SYSTEM' || v_sender || '<' || v_sender || '>' ||
             UTL_TCP.CRLF || 'To: ' || p_recipient || '<' || p_recipient || '>' ||
             UTL_TCP.CRLF || 'Subject: ' || p_subject || UTL_TCP.CRLF ||
             UTL_TCP.CRLF || -- 这前面是报头信息 

            /* UTL_TCP.CRLF ||'dd: '||
             UTL_TCP.CRLF ||'　　nh！'||

             UTL_TCP.CRLF ||'　　2132'||'　　　　　'||'4343'||*/
             UTL_TCP.CRLF || UTL_TCP.CRLF || p_message ; --这个是邮件正文

/*
             UTL_TCP.CRLF || UTL_TCP.CRLF || UTL_TCP.CRLF ||
             UTL_TCP.CRLF ||'2123332！'||
             UTL_TCP.CRLF ||'　　　　　　　　　　　　　　　　　　32:'||TO_CHAR(SYSDATE, 'YYYY/MM/DD hh24:mi:ss')||
             UTL_TCP.CRLF ||UTL_TCP.CRLF ||'345454545';*/


    UTL_SMTP.open_data(v_conn); --打开流

    UTL_SMTP.write_raw_data(v_conn, UTL_RAW.cast_to_raw(v_msg)); --这样写标题和内容都能用中文（数据库必须支持中文，否则还是不行）
    
    UTL_SMTP.close_data(v_conn); --关闭流 

    UTL_SMTP.quit(v_conn); --关闭连接 


EXCEPTION

    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
        DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);

END PRO_SEND_MAIL;