PLSQL NOTE------network(実行権限、acl、テスト)

16692 ワード

1.utl_http,utl_tcp,utl_inaddr,utl_mail,utl_smtpの付与権限
grant execute on &utl_privilege to &username;
2.utl_inaddrの使用
(1)ローカルホスト名(host_name)とアドレス(host_address)の取得
 SET serveroutput on
 BEGIN
   DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);  -- get local host name
   DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);  -- get local IP addr
 END;
 /

(2)host_によるname addressを取得するか、addressに基づいてnameを取得します.
  UTL_INADDR.GET_HOST_ADDRESS ( host  IN VARCHAR2 DEFAULT NULL) 
  RETURN host_address VARCHAR2; 
  --host       The name of the host to retrieve the IP address.
  --host_address     The IP address of the specified host, or that of the local host if host is NULL.
  
  
  UTL_INADDR.GET_HOST_NAME ( ip  IN VARCHAR2 DEFAULT NULL)
 RETURN host_name VARCHAR2;
  --ip     The IP address of the host used to determine its host name. If ip is not NULL, the official name of the host with its domain name is returned. 
  --If this is NULL, the name of the local host is returned and the name does not contain the domain to which the local host belongs.
 
 --host_name        The name of the local or remote host of the specified IP address.

 3.create and drop acl
  declare
  l_acl varchar2(400) := ‘utl_mail.xml’;
  l_username varchar2(400) := 'victor';
 l_host varchar2(400) := '*';
  begin
    dbms_network_acl_admin.create_acl (
      acl         =>l_acl,
     description => 'Allow mail to be send',
      principal   => l_username,
      is_grant    => TRUE,
      privilege   => 'connect'
      );
  
    dbms_network_acl_admin.add_privilege (
    acl       =>l_acl,
    principal => l_username,
    is_grant  => TRUE,
    privilege => 'resolve'
    );
  
    dbms_network_acl_admin.assign_acl(
    acl  => l_acl,
    host => l_host
    );
   end;
 /
 declare
 l_acl varchar2(400) := 'utl_smtp.xml';
 begin
 dbms_network_acl_admin.drop_acl(
    l_acl
   );
 end;
 /

4.send email(utl_mailまたはutl_smtp)
(1)utl_mail
DECLARE
   l_mail_to_list VARCHAR2 (4000);
   l_mail_cc_list VARCHAR2 (4000);
   crlf        VARCHAR2(2)  := chr(13)||chr(10);
 BEGIN
   l_mail_to_list  := 'sender.mail_address';
   l_mail_cc_list  := 'receiver1.mail_address,receiver2.mail_address';
   EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
   UTL_MAIL.send(sender => l_mail_to_list,
                 recipients => l_mail_cc_list,
                 subject => 'test for send mail by utl_mail',
                 message => 'Hi All,'||crlf||crlf
                               ||'this is test email ,please do not reply to it!'||crlf||crlf
                              ||'Thanks!'||crlf,
                 mime_type => 'text; charset=us-ascii');
 EXCEPTION
  WHEN others then
    raise_application_error(-20000, 'Unable to send mail');
 END;
 /
 show errors;

(2)utl_smtp
 
 declare
     TYPE Varchar2Array FORCE IS VARRAY (2147483647) OF VARCHAR2 (32767) ;
     p_from VARCHAR:= 'XXX@XXX';
     p_to VarcharArray := VarcharArray('XXX@XXX','XXX@XXX');
     p_subject VARCHAR2 := 'just for at&t plsql test';
     p_message VARCHAR2 := 'Date:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||CRLF
                 ||'Dear All,'||CRLF
                 ||'This is plsql test mail, please DO NOT Reply this mail.'||CRLF
                 ||'But, would you please tell me the succee on skype !'||CRLF
                 ||'Thanks'||CRLF
                 ||'victor';
     l_session utl_smtp.Connection;
     l_i INTEGER ;
     l_msg VARCHAR2(32767);
     CRLF VARCHAR2(2):= chr(13)||chr(10);
     username varchar2(400) := 'username';
     pass varchar2(400) := 'password';
     host varchar2(200) := 'www.baidu.com';
     port integer := 25;
 BEGIN
     l_session  := utl_smtp.Open_Connection(host,port);
     utl_smtp.HELO(l_session,host);
     UTL_SMTP.command(v_conn, 'AUTH LOGIN');            
   UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username))));
   UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(pass))));
     utl_smtp.Mail(l_session, '<'||p_from||'>');
     l_i := 0;
     WHILE l_i  < p_to.count
     LOOP
       BEGIN
           utl_smtp.Rcpt(l_session, '<'||p_to(l_i+1)||'>');
       END;
         l_i  := l_i  + 1;
     END LOOP;
     UTL_SMTP.OPEN_DATA(l_session);
     l_msg := 'Date:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||CRLF; 
 l_msg := l_msg||'From: "sender"' ||'<'||p_from||'>'||CRLF; 
 for i in 1..p_to.count 
 LOOP 
 l_msg := l_msg||'TO: "Recipient"'||'<'||p_to(i)||'>'||CRLF; 
 END LOOP; 
 l_msg := l_msg||'Subject: ' ||p_subject||CRLF; 
 l_msg := l_msg||'Content-Type: text/plain'; 
 UTL_SMTP.write_raw_data(l_session, utl_raw.cast_to_raw(l_msg)); 
 UTL_SMTP.write_raw_data(l_session, utl_raw.cast_to_raw(CRLF||p_message)); 
 UTL_SMTP.close_data(l_session); 
 UTL_SMTP.quit(l_session); 
 EXCEPTION 
 WHEN others THEN 
 BEGIN 
 dbms_output.put_line ('send_message error: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 
 dbms_output.put_line ('send_message error: '||sqlerrm ); 
 END;
 END; 
 /

 
 
 (3)utl_tcp
CREATE OR REPLACE PROCEDURE SEND_MAIL (
   msg_from    varchar2 := 'EMAILADDRESS@DOMAIN.COM',    ----- MAIL BOX SENDING THE EMAIL
  msg_to      varchar2 := 'EMAILADDRESS@DOMAIN.COM',    ----- MAIL BOX RECIEVING THE EMAIL
   msg_subject varchar2 := 'Output file TEST1',          ----- EMAIL SUBJECT
   msg_text    varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
   v_output1   varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT WILL BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
 IS
   c  utl_tcp.connection;
   rc integer;
   crlf VARCHAR2(2):= CHR(13)||CHR(10);
   mesg VARCHAR2(32767);
 BEGIN
   c := utl_tcp.open_connection('196.35.140.18', 25);       ----- OPEN SMTP PORT CONNECTION
   rc := utl_tcp.write_line(c, 'HELO 196.35.140.18');       ----- PERFORMS HANDSHAKING WITH SMTP SERVER
   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
   rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18');       ----- PERFORMS HANDSHAKING, INCLUDING EXTRA INFORMATION
   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
   rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);    ----- MAIL BOX SENDING THE EMAIL
   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
   rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);        ----- MAIL BOX RECIEVING THE EMAIL
   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
   rc := utl_tcp.write_line(c, 'DATA');                     ----- EMAIL MESSAGE BODY START
   dbms_output.put_line(utl_tcp.get_line(c, TRUE));
   rc := utl_tcp.write_line(c, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
   rc := utl_tcp.write_line(c, 'From: '||msg_from||' <'||msg_from||'>');
   rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
   rc := utl_tcp.write_line(c, 'To: '||msg_to||' <'||msg_to||'>');
   rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
   rc := utl_tcp.write_line(c, 'Content-Type: multipart/mixed;');  ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
   rc := utl_tcp.write_line(c, ' boundary="-----SECBOUND"');       ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
   rc := utl_tcp.write_line(c, );                                ----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD
   rc := utl_tcp.write_line(c, '-------SECBOUND'); 
   rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); ----- 1ST BODY PART. EMAIL TEXT MESSAGE 
   rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit'); 
   rc := utl_tcp.write_line(c, ); 
   rc := utl_tcp.write_line(c, msg_text); ----- TEXT OF EMAIL MESSAGE 
   rc := utl_tcp.write_line(c, ); 
   rc := utl_tcp.write_line(c, '-------SECBOUND'); 
   rc := utl_tcp.write_line(c, 'Content-Type: text/plain;'); ----- 2ND BODY PART. 
   rc := utl_tcp.write_line(c, ' name="Test.txt"'); 
   rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit'); 
   rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT 
   rc := utl_tcp.write_line(c, ' filename="Test.txt"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT 
   rc := utl_tcp.write_line(c, ); 
   rc := utl_tcp.write_line(c, v_output1); 
   rc := utl_tcp.write_line(c, '-------SECBOUND--'); 
   rc := utl_tcp.write_line(c, ); 
   rc := utl_tcp.write_line(c, '.'); ----- EMAIL MESSAGE BODY END 
   dbms_output.put_line(utl_tcp.get_line(c, TRUE)); 
   rc := utl_tcp.write_line(c, 'QUIT'); ----- ENDS EMAIL TRANSACTION 
   dbms_output.put_line(utl_tcp.get_line(c, TRUE)); 
   utl_tcp.close_connection(c); ----- CLOSE SMTP PORT CONNECTION 
   END; 
   /

 4.utl_http
 1 create or replace function sendHttpPost
 2 (p_url in varchar2, request_clob in CLOB, timeout integer := 1000 ) 
 3 return CLOB is
 4     l_http_request   UTL_HTTP.req;
 5     l_http_response  UTL_HTTP.resp;
 6     l_buffer_size constant integer := 512;
 7     l_lines_count constant integer := 20;
 8     l_line_size constant integer :=  50;
 9     l_clob_response clob := empty_clob();
10     l_line           VARCHAR2(128);
11     l_substring_msg  VARCHAR2(512);
12     l_raw_data       RAW(512);
13     function get_host(url in varchar2) return varchar2 is 
14         doubleslash integer;
15         endidx integer;
16         portidx integer;
17         l_host_name varchar2(32767);
18         l_port varchar2(32767);
19     begin
20         doubleslash := instr(url, '//', 1, 1) ;
21         doubleslash := case doubleslash when 0 then 1 else doubleslash+2 end;
22         endidx := instr(url, '/', doubleslash, 1);
23         endidx := case endidx when 0 then length(url)+1 else endidx end;
24         l_host_name := substr(url, doubleslash, endidx-doubleslash);
25         return l_host_name;
26     end;   
27 begin
28     DBMS_OUTPUT.put_line('*********************send http request start');
29     UTL_HTTP.SET_TRANSFER_TIMEOUT(timeout);
30     DBMS_OUTPUT.put_line('*********************send request');
31     l_http_request := UTL_HTTP.begin_request(url => p_url, method => 'POST', http_version => 'HTTP/1.1');
32     UTL_HTTP.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0');
33     UTL_HTTP.set_header(l_http_request, 'Host', get_host(p_url));
34     UTL_HTTP.set_header(l_http_request, 'Connection', 'close');
35     UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset=UTF-8');
36     UTL_HTTP.set_header(l_http_request, 'SOAPAction', '"GetContractInvSmryReqst"');
37     UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(request_clob));
38 
39      <<request_loop>>
40     FOR i IN 0..CEIL(LENGTH(request_clob) / l_buffer_size) - 1 LOOP
41         l_substring_msg := SUBSTR(request_clob, i * l_buffer_size + 1, l_buffer_size);
42         BEGIN
43             l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
44             UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
45             EXCEPTION
46                 WHEN NO_DATA_FOUND THEN
47                     EXIT request_loop;
48         END;
49     END LOOP request_loop;
50     l_http_response := UTL_HTTP.get_response(l_http_request);
51     DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');
52     DBMS_OUTPUT.put_line('Response> Status: ' || l_http_response.Status_code);
53     DBMS_OUTPUT.put_line('Response> Reason Phrase: ' || l_http_response.reason_phrase);
54     BEGIN
55         <<response_loop>>
56         LOOP
57             UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
58             l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
59         END LOOP response_loop;
60         EXCEPTION
61             WHEN UTL_HTTP.end_of_body THEN
62                 UTL_HTTP.end_response(l_http_response);
63         END;
64     DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
65     DBMS_OUTPUT.put_line(CHR(10) || '=== Print first ' || l_lines_count || ' lines of HTTP response... ===' || CHR(10) || CHR(10));
66  
67 <<print_response>>
68  FOR i IN 0..CEIL(LENGTH(l_clob_response) / l_line_size) - 1 LOOP
69        l_line := SUBSTR(l_clob_response, i * l_line_size + 1, l_line_size);
70        DBMS_OUTPUT.put_line('[' || LPAD(i, 2, '0') || ']: ' || l_line);
71         EXIT WHEN i > l_lines_count - 1;
72     END LOOP print_response;
73  IF l_http_request.private_hndl IS NOT NULL THEN
74         UTL_HTTP.end_request(l_http_request);
75     END IF;
76     IF l_http_response.private_hndl IS NOT NULL THEN
77         UTL_HTTP.end_response(l_http_response);
78     END IF;
79     DBMS_OUTPUT.put_line('************************ start print response');
80     printXML(l_clob_response);
81     return l_clob_response ;
82 end;
83 /
84 show errors;

 
'); 33 rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); ----- 1ST BODY PART. EMAIL TEXT MESSAGE 34 rc := utl_tcp.write_line(c, 'Content-Transfer-Encoding: 7bit'); 35 rc := utl_tcp.write_line(c, ); 36 rc := utl_tcp.write_line(c, msg_text); ----- TEXT OF EMAIL MESSAGE 37 rc := utl_tcp.write_line(c, ); 38 rc := utl_tcp.write_line(c, '-------SECBOUND'); 39 rc := utl_tcp.write_line(c, 'Content-Type: text/plain;'); ----- 2ND BODY PART. 40 rc := utl_tcp.write_line(c, ' name="Test.txt"'); 41 rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit'); 42 rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT 43 rc := utl_tcp.write_line(c, ' filename="Test.txt"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT 44 rc := utl_tcp.write_line(c, ); 45 rc := utl_tcp.write_line(c, v_output1); 46 rc := utl_tcp.write_line(c, '-------SECBOUND--'); 47 rc := utl_tcp.write_line(c, ); 48 rc := utl_tcp.write_line(c, '.'); ----- EMAIL MESSAGE BODY END 49 dbms_output.put_line(utl_tcp.get_line(c, TRUE)); 50 rc := utl_tcp.write_line(c, 'QUIT'); ----- ENDS EMAIL TRANSACTION 51 dbms_output.put_line(utl_tcp.get_line(c, TRUE)); 52 utl_tcp.close_connection(c); ----- CLOSE SMTP PORT CONNECTION 53 END; 54/