欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

使用oracle发邮件

程序员文章站 2024-02-18 21:18:10
...

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 RETURN L_FILE; END; ---------------------------------------------删除directory------------------------------------ PROCEDURE DROP_DIRECTORY(P_DIRECTORY_NAME VARCHAR2) IS BEGIN EXECUTE

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入

  RETURN L_FILE;

  END;

  ---------------------------------------------删除directory------------------------------------

  PROCEDURE DROP_DIRECTORY(P_DIRECTORY_NAME VARCHAR2) IS

  BEGIN

  EXECUTE IMMEDIATE 'drop directory ' || P_DIRECTORY_NAME;

  EXCEPTION

  WHEN OTHERS THEN

  NULL;

  END;

  --------------------------------------------------创建directory----------------------------------

  -------

  PROCEDURE CREATE_DIRECTORY(P_DIRECTORY_NAME VARCHAR2,

  P_DIR VARCHAR2) IS

  BEGIN

  EXECUTE IMMEDIATE 'create directory ' || P_DIRECTORY_NAME || ' as ''' ||

  P_DIR || '''';

  EXECUTE IMMEDIATE 'grant read,write on directory ' || P_DIRECTORY_NAME ||

  ' to public';

  EXCEPTION

  WHEN OTHERS THEN

  RAISE;

  END;

  --------------------------------------------分割邮件地址或者附件地址-----------------------------------

  PROCEDURE P_SPLITE_STR(P_STR VARCHAR2,

  P_SPLITE_FLAG INT DEFAULT 1) IS

  L_ADDR VARCHAR2(254) := '';

  L_LEN INT;

  L_STR VARCHAR2(4000);

  J INT := 0; --表示邮件地址或者附件的个数

  BEGIN

  /*处理接收邮件地址列表,包括去空格、将;转换为,等*/

  L_STR := TRIM(RTRIM(REPLACE(REPLACE(P_STR, ';', ','), ' ', ''), ','));

  L_LEN := LENGTH(L_STR);

  FOR I IN 1 .. L_LEN LOOP

  IF SUBSTR(L_STR, I, 1) ',' THEN

  L_ADDR := L_ADDR || SUBSTR(L_STR, I, 1);

  ELSE

  J := J + 1;

  IF P_SPLITE_FLAG = 1 THEN --表示处理邮件地址

  --前后需要加上'',否则很多邮箱将不能发送邮件

  L_ADDR := '';

  --调用邮件发送过程

  MY_ADDRESS_LIST(J) := L_ADDR;

  ELSIF P_SPLITE_FLAG = 2 THEN --表示处理附件名称

  MY_ACCT_LIST(J) := L_ADDR;

  END IF;

  L_ADDR := '';

  END IF;

  IF I = L_LEN THEN

  J := J + 1;

  IF P_SPLITE_FLAG = 1 THEN

  --调用邮件发送过程

  L_ADDR := '';

  MY_ADDRESS_LIST(J) := L_ADDR;

  ELSIF P_SPLITE_FLAG = 2 THEN

  MY_ACCT_LIST(J) := L_ADDR;

  END IF;

  END IF;

  END LOOP;

  END;

  ------------------------------------------------写邮件头和邮件内容------------------------------------------

  PROCEDURE WRITE_DATA(P_CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,

  P_NAME IN VARCHAR2,

  P_VALUE IN VARCHAR2,

  P_SPLITE VARCHAR2 DEFAULT ':',

  P_CRLF VARCHAR2 DEFAULT L_CRLF) IS

  BEGIN

  /* utl_raw.cast_to_raw 对解决中文乱码问题很重要*/

  UTL_SMTP.WRITE_RAW_DATA(P_CONN, UTL_RAW.CAST_TO_RAW(CONVERT(P_NAME ||

  P_SPLITE ||

  P_VALUE ||

  P_CRLF, 'ZHS16GBK')));

  END;

  ----------------------------------------写MIME邮件尾部-----------------------------------------------------

  PROCEDURE END_BOUNDARY(CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,

  LAST IN BOOLEAN DEFAULT FALSE) IS

  BEGIN

  UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);

  IF (LAST) THEN

  UTL_SMTP.WRITE_DATA(CONN, LAST_BOUNDARY);

  END IF;

  END;

  ----------------------------------------------发送附件----------------------------------------------------

  PROCEDURE ATTACHMENT(CONN IN OUT NOCOPY UTL_SMTP.CONNECTION,

  MIME_TYPE IN VARCHAR2 DEFAULT 'text/plain',

  INLINE IN BOOLEAN DEFAULT TRUE,

  FILENAME IN VARCHAR2 DEFAULT 't.txt',

  TRANSFER_ENC IN VARCHAR2 DEFAULT '7 bit',

  DT_NAME IN VARCHAR2 DEFAULT '0') IS

  L_FILENAME VARCHAR2(1000);

  BEGIN

  --写附件头

  UTL_SMTP.WRITE_DATA(CONN, FIRST_BOUNDARY);

  --设置附件格式

  WRITE_DATA(CONN, 'Content-Type', MIME_TYPE);

  --如果文件名称非空,表示有附件

  DROP_DIRECTORY(DT_NAME);

  --创建directory

  CREATE_DIRECTORY(DT_NAME, GET_FILE(FILENAME, 1));

  --得到附件文件名称

  L_FILENAME := GET_FILE(FILENAME, 2);

  IF (INLINE) THEN

  WRITE_DATA(CONN, 'Content-Disposition', 'inline; filename="' ||

  L_FILENAME || '"');

  ELSE

  WRITE_DATA(CONN, 'Content-Disposition', 'attachment; filename="' ||

  L_FILENAME || '"');

  END IF;

  --设置附件的转换格式

  IF (TRANSFER_ENC IS NOT NULL) THEN

  WRITE_DATA(CONN, 'Content-Transfer-Encoding', TRANSFER_ENC);

  END IF;

  UTL_SMTP.WRITE_DATA(CONN, UTL_TCP.CRLF);

  --begin 贴附件内容

  IF TRANSFER_ENC = 'bit 7' THEN

  --如果是文本类型的附件

  BEGIN

  L_FILE_HANDLE := UTL_FILE.FOPEN(DT_NAME, L_FILENAME, 'r'); --打开文件

  --把附件分成多份,这样可以发送超过32K的附件

  LOOP

  UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE);

  L_MESG := L_LINE || L_CRLF;

  WRITE_DATA(CONN, '', L_MESG, '', '');

  END LOOP;

  UTL_FILE.FCLOSE(L_FILE_HANDLE);

  END_BOUNDARY(CONN);

  EXCEPTION

  WHEN OTHERS THEN

  UTL_FILE.FCLOSE(L_FILE_HANDLE);

  END_BOUNDARY(CONN);

  NULL;

[1] [2] [3]

使用oracle发邮件