[EBS]Delivery Status

 https://blog.51cto.com/baser/2059271

[Oracle]DB使用狀況

--查看Table硬碟使用狀況

 select segment_name,segment_type, sum(bytes/1024/1024/1024) GB

from dba_segments

 --where segment_name='&Your_Table_Name' 

group by segment_name,segment_type; 

--查看db 空間使用狀況

select

"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)", ("Reserved_Space(MB)" - "Free_Space(MB)")/"Reserved_Space(MB)"*100||'%' "Used Percent"

from(

select

(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",

(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"

from dual );

[EBS]刪除已建立的Concurrent

無法從前端刪除, 但可執行以下Script刪除

 BEGIN

fnd_program.delete_program('short_name','application name');

fnd_program.delete_executable('short_name','application name');

COMMIT;

END;

 

SELECT fa.application_id           "Application ID",

       fat.application_name        "Application Name",

       fa.application_short_name   "Application Short Name"

  FROM fnd_application fa,

       fnd_application_tl  fat

 WHERE fa.application_id = fat.application_id

   AND fat.language      = USERENV('LANG')

   AND fat.application_name = 'Order Management';

[Oracle]指定sequence為欄位identify

Altering an IDENTITY Column

參考:Oracle 如何做到 SQL Server 的 Identity 欄位型態 - Yowko's Notes

         https://www.oracletutorial.com/oracle-basics/oracle-identity-column/

Oracle 12c introduced a new way that allows you to define an identity column for a table, which is similar to the AUTO_INCREMENT column in MySQL or IDENTITY column in SQL Server.

**Oracle原本無identity用法

--1. 先移除原本存在的IDENTITY

ALTER TABLE test_tb (MODIFY pk_id DROP IDENTITY);

--2. 指定sequence給pk_id 

alter table test_tb modify pk_id default on null test_tb_seq .nextval;

-- CREATE SEQUENCE

CREATE SEQUENCE test_tb_seq 

    START WITH 1

    INCREMENT BY 1

    MAXVALUE 9999999999999999999999999999

    CACHE 20

    CYCLE

[EBS]OM 銷售到出倉所經歷的表

 https://www.twblogs.net/a/5b8cf6cd2b7177188337aec1

[EBS]查看某個Request的Output File

 

Oracle EBS標準功能中request執行完後只能看到自己執行的結果(view output), 

系統人員有時要幫忙查看問題就顯得有些麻煩...

2023更新: Responsibility>Application Developer > Concurrent > Requests > 可以Find其他人執行的結果

查看有個function可以取到request的output, 取得EBS report網址:

SELECT fnd_webfile.get_url(
                           file_type   => 4,                --輸出類行
                           id          => 60240818,         --Request_id
                           gwyuid      => '',               --不使用 (環境參數)
                           two_task    => '',               --不使用 (Two Task)
                           expire_time => 10                --URL保留分鐘數
                          ) url
  FROM dual;

/* Define file types for get_url */

process_log constant number := 1;

icm_log constant number := 2;

request_log constant number := 3;

request_out constant number := 4;

request_mgr constant number := 5;

frd_log constant number := 6;

generic_log constant number := 7;

generic_trc constant number := 8;

generic_ora constant number := 9;

generic_cfg constant number := 10;

context_file constant number := 11;

generic_text constant number := 12;

generic_binary constant number := 13;

request_xml_output constant number :=14;

DECLARE

   l_request_id   NUMBER := :P_REQ_ID;                       -- The request id

   l_two_task     VARCHAR2 (256);

   l_gwyuid       VARCHAR2 (256);

   l_url          VARCHAR2 (1024);

BEGIN

   -- Get the value of the profile option named, Gateway User ID (GWYUID)

   --- l_gwyuid := fnd_profile.VALUE ('APPLSYSPUB/PUB');

   SELECT   profile_option_value

     INTO   l_gwyuid

     FROM   fnd_profile_options o, fnd_profile_option_values ov

    WHERE       profile_option_name = 'GWYUID'

            AND o.application_id = ov.application_id

            AND o.profile_option_id = ov.profile_option_id;

   -- Get the value of the profile option named, Two Task(TWO_TASK)

   SELECT   profile_option_value

     INTO   l_two_task

     FROM   fnd_profile_options o, fnd_profile_option_values ov

    WHERE       profile_option_name = 'TWO_TASK'

            AND o.application_id = ov.application_id

            AND o.profile_option_id = ov.profile_option_id;

   l_url :=

      fnd_webfile.get_url (file_type     => fnd_webfile.request_out, -- for out file

                           ID            => l_request_id,

                           gwyuid        => l_gwyuid,

                           two_task      => l_two_task,

                           expire_time   => 500-- minutes, security!.

                           );

   DBMS_OUTPUT.put_line (l_url);

END;

[EBS] Sales Order Bill_to and Ship_to Address

 Order To Bill_to and Ship_to Customer Address

https://jayantaapps.blogspot.com/2017/07/order-to-bill-to-customer-address.html

----------------------Bill To Customer-------------------------------
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter
WHERE     hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
      AND hcsu.site_use_code = 'BILL_TO'
      AND hca.cust_account_id=:SOLD_TO_ORG_ID --Select SOLD_TO_ORG_ID  From oe_order_headers_all
      and hcsu.site_use_id=:INVOICE_TO_ORG_ID --225009  Select INVOICE_TO_ORG_ID From oe_order_headers_all

----------------------Ship To Customer-------------------------------
/* Formatted on 7/17/2017 3:05:46 PM (QP5 v5.115.810.9015) */
SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag,

       hcsu.location
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter
WHERE     hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
      AND hcsu.site_use_code = 'SHIP_TO'
      AND hca.cust_account_id=:SOLD_TO_ORG_ID --Select SOLD_TO_ORG_ID From oe_order_headers_all
      and hcsu.site_use_id=:SHIP_TO_ORG_ID --Select SHIP_TO_ORG_ID  From oe_order_headers_all 

--- Upto INR 40000 off on Desktop CPUs & All in One Computers; No Cost EMI available

JS樂透程式範例

 

While迴圈

var lottery = [];

var n;

while (lottery.length<6) {

  n=Math.floor(Math.random()*49)+1;

  if (lottery.indexOf(n)===-1) {

    lottery.push(n);    

  }

}

console.log(lottery);

For迴圈

var lottery = [];

var n;

for(i = 0; i < 6; i++){

    n=Math.floor(Math.random()*49)+1;

    if (lottery.indexOf(n)===-1) {

    lottery.push(n);    

  }

}

console.log(lottery);

JS邏輯運算子

會先將值轉換為布林值, 再取兩者其中之一.

var a = 123;

var b = "abc";

var c = null;

var d = undefined;

//undefined, Null, +0, -0 or NaN, 空字串""或''會轉換為falsy, 其他為truthy

console.log(a||c); // ||(or) 若第一個值轉換為truthy, 則回傳第一個值

console.log(c||a); // ||(or) 若第一個值轉換為falsy, 則回傳第二個值

console.log(a&&c); // &&(and) 若第一個truthy, 則回傳第二個值

console.log(c&&a); // &&(and) 若第一個值為falsy, 則回傳第一個值

JS判斷瀏覽器IE自動轉換Edge或Chrome

 

1. 提示IE不支援, 由Edge開啟. 原IE導向其他頁面

<script>

    if (/MSIE \d|Trident.*rv:/.test(navigator.userAgent)) {

        alert("IE is not supported.");

        window.location = 'microsoft-edge:' + location.href;

       

        setTimeout(function () {

            window.location.href = 'https://localhost';

            //window.close();

        }, 1);

    }

</script>

 

2. 直接轉Chrome開啟, 原IE導向其他頁面

<script>

    if (/MSIE \d|Trident.*rv:/.test(navigator.userAgent)) {

       

        var objShell = new ActiveXObject("WScript.Shell");

        objShell.Run("cmd.exe /c start "  + location.href, 0, true);

        setTimeout(function () {

            //history.go(-2);

            window.location.href = 'https://localhost';

            //window.close();

            }, 1);

    }

</script>

ORDS+IIS轉址

 https://medium.com/@rammelhofdotat/iis-and-oracle-apex-ords-437908c79e2

1. 先安裝ORDS

2. IIS新增Sites

3. 設定Rule

ReverseProxy

HTTP>HTTPS

[EBS]Query to find Form Function attached to which Responsibility

https://www.funoracleapps.com/2020/03/query-to-find-form-function-attached-to.html 

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,

ffl.user_function_name, ff.function_name, ffl.description,

ff.TYPE,rtl.language

FROM fnd_compiled_menu_functions cmf,

fnd_form_functions ff,

fnd_form_functions_tl ffl,

fnd_responsibility r,

fnd_responsibility_tl rtl,

apps.fnd_application_all_view faa

WHERE cmf.function_id = ff.function_id

AND r.menu_id = cmf.menu_id

AND rtl.responsibility_id = r.responsibility_id

AND cmf.grant_flag = 'Y'

AND ff.function_id = ffl.function_id

AND faa.application_id(+) = r.application_id

AND UPPER(rtl.responsibility_name) LIKE '%Responsibility_Name%'

--and ffl.user_function_name like '%&Function_Name%'

AND r.end_date IS NULL

AND rtl.language='US'

ORDER BY rtl.responsibility_name;

Oracle APEX EXPORT & IMPORT Application

 

https://docs.oracle.com/en/database/oracle/apex/22.1/aeadm/exporting-and-importing-using-sqlcl.html

EXPORT Application

Step1. Download SQLcl

https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/ 

Step2. (Windows)透過提示命令字元或PowerShell連至DB

切換至SQLcl目錄, 例如: D:\Tools\sqlcl-latest\sqlcl\bin

執行: sql.exe ${username}/${password}@//${00.00.00.000:1521}/${DatabaseName}

Step3. Export Application

執行: apex export -applicationid ${appid} -dir ${path}

例如: apex export -applicationid 224 -dir D:/apex_backup

產生 f224.sql 於目錄 D:/apex_backup

Import Application

於SQLcl執行export的sql file

例如: @D:/apex_backup/f224.sql

ORA-04021：timeout occurred while waiting to lock object

1. 先查詢是否被Lock 

SELECT

b.SID,

b.USERNAME,

b.MACHINE,

b.SERIAL#

FROM

V$ACCESS a,

V$SESSION b

WHERE

a.SID = b.SID

AND a.OBJECT = '%PACKAGE_NAME%'

AND a.TYPE = 'PACKAGE';

2. 若是被Lock, 砍掉該筆session

alter system kill session 'sid,serial#'

[EBS]取得responsibility

 Useful query to get user id, responsibility id and application id in EBS.

To know about User Id use below Query:-

SQL> select user_id from fnd_user where user_name=’DOYEN’;

To know about Responsibility id use below Query:-

SQL> select responsibility_id,RESPONSIBILITY_NAME from fnd_responsibility_vl where responsibility_name like ‘%India%Local%Payables%’;

To Know about Application id use below Query:-

https://doyensys.com/blogs/useful-query-to-get-user-id-responsibility-id-and-application-id-in-ebs/

SQL>select APPLICATION_ID from fnd_responsibility_vl where responsibility_id=50366;

To Know about Org id from po:-

SQL>select org_id from po_headers_all where segment1=’&PO’;

To know about Requisition Org id from requisition number:-

$SQL>select hr.name, prh.segment1, prh.org_id from po_requisition_headers_all prh, hr_all_organization_units hr where prh.org_id = hr.organization_id and prh.segment1 = ‘&Enter_Req_Number’;

[EBS] 查看Concurrent

 SELECT DISTINCT

       fa.APPLICATION_SHORT_NAME, --application（模組）

       fat.APPLICATION_NAME,

       fcp.CONCURRENT_PROGRAM_NAME, --program（應用程式）

       fcpt.USER_CONCURRENT_PROGRAM_NAME,

       fcpT.DESCRIPTION,

       fcpt.LANGUAGE

  FROM apps.fnd_application_tl fat,

       apps.fnd_application fa,

       apps.fnd_concurrent_programs_tl fcpt,

       apps.fnd_concurrent_programs fcp,

       apps.fnd_executables fe

WHERE fat.APPLICATION_ID = fa.APPLICATION_ID

       AND fat.LANGUAGE = fcpt.LANGUAGE

       AND fa.APPLICATION_ID = fcp.APPLICATION_ID

       AND fcpt.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID

       AND fcp.executable_id = fe.executable_id

       AND fcpt.USER_CONCURRENT_PROGRAM_NAME='HUB flow concurrent'; 

       --報表名稱

      

SELECT

   distinct user_concurrent_program_name,

    responsibility_name,

    request_date,

    argument_text,

    request_id,

    phase_code,

    status_code,

    logfile_name,

    outfile_name,

    output_file_type,

    hold_flag,

    user_name

FROM

    apps.fnd_concurrent_requests fcr,

    apps.fnd_concurrent_programs_tl fcp,

    apps.fnd_responsibility_tl fr,

    apps.fnd_user fu

WHERE

    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id

    and fcr.responsibility_id = fr.responsibility_id

    and fcr.requested_by = fu.user_id

    --and user_name = upper('HIMSINGH')

    and user_concurrent_program_name in ('deliverry flow concurrent')

    --and Phase_code='P'

ORDER BY REQUEST_DATE DESC;

--查看request執行狀況

SELECT   /*+ rule */

         rq.parent_request_id,

         rq.request_id,

         tl.user_concurrent_program_name,

         rq.actual_start_date,

         rq.actual_completion_date,

         ROUND((rq.actual_completion_date - rq.actual_start_date) * 1440, 2) run_time_min

FROM     apps.fnd_concurrent_programs_vl tl,

         apps.fnd_concurrent_requests rq

WHERE    tl.application_id = rq.program_application_id

AND      tl.concurrent_program_id = rq.concurrent_program_id

AND      rq.actual_start_date IS NOT NULL

AND      rq.actual_completion_date IS NOT NULL

AND      tl.user_concurrent_program_name = 'employee master data'

ORDER BY rq.actual_completion_date DESC;

[EBS]彈性欄位設定

1. 查看要設定的畫面Title是什麼, 請參考另一篇: [EBS]查詢某個畫面的彈性欄位

2.切換權限Application Developer", Flexfield > Descriptive > Register

按[Segments] > [New], 編輯相關欄位

退出時，check [Freeze Fexfild definitions]，並[Compile]

[EBS]查詢某個畫面的彈性欄位

 1. Situation: 想查詢訂單明細的彈性欄位:SKU ID

2. 先找出這隻程式相關資訊: 

比如說,這畫面上可以看到是Additional Line Attribute Information

或是Help > Record History 查看一下Table, 通常會跟這個名字類似...

或是Help > Diagnostics > Examine 查看(Block輸入$DESCRIPTIVE_FLEXFIELD$, Field輸入要查的項目, 按Tab查詢出Value)

3.  切換權限到Application Developer", Flexfield > Descriptive > Register

F11查詢, 例如輸入Table Name=OE_ORDER_LINES%, Ctrl+F11執行查詢.

再比對一下Title.

4. 記下Ttile再到 Flexfield > Descriptive > Segments  查詢欄位

找到SKU_ID是使用OE_ORDER_LINES_ALL.Attribute7

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

/