Step 1 : login to database server with sys user
sqlplus / as sysdba
STEP 2 : Create Function for to decrypt the encrypted password
SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
STEP 3 : Query for Encrypted password
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';
Output
ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A
STEP 4: Past the Encrypted password from the above query output into below query and execute
SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A') from dual;
Output
APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A')
--------------------------------------------------------------------------------
DEMONSTRATE
STEP 5: Test apps password is working or not
SQL> conn apps/DEMONSTRATE;
Connected.
For CDB and PDB setup. please follow below steps
-bash-5.2$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 30 12:48:28 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ERPDEV7 READ WRITE NO
SQL> alter session set container=ERPDEV7;
Session altered.
SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/ 2 3 4 5
Function created.
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';
ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG61AD748EB9CC0BC4256989CC11E9C227F32015F729A4C6F17DB12FE9B53BA6D21A4A27B45C68E4
AFAE6BF06C0CC9AD1B63
SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG61AD748EB9CC0BC4256989CC11E9C227F32015F729A4C6F17DB12FE9B53BA6D21A4A27B45C68E4AFAE6BF06C0CC9AD1B63') from dual;
APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG61AD748EB9CC0BC4256989CC11E9C227F32015F7
--------------------------------------------------------------------------------
APPS
SQL>