Oracle Materialized View

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

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

CREATE MATERIALIZED VIEW Material_View_Name

[TABLESPACE TBS_XXX]

REFRESH [ON DEMAND (預設)/ COMMIT] 

        [COMPLETE / FORCE (預設) / FAST] 

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

[START WITH SYSDATE NEXT (SYSDATE+1)]

AS

SELECT SQL;

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

CREATE MATERIALIZED VIEW Material_View_Name

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

ON COMMIT

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

AS

SELECT SQL;

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

CREATE MATERIALIZED VIEW Material_View_Name

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

START WITH SYSDATE+0 

NEXT TRUNC(SYSDATE)+1

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

AS

SELECT SQL;

--查詢 Materialized View 狀態

SELECT MVIEW_NAME

     , REFRESH_METHOD

     , LAST_REFRESH_TYPE

     , LAST_REFRESH_DATE

     , QUERY

  FROM USER_MVIEWS WHERE mview_name='MVIEW_NAME';

--手動Refresh

BEGIN

  DBMS_MVIEW.REFRESH( 'MVIEW_NAME' );

END;

--重新編譯

ALTER MATERIALIZED VIEW MVIEW_NAME COMPILE;

Oracle dbms_scheduler

 --建立job

begin

dbms_scheduler.create_job (

   job_name           =>  'delete_history_log',

   job_type           =>  'STORED_PROCEDURE',

   job_action         =>  'manage_logs_pkg.delete_history_logs',

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

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

   enabled            =>  TRUE);

END;

--確認job建立成功

SELECT job_name, job_type, job_action,last_start_date, next_run_date 

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

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

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

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

--刪除JOB

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

--enable / disable a job

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

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

--手動執行JOB

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

--設定attribute(例如NLS_LANGUAGE)

BEGIN

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

   dbms_scheduler.enable('test_sched_job');

END;

--調整JOB參數

Begin 
dbms_scheduler.disable( 'test_sched_job' );

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

dbms_scheduler.enable('test_sched_job');
End;

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

program > schedule > job

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

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

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

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

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

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

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

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

3.每月最後一天運行

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

4.在3月10日運行

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

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

5.每10隔天運行

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

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

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

7.每月29日運行

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

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

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

9.每隔50個小時運行

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

regexp_substr SPILT

 select 

 regexp_substr(:P4_REVISED_ITEM, '[^:]+', 1, 1) "Item_Id", 

 regexp_substr(:P4_REVISED_ITEM, '[^:]+', 1, 3) "Description"

from dual;

--拆分字串, 例: 多個email依據分號;切分 ==>REGEXP_SUBSTR, CONNECT BY

SELECT NVL(REGEXP_SUBSTR('123@mail.com;333@mail;333333@mail', '[^;]+', 1, LEVEL, 'i'), 'NULL') AS STR 

FROM DUAL 

CONNECT BY LEVEL <= LENGTH('123@mail.com;333@mail;333333@mail') - LENGTH(REGEXP_REPLACE('123@mail.com;333@mail;333333@mail', ';', ''))+1;

[Oracle] Network ACL

Oracle 11g可利用 ACL(Access Control List)來做外部訪問權限設定.

Principal will have what privileges on what object. --Principal: WHO, Object: WHAT

1. Create the ACL

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
      description => 'WWW ACL',
      principal   => 'SCOTT', --表示權限賦予給哪個用戶 or PUBLIC
      is_grant    => true,
      privilege   => 'connect');

2.Grant privilege to user or Public

  -- Grant connect permission 
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'www.xml',
principal => 'PUBLIC',  --if you want user like 'SCOTT' 
is_grant => true,
privilege => 'connect');
COMMIT;
END;
/

-- grant resolve permission
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'www.xml',
principal => 'PUBLIC',  --if you want user like 'SCOTT' 
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/

3.Assign ACL to network. 分配受該ACL制約的主機以及其他訊息

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'www.xml',
host => '192.168.63.63', -- specify the host IP address 
lower_port => 34,       -- specify the port range lower value (* for all)
upper_port => 63);      -- specify the port range higher value
COMMIT;
END;
/

4.Verified the ACL is created.

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

5.Drop the ACL

BEGIN
   DBMS_NETWORK_ACL_ADMIN.drop_acl ( acl => 'www.xml');
END;

[Oracel ebs]How to create Request

 Reference Source: 

https://subscription.packtpub.com/book/application-development/9781849684842/1/ch01lvl1sec11/creating-request-sets

Test Run Request:

Reports, Request : Run Request

Parameters: 若有參數要輸入, 例如:有p_user_id, p_order_number, Parameters輸入G1234:55667700  (:分隔)

[Oracle EBS]TABLE後墜代表意思

https://bpcblogsoracle.wordpress.com/2012/05/09/oracle-inventory-inventory-organizations-2/

https://erpschools.com/erps/articles/table-suffixes-oracle-apps_all_tl_f_v_vl_s_a

https://www.oracleappsdna.com/2012/02/oracle-tables-with-suffix-_all-_tl-_b-_f-_v-_vl-_s-_a-_avn-and-_acn/

_ALL: 以_ALL 結尾的表表示它存儲與多個組織相關的數據。這樣的表肯定會有一個名為 org_id 的列，它指定記錄所屬的組織。

_TL: 以_TL 結尾的表是一個翻譯表。這樣的表有一個叫做language的列

_B: Tables ending _B are the base tables

_VL: 以 _VL 結尾的表是基於多語言或翻譯表構建的視圖。換句話說，這種視圖將基表數據與轉換錶數據結合起來。如果您希望查看與一種特定語言相關的數據，您應該使用 USERENV(‘LANG’) 設置語言。

_V: Tables ending with _V are views

_S: Tables ending with _S are Sequences

_A, _Avn, _ACn: Tables ending with _A, _Avn, _ACn are audit tables.

* audit tables:審計表是包含行的完整歷史記錄的表。

_F: 使用兩個日期列（Effective_start_Date、Effective_end_date）跟踪數據的表。這些表主要用於 HRMS 和工資模塊。

_AVN and _ACN are Audit Shadow Views (when data was changed, and with what values

(何時更改數據，以及更改了哪些值)

_KFV is the view which contains all the information about Key Flex Fields(KFFs).

_DFV is the view which contains the information about Descriptive Flex Fields(DFFs).

GL: General Ledger 總帳，使存儲會計資訊的中心，接收來自財務和製造模組的會計資訊
GL包括(共6個模組)：
      採購 Purchasing ，
      應付 Account Payable ，
      資產 Fixed Assets，
      應收 Accounts Receivable，
      庫存 Inventory，
      訂單 Order Management

[Oracel ebs]How to create Concurrent Program

Reference source:

https://www.youtube.com/watch?v=Qjjyraip4DM

http://www.bizinsightconsultingblog.com/2009/06/how-to-create-concurrent-program-on.html

 1. what is concurrent?

2.Concurrent Program Type

PL/SQL Store Procedure | PLSQL

Oracle Reports | SQL/PLSQLL

Host | Shell Script

SQL*Loader | SQLLoader Control File

SQL*Plus | SQL/PLSQL

Java Concurrent Program | Java

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

Step:

1. Executable

2. Concurrent Program

3. Attach Concurrent Program with Request Group

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

Create Executable

    Concurrent : Program : Executable

Executable

Enter all the details for Executable

Short Name

Best Practice is Short Name of Executable and concurrent program Schedule be same

Subroutine Name

For example: define executable for store procedure

Create Executable  

    Concurrent : Program : Define

  

Program

Enter the name for CP. Check the enable check box.

Short Name

Enter Name for CP Short Name should be same as short name of executable.(It's Best Practice)

Executable-Name

Select executable name from LOV.

Output-Format

Select the output format for CP.

Parameters

    1.Select the Value Set .

    2.Default Type.

    3.Default value for Set

    4.Default Value.

    

Attach Concurrent Program with Request Group  

Security : Responsibility : Request

After Saving the Concurrent Program, Attach it with required Request Set.

Choose the group where you want to attach CP

Add Concurrent program