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

/

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