oracle disable user
/* Formatted on 2018/07/04 17:30 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE cbm_fnd_prc_001 (
p_user_name IN VARCHAR2,
p_user_end_date IN VARCHAR2,
p_message OUT VARCHAR2
)
IS
p_count NUMBER;
p_user_end_date1 VARCHAR2 (20);
BEGIN
p_user_end_date1 := TO_DATE (p_user_end_date, 'YYYY/MM/DD');
SELECT COUNT (*)
INTO p_count
FROM fnd_user fu
WHERE 1 = 1 AND fu.user_name = p_user_name AND fu.end_date IS NULL;
IF p_count = 1
THEN
fnd_user_pkg.updateuser (x_user_name => p_user_name,
x_owner => NULL,
x_end_date => p_user_end_date1
);
ELSE
p_message := '權限已經關閉';
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
p_message :=
DBMS_UTILITY.format_error_backtrace
|| DBMS_UTILITY.format_error_stack;
END;