I have a bad memory, so I start blogging...: 4.PASSWORDを初期化するPROCEDURE　：APEX ID/PASSWORD認証機能

2016/03/19

Goal
パスワードを初期化し、新しいパスワードをEmailで送信するプロシージャ
新規ユーザー登録後とパスワード忘れ時に呼び出すことを想定している。
また、新パスワードでの初回ログイン時にパスワード変更を強制するために、USERMASTERテーブルにEXPIREフラグを立てる。
EMAILはAPEX_MAILを使って送信する。

Source

	create or replace procedure PWD_INITIALIZATION(p_user_id in NUMBER)
	IS
	l_username VARCHAR2(2000);
	l_useremail VARCHAR2(100);
	l_sendemail VARCHAR2(100); -- need to set FROM address
	l_fullname VARCHAR2(30);
	l_ini_pwd VARCHAR2(8);
	l_body VARCHAR2(2000) := 'This is HTML mail. If you cannot see the mail body, please contact XXXX.';
	l_body_html VARCHAR2(4000);
	l_subj VARCHAR2(300);
	BEGIN
	--- Getting user info for sending notification
	select USERNAME,EMAIL_ADDRESS,FULL_NAME
	into l_username,l_useremail,l_fullname
	from USERMASTER
	where USER_ID = p_user_id;
	-- Create initial password
	select dbms_random.string('A',8) into l_ini_pwd from sys.dual;
	-- Set initial password in USERMASTER
	UPDATE USERMASTER SET PWD = MD5_HASH(l_username,l_ini_pwd)
	,PWD_EXPIRE = '1' ---Set EXPIRE flag
	where USER_ID = p_user_id;
	-- sending temporary pwd.
	--Creating EMAIL subject
	l_subj := 'Temporary PWD notification.';
	-- Creating EMAIL HTML BODY
	l_body_html := '<HTML><BODY><PRE><span style="font-family:monospace;"><BR>';
	l_body_html := l_body_html\|\|'Dear Sir or Madam,<BR><BR>';
	l_body_html := l_body_html\|\|'Your temporaly Password is '\|\|l_ini_pwd\|\|' . <BR><BR>';
	l_body_html := l_body_html\|\|'Go to --> http://--------';
	-- End part of Email body
	l_body_html := l_body_html\|\|'Regards,<BR>';
	l_body_html := l_body_html\|\|'</span></PRE></BODY></HTML>';
	-- send email with apex_mail
	wwv_flow_api.set_security_group_id;
	apex_mail.send (p_to => l_useremail,
	p_from => l_sendemail,
	p_body => l_body,
	p_body_html => l_body_html,
	p_subj => l_subj);
	END;

I have a bad memory, so I start blogging...