Oracle 利用正規表達式取代特殊字元

 WITH test_data (text_value) AS

(

SELECT 'A12345678' FROM DUAL UNION ALL

SELECT 'a12345678' FROM DUAL UNION ALL

SELECT 'A1234567' FROM DUAL UNION ALL

SELECT 'A123456789' FROM DUAL UNION ALL

SELECT '$12345678' FROM DUAL UNION ALL

SELECT '中文A1234567_$' FROM DUAL

)

SELECT td.text_value,

REGEXP_REPLACE(

td.text_value, '[^a-zA-Z0-9一-龥]', ''

) AS CLEANED_TEXT

FROM test_data td;

解析： [^...]： 方括號中的插入符號 (^) 表示取反。這表示它將匹配任何不在括號內列出的字元。 a-zA-Z： 這部分匹配任何小寫（a-z）或大寫（A-Z）的英文字母。 0-9： 這匹配任何從 0 到 9 的數字。 一-龥： 這個範圍匹配常用的中文字符。一（U+4E00）到 龥（U+9FA5）包含了相當大部分的中日韓統一表意文字。

[PLSQL]EXIT/RETURN/CONTINUE

exit 結束循環, 跳出這個循環, 繼續執行後續程式

return 直接結束整個程式

continue 中止這個循環, 跳下一循環

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

exit範例:

DECLARE

i NUMBER;

BEGIN

FOR i IN 1..3

LOOP

IF (i MOD 2 = 0 ) THEN

dbms_output.put_line('遇到偶數');

dbms_output.put_line('EXIT:跳出循環');

EXIT;

END IF;

dbms_output.put_line('i='||i);

END LOOP;

dbms_output.put_line('END LOOP');

END;

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

i=1

遇到偶數

EXIT:跳出循環

END LOOP

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

--return範例:

DECLARE

i NUMBER;

BEGIN

FOR i IN 1..3

LOOP

IF (i MOD 2 = 0 ) THEN

dbms_output.put_line('遇到偶數');

dbms_output.put_line('RETURN:結束整個程式');

return;

END IF;

dbms_output.put_line('i='||i);

END LOOP;

dbms_output.put_line('END LOOP');

END;

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

i=1

遇到偶數

RETURN:結束整個程式

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

continue範例:

DECLARE

i NUMBER;

BEGIN

FOR i IN 1..3

LOOP

IF (i MOD 2 = 0 ) THEN

dbms_output.put_line('遇到偶數');

dbms_output.put_line('CONTINUE:跳出這個循環 下一循環');

continue;

END IF;

dbms_output.put_line('i='||i);

END LOOP;

dbms_output.put_line('END LOOP');

END;

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

i=1

遇到偶數

CONTINUE:跳出這個循環 下一循環

i=3

END LOOP

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

Oracle EBS結帳區間判斷SQL

 From: http://arthur-work.blogspot.com/2020/02/oracle-ebs-invsql.html

Oracle EBS: 用於查詢INV期間是否關閉之SQL

SELECT * FROM all_objects WHERE object_name='ORG_ACCT_PERIODS'

SELECT ood.organization_id "Organization ID" ,

ood.organization_code "Organization Code" ,

ood.organization_name "Organization Name" ,

oap.period_name "Period Name" ,

oap.period_start_date "Start Date" ,

oap.period_close_date "Closed Date" ,

oap.schedule_close_date "Scheduled Close" ,

DECODE(oap.open_flag, 'P','P - Period Close is processing' ,

'N','N - Period Close process is completed' ,

'Y','Y - Period is open if Closed Date is NULL' ,'Unknown') "Period Status"

FROM org_acct_periods oap ,

org_organization_definitions ood

WHERE oap.organization_id = ood.organization_id

AND (TRUNC(SYSDATE) -- Comment line if a a date other than SYSDATE is being tested.

--AND ('01-DEC-2014' -- Uncomment line if a date other than SYSDATE is being tested.

BETWEEN TRUNC(oap.period_start_date) AND TRUNC (oap.schedule_close_date))

ORDER BY ood.organization_id,

oap.period_start_date;

-- If Period Status is 'Y' and Closed Date is not NULL then the closing of the INV period failed.

[EBS]Sales Order Join Delivery

 

SELECT

OOH.ORG_ID,

OOH.ORDER_NUMBER,

OOH.ORDERED_DATE,

OOH.ORDER_TYPE_ID,

OOL.LINE_ID "ORDER_LINE_ID",

OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER "ORDER_LINE",

OOL.INVENTORY_ITEM_ID "ITEM_ID",

OOL.ORDERED_ITEM "ITEM_NUMBER",

OOL.ORDERED_QUANTITY "QUANTITY",

OOL.ORDER_QUANTITY_UOM "UOM",

OOL.UNIT_SELLING_PRICE "UNIT_PRICE",

OOH.TRANSACTIONAL_CURR_CODE "CURRENCY",

trunc(OOL.SCHEDULE_SHIP_DATE) "DEMAND_DATE",

OOH.SOLD_TO_ORG_ID "CUSTOMER_ID",

OOH.SHIP_TO_ORG_ID,

OOH.INVOICE_TO_ORG_ID,

WND.name

FROM

APPS.OE_ORDER_HEADERS_ALL OOH

INNER JOIN APPS.OE_ORDER_LINES_ALL OOL ON

OOL.HEADER_ID = OOH.HEADER_ID

INNER JOIN wsh_delivery_details WDD ON

OOL.header_id=WDD.source_header_id

AND OOL.line_id=WDD.source_line_id

INNER JOIN wsh_delivery_assignments WDA ON

WDD.delivery_detail_id=WDA.delivery_detail_id

INNER JOIN wsh_new_deliveries WND ON

WND.delivery_id = WDA.delivery_id

WHERE

1 = 1

AND OOH.order_number = '12345'

AND WND.name='DD102201130'

;

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