do something

2022年5月27日星期五

Oracle SQL取日期

當月第一天 select trunc(sysdate, ‘mm’) from dual

當年第一天 select trunc(sysdate,‘yy’) from dual

當前年月日 select trunc(sysdate,‘dd’) from dual

當年第一天 select trunc(sysdate,‘yyyy’) from dual

當前星期的第一天（也就是星期天） select trunc(sysdate,‘d’) from dual

當前日期 select trunc(sysdate) from dual

當前時間（準確到小時） select trunc(sysdate, ‘hh’) from dual

當前時間（準確到分鐘） select to_char(trunc(sysdate, ‘mi’),‘yyyy-MM-dd HH:mm:ss’) from dual

前一天的日期 select TRUNC(SYSDATE - 1) from dual

前一個月的日期 select add_months(trunc(sysdate),-1) from dual

後一個月的日期 select add_months(trunc(sysdate),1) from dual

本月最後一天 select to_char(last_day(sysdate), ‘yyyy-mm-dd’) from dual

2022年5月19日星期四

utl_smtp發送Mail

CREATE OR REPLACE PROCEDURE send_mail (

p_to IN VARCHAR2,

p_cc IN VARCHAR2,

p_from IN VARCHAR2,

p_mail_subject IN VARCHAR2,

p_message IN VARCHAR2,

p_smtp_host IN VARCHAR2,

p_smtp_port IN NUMBER DEFAULT 25,

p_mime_type IN VARCHAR2 DEFAULT NULL,

p_directory IN VARCHAR2 DEFAULT NULL,

p_attach IN VARCHAR2 DEFAULT NULL )

/********************************

* 使用範例:

* BEGIN

* apex_ws_erp.send_mail(p_to => 'kn@mail.com.tw',

* p_cc => kn@mail.com.tw;kn@mail.com.tw',

* p_from => 'admin@mail.com.tw',

* p_mail_subject => 'Subject test',

* p_message => 'This is Test msessagehello world哈囉你好嗎',

* p_smtp_host => 'localhost',

* p_mime_type => 'text/plain',

* p_directory => 'DUMP_CSV_DIR',

* p_attach => 'test.csv');

* END;

l_mail_conn utl_smtp.connection;

CRLF varchar2(2) := CHR( 13 ) || CHR( 10 );

--utl_smtp.write_data 用來放置一般資訊

--utl_smtp.write_raw_data 用來放置 16 進位資訊, 也是中文字必須轉換成 16 進位才可正常顯示

file_handle bfile;

file_exists boolean;

block_size number;

file_len number;

pos number;

total number;

read_bytes number;

data raw(200);

loopcount number;

att_count number;

BEGIN

-- 開啟 Mail Connection 物件

l_mail_conn := utl_smtp.open_connection(p_smtp_host, p_smtp_port);

-- 建立連線

utl_smtp.helo(l_mail_conn, p_smtp_host);

-- 設定寄件者

utl_smtp.mail(l_mail_conn, p_from);

-- 設定收件者

--utl_smtp.rcpt(l_mail_conn, p_to);

-- 設定多個收件者

FOR r1 IN (SELECT NVL(REGEXP_SUBSTR(p_to||';'||p_cc, '[^;]+', 1, LEVEL, 'i'), 'NULL') AS mail_to

FROM DUAL

CONNECT BY LEVEL <= LENGTH(p_to||';'||p_cc) - LENGTH(REGEXP_REPLACE(p_to||';'||p_cc, ';', ''))+1)

LOOP

IF r1.mail_to <> 'NULL' THEN

utl_smtp.rcpt(l_mail_conn, r1.mail_to);

END IF;

END LOOP;

-- 設定發信內容

utl_smtp.open_data(l_mail_conn);

-- 寫入 Mail Header

utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Subject:'||p_mail_subject|| CRLF));

utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('To:'||p_to|| CRLF));

utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Cc:'||p_cc|| CRLF));

utl_smtp.write_data(l_mail_conn, 'MIME-Version: 1.0' || CRLF );

utl_smtp.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="gcboundary0p"'|| CRLF|| CRLF);

IF p_message IS NOT NULL THEN

utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html; charset=utf-8/big5' || CRLF );

utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: 8bit' || CRLF );

utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_message) );

utl_smtp.write_data(l_mail_conn, '' || CRLF );

END IF;

IF p_message IS NOT NULL THEN

utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF);

utl_smtp.write_data(l_mail_conn, 'Content-Type: text/html; charset="utf-8"' || CRLF || CRLF);

--utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: 8bit' || CRLF );

utl_smtp.write_raw_data(l_mail_conn, utl_raw.cast_to_raw(p_message) );

utl_smtp.write_data(l_mail_conn, CRLF || CRLF);

END IF;

dbms_output.put_line('Starting attachment segment');

dbms_output.put_line('Directory: '||p_directory);

dbms_output.put_line('AttachList: '||p_attach);

-- MAIL附件

BEGIN

IF p_attach IS NOT NULL THEN

loopcount := 0;

loopcount := loopcount +1;

dbms_output.put_line('Attaching: '||p_directory||'/'||p_attach);

utl_file.fgetattr(p_directory, p_attach, file_exists, file_len, block_size);

IF file_exists THEN

dbms_output.put_line('Getting mime_type for the attachment');

utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

utl_smtp.write_data(l_mail_conn, 'Content-Type: '||p_mime_type ||'; name="' || p_attach || '"' || CRLF );

utl_smtp.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || CRLF );

utl_smtp.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="'||p_attach||'"' || CRLF );

utl_smtp.write_data(l_mail_conn, CRLF );

file_handle := bfilename(p_directory, p_attach);

pos := 1;

total := 0;

file_len := dbms_lob.getlength(file_handle);

dbms_lob.open(file_handle, dbms_lob.lob_readonly);

LOOP

IF pos + 57 - 1 > file_len THEN

read_bytes := file_len - pos + 1;

--dbms_output.put_line('Last read - Start: '||pos);

ELSE

--dbms_output.put_line('Reading - Start: '||pos);

read_bytes := 57;

END IF;

total := total + read_bytes;

dbms_lob.read(file_handle, read_bytes, pos, data);

utl_smtp.write_raw_data(l_mail_conn, utl_encode.base64_encode(data));

pos := pos + 57;

IF pos > file_len THEN

EXIT;

END IF;

END LOOP;

dbms_output.put_line('Length was '||file_len);

dbms_lob.close(file_handle);

IF (loopcount < att_count) then

utl_smtp.write_data(l_mail_conn, CRLF );

utl_smtp.write_data(l_mail_conn, '--gcboundary0p'||CRLF );

ELSE

utl_smtp.write_data(l_mail_conn, CRLF );

utl_smtp.write_data(l_mail_conn, '--gcboundary0p--'||CRLF );

dbms_output.put_line('Writing end boundary');

END IF;

ELSE

dbms_output.put_line('Skipping: '||p_directory||'/'||p_attach||'Does not exist.');

END IF;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('Error Message：'||sqlerrm);

END;

-- 結束連線

utl_smtp.close_data(l_mail_conn);

utl_smtp.quit(l_mail_conn);

END;

2022年4月27日星期三

[Oracle]fnd_global.apps_initialize ORA-02074

Oracle APEX 程式透過DB Link呼叫EBS Package, Package裡有使用fnd_profile.VALUE ('user_id') .但因為不是由EBS呼叫的程式, EBS的USER_ID會是空的, 為了不異動原本程式, 故在呼叫此Package前會先執行fnd_global.apps_initialize.

作法如下:

1. 在EBS產生一個Procedure:

PROCEDURE SET_APPS_INITIAL (p_user_id IN NUMBER, p_resp_id IN NUMBER, p_resp_appl_id IN NUMBER)
IS
BEGIN
fnd_global.apps_initialize(NVL(p_user_id,0),
NVL(p_resp_id,0),
NVL(p_resp_appl_id,0)
);
END;

2. APEX程式透過DB Link呼叫此Procedure, 並傳入EBS User ID:

APPS.SET_APPS_INITIAL@DB_LINK_NAME (

:EBS_USER_ID,

:EBS_RESP_ID,

:EBS_RESP_APPL_ID);

3. APEX程式再呼叫原本要用的EBS Package:

APPS.OM_CUST.UPD_DELIVERY@DB_LINK_NAME(:DELIVERY_NAME);

----------------------------------------------------------------------------------------------------------

至此第一次執行沒問題, 但要開始處理第二筆資料時出現錯誤:

ORA-20001: Oracle error -20001: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN'). has been detected in fnd_global.set_nls. ORA-06512: at "APPS.APP_EXCEPTION", line 72 ORA-06512: at "APPS.FND_GLOBAL", line 271 ORA-06512: at "APPS.FND_GLOBAL", line 1575 ORA-06512: at "APPS.FND_GLOBAL", line 2006 ORA-06512: at "APPS.FND_GLOBAL", line 2607 ORA-06512: at "APPS.FND_GLOBAL", line 2759 ORA-06512: ORA-06512: at "APEX_210200.WWV_FLOW_CODE_EXEC_PLSQL", line 124 ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 2417 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 682 ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 2378 ORA-06512: at "APEX_210200.WWV_FLOW_CODE_EXEC_PLSQL", line 96 ORA-06512: at "APEX_210200.WWV_FLOW_CODE_EXEC_PLSQL", line 301 ORA-06512: at "APEX_210200.WWV_FLOW_CODE_EXEC", line 340

找了很久終於在這查到問題原因及解法:

this issue happens when executing FND_GLOBAL.apps_initialize more than once within a trigger/via a db link in the same transaction.

http://oracle-apps-dba.blogspot.com/2008/05/fndglobal-affected-by-new-global.html

於是參考第二種作法(APEX沒有用到分散式處理應該)修改了SET_APPS_INITIAL Procedure:

PROCEDURE SET_APPS_INITIAL (p_user_id IN NUMBER, p_resp_id IN NUMBER, p_resp_appl_id IN NUMBER)
IS

pragma autonomous_transaction;
BEGIN
fnd_global.apps_initialize(NVL(p_user_id,0),
NVL(p_resp_id,0),
NVL(p_resp_appl_id,0)
);

COMMIT;
END;

執行了幾次並沒有再出現ora-02074, 也有取到設定的EBS User ID.

後續測試活動再繼續觀察了...

PS.pragma autonomous_transaction;說明可參考其他:

https://tomkuo139.blogspot.com/2009/11/oracle-plsql-pragma-autonomoustransacti.html

2022年4月21日星期四

Oracle UTL_FILE產生檔案

DECLARE

file_open utl_file.file_type;

v_file_path varchar2(200);

v_file_name varchar2(200);

BEGIN

--v_file_path := '/u01/app/oracle/product/19.3.0/dbhome_1/csv_file';

v_file_path := 'DUMP_CSV_DIR';

v_file_name := 'test.csv';

-- 建立 File Object

file_open := UTL_FILE.FOPEN(v_file_path

, v_file_name

, 'W'

);

-- 寫入資料並換行

UTL_FILE.PUT_LINE(file_open, 'Hello1哈囉一');

UTL_FILE.PUT_LINE(file_open, 'Hello2哈囉二');

UTL_FILE.PUT_LINE(file_open, 'Hello3哈囉三');

-- 寫入資料但不換行

for i in 0..9 loop

UTL_FILE.PUT( file_open, to_char(i) );

end loop;

-- 釋放物件

UTL_FILE.FCLOSE(file_open);

END;

Oracle utl_smtp Attachment utl_file

https://community.oracle.com/tech/developers/discussion/3954846/when-send-mail-with-pdf-file-attachment-using-utl-smtp-cant-open-the-pdf

CREATE OR REPLACE package send_email as

function get_mime_type(FileName in varchar2) return varchar2;

function split(p_list in varchar2, p_del in varchar2 := ',') return split_tbl pipelined;

procedure send (

Tolist in varchar2,

Bcc in varchar2 := null,

Subject in varchar2,

Body in varchar2,

FromEmail in varchar2 := 'DoNotReply@Nowhere.Com',

SmtpServer in varchar2 := 'mailhost',

SmtpPort in number := 25,

AttachList in varchar2 := null,

Directory in varchar2 := null

);

end send_email;

CREATE OR REPLACE package body send_email as

function get_mime_type(FileName in varchar2) return varchar2 is

type mime_type_arr is table of varchar2(250) index by varchar2(20);

mime_types mime_type_arr;

mime_type varchar2(250);

extension varchar2(250);

begin

-- Populate the mime_types array

mime_types('323') := 'text/h323';

mime_types('acx') := 'application/internet-property-stream';

mime_types('ai') := 'application/postscript';

mime_types('aif') := 'audio/x-aiff';

mime_types('aifc') := 'audio/x-aiff';

mime_types('aiff') := 'audio/x-aiff';

mime_types('asf') := 'video/x-ms-asf';

mime_types('asr') := 'video/x-ms-asf';

mime_types('asx') := 'video/x-ms-asf';

mime_types('au') := 'audio/basic';

mime_types('avi') := 'video/x-msvideo';

mime_types('axs') := 'application/olescript';

mime_types('bas') := 'text/plain';

mime_types('bcpio') := 'application/x-bcpio';

mime_types('bin') := 'application/octet-stream';

mime_types('bmp') := 'image/bmp';

mime_types('c') := 'text/plain';

mime_types('cat') := 'application/vnd.ms-pkiseccat';

mime_types('cdf') := 'application/x-cdf';

mime_types('cer') := 'application/x-x509-ca-cert';

mime_types('class') := 'application/octet-stream';

mime_types('clp') := 'application/x-msclip';

mime_types('cmx') := 'image/x-cmx';

mime_types('cod') := 'image/cis-cod';

mime_types('cpio') := 'application/x-cpio';

mime_types('crd') := 'application/x-mscardfile';

mime_types('crl') := 'application/pkix-crl';

mime_types('crt') := 'application/x-x509-ca-cert';

mime_types('csh') := 'application/x-csh';

mime_types('css') := 'text/css';

mime_types('csv') := 'text/plain';

mime_types('dcr') := 'application/x-director';

mime_types('der') := 'application/x-x509-ca-cert';

mime_types('dir') := 'application/x-director';

mime_types('dll') := 'application/x-msdownload';

mime_types('dms') := 'application/octet-stream';

mime_types('doc') := 'application/msword';

mime_types('docx') := 'application/msword';

mime_types('dot') := 'application/msword';

mime_types('dvi') := 'application/x-dvi';

mime_types('dxr') := 'application/x-director';

mime_types('eps') := 'application/postscript';

mime_types('etx') := 'text/x-setext';

mime_types('evy') := 'application/envoy';

mime_types('exe') := 'application/octet-stream';

mime_types('fif') := 'application/fractals';

mime_types('flr') := 'x-world/x-vrml';

mime_types('gif') := 'image/gif';

mime_types('gtar') := 'application/x-gtar';

mime_types('gz') := 'application/x-gzip';

mime_types('h') := 'text/plain';

mime_types('hdf') := 'application/x-hdf';

mime_types('hlp') := 'application/winhlp';

mime_types('hqx') := 'application/mac-binhex40';

mime_types('hta') := 'application/hta';

mime_types('htc') := 'text/x-component';

mime_types('htm') := 'text/html';

mime_types('html') := 'text/html';

mime_types('htt') := 'text/webviewhtml';

mime_types('ico') := 'image/x-icon';

mime_types('ief') := 'image/ief';

mime_types('iii') := 'application/x-iphone';

mime_types('ins') := 'application/x-internet-signup';

mime_types('isp') := 'application/x-internet-signup';

mime_types('jfif') := 'image/pipeg';

mime_types('jpe') := 'image/jpeg';

mime_types('jpeg') := 'image/jpeg';

mime_types('jpg') := 'image/jpeg';

mime_types('js') := 'application/x-javascript';

mime_types('latex') := 'application/x-latex';

mime_types('lha') := 'application/octet-stream';

mime_types('lsf') := 'video/x-la-asf';

mime_types('lsx') := 'video/x-la-asf';

mime_types('lzh') := 'application/octet-stream';

mime_types('m13') := 'application/x-msmediaview';

mime_types('m14') := 'application/x-msmediaview';

mime_types('m3u') := 'audio/x-mpegurl';

mime_types('man') := 'application/x-troff-man';

mime_types('mdb') := 'application/x-msaccess';

mime_types('me') := 'application/x-troff-me';

mime_types('mht') := 'message/rfc822';

mime_types('mhtml') := 'message/rfc822';

mime_types('mid') := 'audio/mid';

mime_types('mny') := 'application/x-msmoney';

mime_types('mov') := 'video/quicktime';

mime_types('movie') := 'video/x-sgi-movie';

mime_types('mp2') := 'video/mpeg';

mime_types('mp3') := 'audio/mpeg';

mime_types('mpa') := 'video/mpeg';

mime_types('mpe') := 'video/mpeg';

mime_types('mpeg') := 'video/mpeg';

mime_types('mpg') := 'video/mpeg';

mime_types('mpp') := 'application/vnd.ms-project';

mime_types('mpv2') := 'video/mpeg';

mime_types('ms') := 'application/x-troff-ms';

mime_types('mvb') := 'application/x-msmediaview';

mime_types('nws') := 'message/rfc822';

mime_types('oda') := 'application/oda';

mime_types('p10') := 'application/pkcs10';

mime_types('p12') := 'application/x-pkcs12';

mime_types('p7b') := 'application/x-pkcs7-certificates';

mime_types('p7c') := 'application/x-pkcs7-mime';

mime_types('p7m') := 'application/x-pkcs7-mime';

mime_types('p7r') := 'application/x-pkcs7-certreqresp';

mime_types('p7s') := 'application/x-pkcs7-signature';

mime_types('pbm') := 'image/x-portable-bitmap';

mime_types('pdf') := 'application/pdf';

mime_types('pfx') := 'application/x-pkcs12';

mime_types('pgm') := 'image/x-portable-graymap';

mime_types('pko') := 'application/ynd.ms-pkipko';

mime_types('pma') := 'application/x-perfmon';

mime_types('pmc') := 'application/x-perfmon';

mime_types('pml') := 'application/x-perfmon';

mime_types('pmr') := 'application/x-perfmon';

mime_types('pmw') := 'application/x-perfmon';

mime_types('pnm') := 'image/x-portable-anymap';

mime_types('pot,') := 'application/vnd.ms-powerpoint';

mime_types('ppm') := 'image/x-portable-pixmap';

mime_types('pps') := 'application/vnd.ms-powerpoint';

mime_types('ppt') := 'application/vnd.ms-powerpoint';

mime_types('prf') := 'application/pics-rules';

mime_types('ps') := 'application/postscript';

mime_types('pub') := 'application/x-mspublisher';

mime_types('qt') := 'video/quicktime';

mime_types('ra') := 'audio/x-pn-realaudio';

mime_types('ram') := 'audio/x-pn-realaudio';

mime_types('ras') := 'image/x-cmu-raster';

mime_types('rgb') := 'image/x-rgb';

mime_types('rmi') := 'audio/mid';

mime_types('roff') := 'application/x-troff';

mime_types('rtf') := 'application/rtf';

mime_types('rtx') := 'text/richtext';

mime_types('scd') := 'application/x-msschedule';

mime_types('sct') := 'text/scriptlet';

mime_types('setpay') := 'application/set-payment-initiation';

mime_types('setreg') := 'application/set-registration-initiation';

mime_types('sh') := 'application/x-sh';

mime_types('shar') := 'application/x-shar';

mime_types('sit') := 'application/x-stuffit';

mime_types('snd') := 'audio/basic';

mime_types('spc') := 'application/x-pkcs7-certificates';

mime_types('spl') := 'application/futuresplash';

mime_types('src') := 'application/x-wais-source';

mime_types('sst') := 'application/vnd.ms-pkicertstore';

mime_types('stl') := 'application/vnd.ms-pkistl';

mime_types('stm') := 'text/html';

mime_types('svg') := 'image/svg+xml';

mime_types('sv4cpio') := 'application/x-sv4cpio';

mime_types('sv4crc') := 'application/x-sv4crc';

mime_types('swf') := 'application/x-shockwave-flash';

mime_types('t') := 'application/x-troff';

mime_types('tar') := 'application/x-tar';

mime_types('tcl') := 'application/x-tcl';

mime_types('tex') := 'application/x-tex';

mime_types('texi') := 'application/x-texinfo';

mime_types('texinfo') := 'application/x-texinfo';

mime_types('tgz') := 'application/x-compressed';

mime_types('tif') := 'image/tiff';

mime_types('tiff') := 'image/tiff';

mime_types('tr') := 'application/x-troff';

mime_types('trm') := 'application/x-msterminal';

mime_types('tsv') := 'text/tab-separated-values';

mime_types('txt') := 'text/plain';

mime_types('uls') := 'text/iuls';

mime_types('ustar') := 'application/x-ustar';

mime_types('vcf') := 'text/x-vcard';

mime_types('vrml') := 'x-world/x-vrml';

mime_types('wav') := 'audio/x-wav';

mime_types('wcm') := 'application/vnd.ms-works';

mime_types('wdb') := 'application/vnd.ms-works';

mime_types('wks') := 'application/vnd.ms-works';

mime_types('wmf') := 'application/x-msmetafile';

mime_types('wps') := 'application/vnd.ms-works';

mime_types('wri') := 'application/x-mswrite';

mime_types('wrl') := 'x-world/x-vrml';

mime_types('wrz') := 'x-world/x-vrml';

mime_types('xaf') := 'x-world/x-vrml';

mime_types('xbm') := 'image/x-xbitmap';

mime_types('xla') := 'application/vnd.ms-excel';

mime_types('xlc') := 'application/vnd.ms-excel';

mime_types('xlm') := 'application/vnd.ms-excel';

mime_types('xls') := 'application/vnd.ms-excel';

mime_types('xlsx') := 'application/vnd.ms-excel';

mime_types('xlt') := 'application/vnd.ms-excel';

mime_types('xlw') := 'application/vnd.ms-excel';

mime_types('xof') := 'x-world/x-vrml';

mime_types('xpm') := 'image/x-xpixmap';

mime_types('xwd') := 'image/x-xwindowdump';

mime_types('z') := 'application/x-compress';

mime_types('zip') := 'application/zip';

-- Determine the file extension

extension := regexp_replace(FileName,'^.*\.(.+)$','\1');

-- use the extension to determine the MIME type

if mime_types.exists(extension) then

mime_type := mime_types(extension);

else

mime_type := 'text/plain'; -- Default

end if;

dbms_output.put_line('Extension is '||extension);

dbms_output.put_line('mime_type is '||mime_type);

return mime_type;

end get_mime_type;

function split(p_list varchar2, p_del varchar2 := ',') return split_tbl pipelined is

l_idx pls_integer;

l_list varchar2(32767) := p_list;

l_value varchar2(32767);

begin

loop

l_idx := instr(l_list,p_del);

if l_idx > 0 then

pipe row(substr(l_list,1,l_idx-1));

l_list := substr(l_list,l_idx+length(p_del));

else

pipe row(l_list);

exit;

end if;

end loop;

return;

end split;

Procedure send (

ToList in varchar2,

Bcc in varchar2 := null,

Subject in varchar2,

Body in varchar2,

FromEmail in varchar2 := 'DoNotReply@Nowhere.Com',

SmtpServer in varchar2 := 'mailhost',

SmtpPort in number := 25,

AttachList in varchar2 := null,

Directory in varchar2 := null

) is

smtp utl_smtp.CONNECTION;

reply utl_smtp.REPLY;

file_handle bfile;

file_exists boolean;

block_size number;

file_len number;

pos number;

total number;

read_bytes number;

data raw(200);

my_code number;

my_errm varchar2(32767);

mime_type varchar2(50);

myhostname varchar2(255);

att_table dbms_utility.uncl_array;

att_count number;

tablen binary_integer;

loopcount number;

procedure writeLine(line in varchar2 default null) is

begin

utl_smtp.Write_Data(smtp, line||utl_tcp.CRLF);

end;

begin

myhostname := sys_context('USERENV','SERVER_HOST');

dbms_output.put_line('ToList: '||ToList);

dbms_output.put_line('Bcc: '||Bcc);

dbms_output.put_line('Subject: '||Subject);

dbms_output.put_line('Body: '||Body);

dbms_output.put_line('FromEmail: '||FromEmail);

dbms_output.put_line('SmtpServer: '||SmtpServer);

dbms_output.put_line('AttachList: '||AttachList);

dbms_output.put_line('Directory: '||Directory);

dbms_output.put_line('ToList: '||ToList);

dbms_output.put_line('myhostname: '||myhostname);

dbms_output.put_line('Opening connection to '||SmtpServer);

smtp := utl_smtp.open_connection(SmtpServer, SmtpPort);

reply := utl_smtp.helo(smtp, myhostname);

reply := utl_smtp.mail(smtp, regexp_replace(FromEmail,'^".*"<(.*)>$','\1'));

-- Split up the recipient list

for i in (select column_value as recipient from table(split(ToList)))

loop

dbms_output.put_line('Recipient: '||i.recipient);

reply := utl_smtp.RCPT(smtp, i.recipient);

end loop;

-- Split up the BCC list

for i in (select column_value as recipient from table(split(Bcc)))

loop

dbms_output.put_line('BCC: '||i.recipient);

reply := utl_smtp.RCPT(smtp, i.recipient);

end loop;

reply := utl_smtp.open_data( smtp );

WriteLine( 'From: '||FromEmail);

-- Split up the recipient list (again) - but NOT the BCC

for i in (Select column_value as recipient from table(split(ToList)))

loop

WriteLine( 'To: '||i.recipient);

end loop;

WriteLine('Subject: '||Subject);

WriteLine('Content-Type: multipart/mixed; boundary="gc0p4Jq0M2Yt08jU534c0p"');

WriteLine('MIME-Version: 1.0');

WriteLine;

-- start of boundary payloads

WriteLine('--gc0p4Jq0M2Yt08jU534c0p');

if Body like '<html>%' then

WriteLine('Content-Type: text/html');

else

WriteLine('Content-Type: text/plain');

end if;

WriteLine;

WriteLine(Body);

WriteLine;

WriteLine('--gc0p4Jq0M2Yt08jU534c0p');

dbms_output.put_line('Starting attachment segment');

dbms_output.put_line('Directory: '||Directory);

dbms_output.put_line('AttachList: '||AttachList);

-- Split up the attachment list

loopcount := 0;

Select count(*) into att_count from table(split(AttachList));

if AttachList is not null and Directory is not null then

for i in (Select ltrim(rtrim(column_value)) as attachment from table(split(AttachList))) loop

loopcount := loopcount +1;

dbms_output.put_line('Attaching: '||Directory||'/'||i.attachment);

utl_file.fgetattr(Directory, i.attachment, file_exists, file_len, block_size);

if file_exists then

dbms_output.put_line('Getting mime_type for the attachment');

Select get_mime_type(i.attachment) into mime_type from dual;

WriteLine('Content-Type: '||mime_type);

WriteLine('Content-Transfer-Encoding: base64');

WriteLine('Content-Disposition: attachment; filename="'||i.attachment||'"');

WriteLine;

file_handle := bfilename(Directory,i.attachment);

pos := 1;

total := 0;

file_len := dbms_lob.getlength(file_handle);

dbms_lob.open(file_handle,dbms_lob.lob_readonly);

loop

if pos + 57 - 1 > file_len then

read_bytes := file_len - pos + 1;

--dbms_output.put_line('Last read - Start: '||pos);

else

--dbms_output.put_line('Reading - Start: '||pos);

read_bytes := 57;

end if;

total := total + read_bytes;

dbms_lob.read(file_handle,read_bytes,pos,data);

utl_smtp.write_raw_data(smtp,utl_encode.base64_encode(data));

--utl_smtp.write_raw_data(smtp,data);

pos := pos + 57;

if pos > file_len then

exit;

end if;

end loop;

dbms_output.put_line('Length was '||file_len);

dbms_lob.close(file_handle);

if (loopcount < att_count) then

WriteLine;

WriteLine( '--gc0p4Jq0M2Yt08jU534c0p' );

else

WriteLine;

WriteLine( '--gc0p4Jq0M2Yt08jU534c0p--' );

dbms_output.put_line('Writing end boundary');

end if;

else

dbms_output.put_line('Skipping: '||Directory||'/'||i.attachment||'Does not exist.');

end if;

end loop;

end if;

reply := utl_smtp.close_data( smtp );

reply := utl_smtp.quit( smtp );

end;

end send_email;

2022年4月19日星期二

Oracle Materialized View

https://dotblogs.com.tw/keep_walking_claire/2017/07/07/materialized_view

--Create Materialized View (不會自動更新資料)

CREATE MATERIALIZED VIEW Material_View_Name

[TABLESPACE TBS_XXX]

REFRESH [ON DEMAND (預設)/ COMMIT]

[COMPLETE / FORCE (預設) / FAST]

[WITH Primary Key (預設)/ ROWID / ROWID / OBJECT ID]

[START WITH SYSDATE NEXT (SYSDATE+1)]

SELECT SQL;

-- Create Materialized View (Table Commit 就會更新資料)

CREATE MATERIALIZED VIEW Material_View_Name

REFRESH [COMPLETE / FORCE (預設) / FAST]

ON COMMIT

[WITH ROWID / WITH PRIMARY KEY (預設)]

SELECT SQL;

--Create Materialized View (定期自動更新資料)

CREATE MATERIALIZED VIEW Material_View_Name

REFRESH [COMPLETE / FORCE (預設) / FAST]

START WITH SYSDATE+0

NEXT TRUNC(SYSDATE)+1

[WITH ROWID / WITH PRIMARY KEY (預設)]

SELECT SQL;

--查詢 Materialized View 狀態

SELECT MVIEW_NAME

, REFRESH_METHOD

, LAST_REFRESH_TYPE

, LAST_REFRESH_DATE

, QUERY

FROM USER_MVIEWS WHERE mview_name='MVIEW_NAME';

--手動Refresh

BEGIN

DBMS_MVIEW.REFRESH( 'MVIEW_NAME' );

END;

--重新編譯

ALTER MATERIALIZED VIEW MVIEW_NAME COMPILE;

2022年4月7日星期四

Oracle dbms_scheduler

--建立job

begin

dbms_scheduler.create_job (

job_name => 'delete_history_log',

job_type => 'STORED_PROCEDURE',

job_action => 'manage_logs_pkg.delete_history_logs',

start_date => to_date('20211208080000','yyyymmdd hh24miss'),

repeat_interval => 'freq=daily; byhour=8', --Run at 8 am every day --'freq=minutely; interval=5' --每五分鐘

enabled => TRUE);

END;

--確認job建立成功

SELECT job_name, job_type, job_action,last_start_date, next_run_date

FROM USER_SCHEDULER_JOBS where job_name=upper('delete_history_log');

--早上8:00後查看job啟動成功

select job_name,log_date,status,req_start_date,actual_start_date,run_duration,session_id

from user_scheduler_job_run_details where job_name= upper('delete_history_log');

--刪除JOB

DBMS_SCHEDULER.drop_job (job_name=>'test_sched_job');

--enable / disable a job

DBMS_SCHEDULER.disable (name=>'test_sched_job');

DBMS_SCHEDULER.enable (name=>'test_sched_job');

--手動執行JOB

DBMS_SCHEDULER.run_job (job_name => 'test_sched_job', use_current_session => TRUE);

--設定attribute(例如NLS_LANGUAGE)

BEGIN

dbms_scheduler.set_attribute('test_sched_job','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' ');

dbms_scheduler.enable('test_sched_job');

END;

--調整JOB參數

Begin 
dbms_scheduler.disable( 'test_sched_job' );

DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'test_sched_job', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI,SAT;byhour=20;byminute=0; bysecond=0');--change value--as per need

dbms_scheduler.enable('test_sched_job');
End;

--若要作完整架構的排成請參考:

program > schedule > job

https://www.support.dbagenesis.com/post/scheduling-jobs-with-dbms_scheduler

--repeat_interval 範例 (https://www.twblogs.net/a/5d498143bd9eee541c306970)

1.每週5的時候運行，以下3條實現功能一樣

REPEAT_INTERVAL => 'FREQ=DAILY; BYDAY=FRI';

REPEAT_INTERVAL => 'FREQ=WEEKLY; BYDAY=FRI';

REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=FRI';

2.每隔一週運行一次，僅在周5運行

REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI’;

3.每月最後一天運行

REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=-1';

4.在3月10日運行

REPEAT_INTERVAL => 'FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10';

REPEAT_INTERVAL => 'FREQ=YEARLY; BYDATE=0310';

5.每10隔天運行

REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=10';

6.每天的下午4、5、6點時運行

REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=16,17,18’;

7.每月29日運行

REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=29';

8.每年的最後一個周5運行

REPEAT_INTERVAL => 'FREQ=YEARLY; BYDAY=-1FRI';

9.每隔50個小時運行

REPEAT_INTERVAL => 'FREQ=HOURLY; INTERVAL=50';

訂閱：文章 (Atom)

2022年5月27日 星期五

2022年5月19日 星期四

2022年4月27日 星期三

2022年4月21日 星期四

2022年4月19日 星期二

2022年4月7日 星期四

2022年5月27日星期五

2022年5月19日星期四

2022年4月27日星期三

2022年4月21日星期四

2022年4月19日星期二

2022年4月7日星期四