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