程序包头:
CREATE OR REPLACE PACKAGE cux_fnd_web_sec IS FUNCTION get_user_pass(p_fnd_user IN VARCHAR2, p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE') RETURN VARCHAR2; FUNCTION get_apps_pass(p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE') RETURN VARCHAR2; --iven.lin 2018-01-01 FUNCTION encrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2; --iven.lin 2028-01-01 FUNCTION decrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2; END cux_fnd_web_sec;
程序包体:
CREATE OR REPLACE PACKAGE BODY cux_fnd_web_sec IS FUNCTION encrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.encrypt(java.lang.String,java.lang.String) return java.lang.String'; FUNCTION decrypt(key IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; FUNCTION get_apps_pass(p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE') RETURN VARCHAR2 IS l_apps_encrypted_pass VARCHAR2(200); l_apps_decrypted_pass VARCHAR2(200); BEGIN -- get apps encrypted pass SELECT encrypted_foundation_password INTO l_apps_encrypted_pass FROM apps.fnd_user WHERE user_name = 'GUEST'; --decrypt apps pass SELECT decrypt(p_guest_login, l_apps_encrypted_pass) INTO l_apps_decrypted_pass FROM dual; RETURN l_apps_decrypted_pass; END get_apps_pass; FUNCTION get_user_pass(p_fnd_user IN VARCHAR2, p_guest_login IN VARCHAR2 DEFAULT 'GUEST/ORACLE') RETURN VARCHAR2 IS l_user_encrypted_pass VARCHAR2(200); l_user_decrypted_pass VARCHAR2(200); BEGIN -- get fnd user encrypted pass BEGIN SELECT encrypted_user_password INTO l_user_encrypted_pass FROM fnd_user WHERE user_name = p_fnd_user; EXCEPTION WHEN no_data_found THEN RETURN 'User ' || p_fnd_user || ' is not exist in FND_USER table'; END; --decrypt user pass SELECT decrypt(get_apps_pass(p_guest_login), l_user_encrypted_pass) INTO l_user_decrypted_pass FROM dual; RETURN l_user_decrypted_pass; END get_user_pass; END cux_fnd_web_sec;
--使用
SELECT cux_fnd_web_sec.get_user_pass('用户名' ) FROM dual;
标签:RETURN,fnd,EBS,apps,查询,VARCHAR2,user,pass,Oracle From: https://www.cnblogs.com/ivenlin/p/18119385