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)の取得
(2)host_によるname addressを取得するか、addressに基づいてnameを取得します.
3.create and drop acl
4.send email(utl_mailまたはutl_smtp)
(1)utl_mail
(2)utl_smtp
(3)utl_tcp
4.utl_http
'); 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/
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 := '[email protected]', ----- MAIL BOX SENDING THE EMAIL
msg_to varchar2 := '[email protected]', ----- 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/