do something

2022年8月29日星期一

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

2022年8月18日星期四

[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

2022年6月30日星期四

[Oracle]DB Link Session ORA-04068

APEX DB 透過DB LINK呼叫EBS Package,

出現幾個怪現象:

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內存, 依照使用次數成長, 與登錄用戶數無關, 會大量使用到內存

2022年6月2日星期四

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

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

2022年5月27日星期五

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

2022年5月19日星期四

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 )

/********************************

* 使用範例:

* 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 => 'This is Test msessagehello world哈囉你好嗎',

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

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;

訂閱：文章 (Atom)

2022年8月29日 星期一

2022年8月18日 星期四

2022年6月30日 星期四

2022年6月2日 星期四

2022年5月27日 星期五

2022年5月19日 星期四

2022年8月29日星期一

2022年8月18日星期四

2022年6月30日星期四

2022年6月2日星期四

2022年5月27日星期五

2022年5月19日星期四