[Oracle]DB Link Session ORA-04068

 APEX DB 透過DB LINK呼叫EBS Package,

出現幾個怪現象:

1. 

ERROR at line 1:

ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package body "PACKAGE.NAME" has been

invalidated

ORA-06508: PL/SQL: could not find program unit being called:

"PACKAGE.NAME"

ORA-06512: at line 1

2. EBS Package 裡有使用  fnd_global.org_id, fnd_global.user_id, FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')

所以APEX DB執行前會去set_client_info, fnd_global.apps_initialize.

但執行EBS package 寫入EBS table卻出現別人的user_id & org_id.

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

先是懷疑DB Link的session, 查到這篇說明

https://stackoverflow.com/questions/1761595/frequent-error-in-oracle-ora-04068-existing-state-of-packages-has-been-discarde

關鍵字: 

ALTER SESSION CLOSE  DATABASE LINK DBLINK

PRAGMA SERIALLY_REUSABLE

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

ALTER SESSION CLOSE  DATABASE LINK DBLINK:

https://www.uj5u.com/shujuku/28126.html

說明一般企業不使用DB Link原因:不外乎出問題不好查..佔資源...

那如果真的要使用, 該如何管理DB Link Session?

文中有三種方式, 個人認為1和3比較適合, 1為首選

1. 使用完後程式commit, close db link (沒有commit會出現ora-02080錯誤)

指令:

commit;

alter sesssion close database link ;

or 系統包:

DBMS_SESSION.CLOSE_DATABASE_LINK(dblink_name);

EXAMPLE:

DECLARE

  v VARCHAR2(50);

CURSOR r IS

  SELECT name FROM employee@test_db_link WHERE empno = '1';

BEGIN

OPEN r;

    LOOP

FETCH r INTO v;

EXIT WHEN r%NOTFOUND;

    END LOOP;

    CLOSE r;

    COMMIT;

    EXECUTE IMMEDIATE 'alter session close database link test_db_link';

END;

3.定期JOB Kill session

select sid,

 serial#,

 username,

 osuser,

 machine,

 'alter system kill session ' || '''' || sid || ',' || serial# ||''' immediate;' session_level

 from v$session

where machine in ('連線的DB machine1', '連線的DB machine2', '連線的DB machine3');

命令後面要加immediate, 沒加的話v$session中的STATUS列會變為KILLED，但是資源並未釋放

利用方式三可以做成JOB定時kill session，但這樣存在很大的風險。 ==> 可能不小心刪到還在使用的session?? 而且我們沒有EBS DB權限

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

PRAGMA SERIALLY_REUSABLE

https://blog.csdn.net/xiadingling/article/details/82492709

"Packaged public variables and cursors persist for the duration of a session. 

They can be shared by all subprograms that execute in the environment. 

They let you maintain data across transactions without storing it in the database."

Package裡的變數生命週期是會話(session), 所以如果在同一session執行多次 變數可能會使用到上一次執行的值.

PRAGMA SERIALLY_REUSABLE這命令使得Oracle只保持一次呼叫的值.

使用時Package & Package Body都要宣告.

缺點:每次使用都會釋放, 也因此會占用DB內存, 依照使用次數成長, 與登錄用戶數無關, 會大量使用到內存

Oracle PLSQL Call API

 

Ajax Callback:  呼叫DB Funcction取得值

Process_name=set_clock_in

declare

  v_clock_in varchar2(20); 

begin

  SELECT get_clock_in_fun( to_char(sysdate,'yyyy-mm-dd')) 

  into v_clock_in

  from dual;

  htp.p(v_clock_in);

end;

Javascript: AJAX Call API

apex.server.process("set_clock_in" //Process or AJAX Callback name

                    , null          //Parameter

                    , {  

                       success: function(pData){

                           apex.item("P1_CLOCK_IN").setValue(pData);

                           //alert('Record loaded successfully');

                        },

                        dataType: "text"

                    }

                   );

PLSQL Call API Function:

get_clock_in_fun

CREATE OR REPLACE function get_clock_in_fun( v_date IN varchar2)

return varchar2

is 

      -- Local variables here

      i      integer;

      l_resp clob;

      g_url  varchar2(300) := 'https://example-api.company.com/clock-in/v_date;

      v_hashkey  varchar2(128) := '.........................................................................';

      v_hashdate varchar2(20);

      v_token    varchar2(200);

      v_resp varchar2(4000);

     

      web_failure_detected EXCEPTION;

      PRAGMA EXCEPTION_INIT(web_failure_detected, -29273);

begin

    --Authorization : {Hash Key}:{UTC datetime} 

    select to_char(systimestamp at time zone 'UTC','yyyymmddhh24miss') 

    into v_hashdate

    from dual;

      

    select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_hashkey||':'||v_hashdate))) 

    into v_token

    from dual;

    --utl_encode.base64_encode()編碼生成的字串以64個字元為一組其後添加回車換行字元

    --利用正則式替換掉所有空白字元,不管是否為回車換行字元

    v_token:=regexp_replace(v_token, '\s', '');

           

    apex_web_service.g_request_headers(1).name := 'Authorization';

    apex_web_service.g_request_headers(1).value := 'Bearer '||v_token;

      

    -- Content-Type

    apex_web_service.g_request_headers(2).name := 'Content-Type';

    apex_web_service.g_request_headers(2).Value := 'text/html';

    --HTTP 411 header加入Content-Length

    apex_web_service.g_request_headers(3).name := 'Content-Length'; 

    apex_web_service.g_request_headers(3).value := 0; 

    

    l_resp := apex_web_service.make_rest_request(p_url         => g_url,

                                                 p_http_method => 'GET'

                                                   --p_parm_name  => apex_util.string_to_table('',':'),

                                                   --p_parm_value => apex_util.string_to_table('',':')

                                                   --p_wallet_path =>null,

                                                   --p_wallet_pwd  => null

                                                 );

    v_resp := substr(l_resp, 1, 4000);

    with date_array (col) as

   (select v_resp

       from dual)

    SELECT min(val) INTO v_resp FROM 

    (

select regexp_substr(replace(substr(col,instr(col,'[')+1, instr(col,']')-2), '"', ''), '[^,]+', 1, level) val

from date_array

connect by level <= regexp_count(col, ',') + 1

    );

    

    return v_resp;

EXCEPTION 

    WHEN web_failure_detected THEN

       DBMS_OUTPUT.put_line ('web failure detected');

       RETURN ':';

    WHEN OTHERS THEN 

       RETURN '--';

END;

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

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 )

AS

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

 * 使用範例:

 * 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   => '<b>This is Test msessage</b><p>hello world</p><p>哈囉你好嗎</p>',

 *           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;  

  

  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;

[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

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;

/