UTL_SMTP のサンプル - PL/SQLからEmail送信

UTL_SMTPを使ってPL/SQLからEmail送信する場合のサンプルコード。
APEXからメールするならAPEX_MAILの方が簡単。

• Source
  
  

雑多な1行SQLメモ ‐ Oracle - 単語登録用

単語登録して呼び出しているSQLバックアップ

‐Oracle編

	-- 日付フォーマット
	alter SESSION set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
	-- DDL の　確認
	select * from user_objects order by last_ddl_time DESC;
	-- オラクルのバージョン確認
	select * from v$version;
	-- 日付範囲指定
	CREATION_DATE >= to_date('01/01/2014', 'MM/DD/YYYY')
	-- 日付の範囲指定　FROM TO　変数
	WHERE (@FROM IS NULL or TRUNC(<DATE>) >= @FROM)
	AND (@TO IS NULL or TRUNC(<DATE>) <= @TO)
	-- Table 一覧
	select * from tab;
	-- 出力
	dbms_output.put_line ();
	-- job　スケジュール
	SELECT * FROM user_scheduler_jobs;
	-- job スケジュール material viewの場合
	SELECT * FROM dba_JOBS;
	-- View の中身
	select view_name, text from user_views where view_name='';
	-- APEX_MAIL　ログ
	select * from APEX_MAIL_LOG order by LAST_UPDATED_ON desc;
	-- APEX_MAIL　キュー
	select * from APEX_MAIL_QUEUE;
	-- USER 一覧
	select * from dba_users;
	-- DB link の一覧
	SELECT * FROM DBA_DB_LINKS;
	‐‐ 制約条件一覧
	select constraint_name,constraint_type,table_name,status from user_constraints where table_name = '<tablename>';
	constraint_type
	C - 表でのチェック制約
	P - 主キー
	U - 一意のキー
	R - 参照整合性
	V - ビューでのチェック・オプション付き
	O - ビューで読取り専用
	H - ハッシュ式
	F - REF列を含む制約
	S - サプリメンタル・ロギング
	--ULT_FILE のDirectoryの名前を調べる時
	SELECT DIRECTORY_NAME,DIRECTORY_PATH FROM ALL_DIRECTORIES;
	--上記Directoryに対する権限を調べる
	SELECT TABLE_NAME, PRIVILEGE FROM USER_TAB_PRIVS_RECD WHERE TABLE_NAME = '<DIRECTORY_NAME>';
	-- UTLパッケージの有無確認
	select * from all_objects where object_type = 'PACKAGE BODY' and OBJECT_NAME like 'UTL%';
	-- Table spaceの確認
	select a.TABLESPACE_NAME
	, min(a.BYTES)/1024/1024 size_mb
	, round(min(a.BYTES)/(1024*1024) - sum(b.BYTES)/ (1024*1024),2) usage_mb
	, round((min(a.BYTES)/(1024*1024) - sum(b.BYTES)/(1024*1024))/ (min(a.BYTES)/1024/1024)*100,2) "usage(%)"
	, round(sum(b.BYTES)/(1024*1024),2) free_mb
	from dba_data_files a, dba_free_space b
	where a.FILE_ID = b.FILE_ID
	group by a.TABLESPACE_NAME;

view raw SQL_exchange_Oracle.sql hosted with ❤ by GitHub

APP_USERを意図的にセットする

• Goal
  プロシージャからレコード作成／更新時に、APP_USERをセットする。テーブルのトリガーでLAST_UPDATE_BYをセットしている場合でも使える。
  バッチ更新やEDIでの更新時などユーザー名以外の情報をセットできる。
• Source
  
  

  	BEGIN
  	--SET APP_USER
  	apex_application.g_user := 'EDI';
  	--
  	UPDATE TABLE_NAME SET a = 1;
  	END;

  view raw SET_APP_USER.sql hosted with ❤ by GitHub

雑多な1行SQLメモ ‐ SQL server - 単語登録用

単語登録して呼び出しているSQLバックアップ

‐SQL server編

	-- 日付の藩士指定
	SELECT * FROM dual where [date] >= convert(datetime,'01/01/2015',101);
	--- NULL と　Blankを除く
	WHERE COALESCE ([column_name], '') <> ''
	-- 日付データから時間情報をカット、Oracleの Trunc()の代替 -- SQLSERVER　2008　以上
	cast(<DATE> as DATE)
	-- 日付データから時間情報をカット、Oracleの Trunc()の代替 -- SQLSERVER　2008　未満
	dateadd(dd, datediff(dd,0, <DATE>), 0)
	-- 日付データから時間情報をカット、Oracleの Trunc()の代替 -- SQLSERVER　2008　以上
	WHERE (@FROM IS NULL or cast(<DATE> as DATE) >= @FROM)
	AND (@TO IS NULL or cast(<DATE> as DATE) <= @TO)
	-- 日付データから時間情報をカット、Oracleの Trunc()の代替 -- SQLSERVER　2008　未満
	WHERE (@FROM IS NULL or dateadd(dd, datediff(dd,0, <DATE>), 0) >= @FROM)
	AND (@TO IS NULL or dateadd(dd, datediff(dd,0, <DATE>), 0) <= @TO)
	-- ダミーテーブル Oracle のDualの代替 - FROM句を付けないだけ！
	select cast(getdate() as DATE)
	-- Transaction log
	select * from ::fn_dblog(null,null)
	‐‐ シングルユーザーモード変更
	ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	-- DBのリネーム
	EXEC sp_renamedb 'oldname', 'newname';
	-- 改行削除
	REPLACE(REPLACE(FirstName, CHAR(13), ''), CHAR(10), '')
	-- Rollback　出来るようにSQLを流す
	BEGIN TRANSACTION
	DELETE FROM <table> WHERE ....
	-- Commit する
	COMMIT TRANSACTION
	ｰｰ Rollback　する
	ROLLBACK TRANSACTION

view raw SQL_exchange_SQLserver.sql hosted with ❤ by GitHub

ワンダー / Wonder

私の子供(9歳)が授業中に読んでいた本で、面白いと教えてもらった本。
よくわからない単語はあるが、2章から面白くなってきてグイグイ前に進む。
(2章までは我慢が必要かも。)
イジメや思春期的な感覚がテーマ。重たい内容だと思うのだが、私の英語力ではどの程度重たい感じかまでは正しく理解できない。

• Age Range: 8 - 12 years
• 
  

Wonder

4.PASSWORDを初期化するPROCEDURE ：APEX ID/PASSWORD認証機能 - カスタム編 -

目次に戻る

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

  view raw PWD_INITIALIZATION.sql hosted with ❤ by GitHub

目次に戻る

0.USERMASTER テーブルを作る ：APEX ID/PASSWORD認証機能 - カスタム編 -

目次に戻る

• Goal
  USERMASTERテーブルのサンプル
  パスワードの初期化機能と初回ログイン時にパスワード変更の強制を想定しているため、EXPIREフラグを追加している。
• Source
  
  

  	CREATE SEQUENCE "USERMASTER_SEQ"
  	MINVALUE 1
  	MAXVALUE 9999999999999999999999999999
  	INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOPARTITION
  	/
  	CREATE TABLE "USERMASTER"
  	( "USER_ID" NUMBER NOT NULL ENABLE,
  	"USERNAME" VARCHAR2(20) NOT NULL ENABLE,
  	"FULL_NAME" VARCHAR2(100) NOT NULL ENABLE,
  	"EMAIL_ADDRESS" VARCHAR2(100) NOT NULL ENABLE,
  	"PWD_EXPIRE" NUMBER(1,0) NOT NULL ENABLE,
  	"PWD" VARCHAR2(60) NOT NULL ENABLE,
  	CONSTRAINT "USERMASTER_PK" PRIMARY KEY ("USER_ID")
  	USING INDEX ENABLE,
  	CONSTRAINT "USERMASTER_UK1" UNIQUE ("USERNAME")
  	USING INDEX ENABLE,
  	CONSTRAINT "USERMASTER_UK2" UNIQUE ("EMAIL_ADDRESS")
  	USING INDEX ENABLE
  	)
  	/
  	CREATE OR REPLACE EDITIONABLE TRIGGER "BI_USERMASTER"
  	before insert on "USERMASTER"
  	for each row
  	begin
  	if :NEW."USER_ID" is null then
  	select "USERMASTER_SEQ".nextval into :NEW."USER_ID" from sys.dual;
  	end if;
  	end;
  	/
  	ALTER TRIGGER "BI_USERMASTER" ENABLE
  	/

  view raw USER_MASTER.sql hosted with ❤ by GitHub

目次に戻る

フルスクラッチから1日でCMSを作る シェルスクリプト高速開発手法入門

シェルスクリプトの開発の必要があり購入



フルスクラッチから1日でCMSを作る シェルスクリプト高速開発手法入門





2016/06/18追記
MACとVirtualBOXでやってみた。面白いです。
Kindle版なくなったみたい…

3.PASSWORDを変更するFUNCTION ：APEX ID/PASSWORD認証機能 - カスタム編 -

目次に戻る

• Goal
  パスワードを変更するFUNCTION
  パスワードの条件(8文字以上、英数字 etc)はAPEX上で実装するので、このファンクションではチェックしていない。パスワード変更画面で、パスワード変更ボタンを押したときに呼び出す。パスワードチェック時及び新パスワードのハッシュ化時は、1.PASSWORDをハッシュ化するFUNCTIONを呼び出す。
• Source
  
  

  	create or replace Function CHGPWD(p_user_id in NUMBER, -- USERMASTER の key
  	p_username in VARCHAR2, -- USERNAME
  	p_old_pwd in VARCHAR2, -- old password
  	p_new_pwd in VARCHAR2, -- new password
  	p_con_pwd in VARCHAR2) -- confirm new password
  	return BOOLEAN
  	IS
  	l_flag NUMBER;
  	BEGIN
  	-- Check　パスワードと確認パスワードの確認(不要かも)
  	IF p_new_pwd != p_con_pwd THEN
  	RETURN FALSE;
  	END IF;
  	-- 現状のパスワードの確認
  	select 1 into l_flag from USERMASTER a
  	where a.USER_ID = p_user_id
  	and a.PWD = SHA1_HASH(p_username,p_old_pwd) ---MD5_HASH(p_username,p_old_pwd)
  	and upper(a.USERNAME) = upper(p_username);
  	-- 現状のパスワードが一致した時のみパスワードを更新する
  	IF l_flag = 1 THEN
  	UPDATE USERMASTER a SET a.PWD = SHA1_HASH(p_username,p_new_pwd) ---MD5_HASH(p_username,p_new_pwd)
  	,a.PWD_EXPIRE = 0 -- Expireをフラグ管理
  	where a.USER_ID = p_user_id
  	and a.PWD = SHA1_HASH(p_username,p_old_pwd) ------MD5_HASH(p_username,p_old_pwd)
  	and upper(a.USERNAME) = upper(p_username);
  	RETURN TRUE;
  	ELSE
  	RETURN FALSE;
  	END IF;
  	END;

  view raw CHGPWD.sql hosted with ❤ by GitHub

目次に戻る

2.ID/PASSWORDをチェックするFUNCTION ：APEX ID/PASSWORD認証機能 - カスタム編 -

目次に戻る

• Goal
  ログイン画面から呼び出すFUNCTION
  IDとパスワードをチェックする
  チェック時に、1.PASSWORDをハッシュ化するFUNCTIONを呼び出す
• Source
  
  

  	CREATE OR REPLACE FUNCTION AUTH (p_username IN VARCHAR2,p_password IN VARCHAR2 )
  	RETURN BOOLEAN
  	IS
  	l_pwd VARCHAR2(60);
  	l_value NUMBER;
  	l_returnvalue BOOLEAN;
  	BEGIN
  	select SHA1_HASH(p_username,p_password) into l_pwd from dual;
  	BEGIN
  	SELECT 1
  	INTO l_value
  	FROM USERMASTER -- User master table --
  	WHERE 1=1
  	AND upper(USERNAME) = upper(p_username)
  	AND upper(PWD) = upper(l_pwd);
  	EXCEPTION
  	WHEN no_data_found
  	OR too_many_rows THEN
  	l_value := 0;
  	WHEN OTHERS THEN
  	l_value := 0;
  	END;
  	l_returnvalue := l_value = 1;
  	RETURN l_returnvalue;
  	END;

  view raw AUTH.sql hosted with ❤ by GitHub

目次に戻る

文字列から特定の文字だけを抜き出す - SQL

• Goal
  文字列から特定の文字だけを抜き出す
  (例)　123 My 4566,,, name .. is 0#$%0 John. からアルファベットだけを抜き出す
  　　　結果として、MYNAMEISJOHNだけを抜き出す
• Source
  
  

  	select replace(translate(<input sentence>,
  	replace(translate(<input sentence>,<search letter>,
  	<dummy letter>),''),'x'),'x','') from dual;
  	--- EX) select alphabet only
  	select replace(translate(upper('123 My 4566,,, name .. is 0#$%0 John.'),
  	replace(translate(upper('123 My 4566,,, name .. is 0#$%0 John.'),
  	'ABCDEFGHIJKLMNOPQRSTUVWXYZ','0'),''),'x'),'x','') from dual;
  	----
  	----Result
  	----
  	MYNAMEISJOHN

  view raw Choose-letters.sql hosted with ❤ by GitHub
  
  
  自動でUSERIDを作って付与するときに使用
  頭6文字の英字＋連番をUSERIDとして使うなど

APEX ID/PASSWORD認証機能 - AD編

• Goal
  APEXアプリのログイン時、Active Directory(アクティブディレクトリ)を使ってユーザ認証する
• How
  共有コンポーネント -> 認証スキーム で作成する
  スキームタイプは、"LDAPディレクトリ"
  ホスト、ポートを入力し、
  認識名(DN)文字列は、ドメイン名\％LDAP_USER%
• Screen Shot

1.PASSWORDをハッシュ化するFUNCTION ：APEX ID/PASSWORD認証機能 - カスタム編 -

目次に戻る

• Goal
  USERのパスワード設定時、また認証時に呼び出すハッシュ化関数を作成する
• Source : MD5のハッシュ化 (9i)
  apex.oracle.com 上ではDBMS_CRYPTOが使えないらしいので、ここではMD5を使用
  (あくまでテスト環境だからか…)

	create or replace function "MD5_HASH"
	(g_user_name in VARCHAR2,
	g_password in VARCHAR2)
	return VARCHAR2
	is
	l_password VARCHAR2 (4000);
	l_salt VARCHAR2 (4000) := 'SALTSALTSALTSALTSALT'; -- Need to change your original.
	BEGIN
	l_password :=
	UTL_RAW.cast_to_raw (DBMS_OBFUSCATION_TOOLKIT.md5 (input_string => g_password
	|| SUBSTR ( l_salt, 13, 16)
	|| upper(g_user_name)
	|| SUBSTR ( l_salt, 4, 10)));
	RETURN l_password;
	end;

view raw MD5_HASH.sql hosted with ❤ by GitHub



• Source : SHA1のハッシュ化(10g / 11g)

	CREATE OR REPLACE function SHA1_HASH(p_username in VARCHAR2,p_password in VARCHAR2)
	return VARCHAR2
	is
	l_password VARCHAR2 (4000);
	l_salt VARCHAR2 (4000) := 'SALTSALTSALTSALTSALTSALT'; -- change with your salt.
	BEGIN
	l_password :=
	DBMS_CRYPTO.HASH(src => UTL_I18N.STRING_TO_RAW(p_password
	|| SUBSTR ( l_salt, 10, 13)
	|| upper(p_username)
	|| SUBSTR( l_salt, 4, 10),'AL32UTF8'),
	typ => DBMS_CRYPTO.HASH_SH1
	);
	RETURN l_password;
	END;

view raw SHA1_HASH.sql hosted with ❤ by GitHub



• Source : SHA2のハッシュ化
  (To be updated.)

目次に戻る

APEX ID/PASSWORD認証機能 - カスタム編 - 目次

APEX のID/パスワードの認証、変更、初期化機能の実装方法

Table of contents
　0.USERMASTERテーブルを作る
　1.PASSWORDをハッシュ化するFUNCTION
　2.ID/PASSWORDをチェックするFUNCTION　
　3.PASSWORDを変更するFUNCTION　
　4.PASSWORDを初期化するPROCEDURE
　5.認証スキームの作成
　6.パスワード変更画面の作成
　7.初回ログイン時、パスワード変更画面へリダイレクト
　8.USER登録画面の作成(Password初期化機能)

APEX_MAIL を使ってプロシージャやファンクションからEmail送信

• Goal
  APEX_MAILを使ってPROCEDUREからEmailを送る
  (もちろんUTL_SMTPでも送れるが、APEX_MAILの方が簡単)
  想定される場面は、Batchでお知らせメールを配信したい場合など
• How
  以下の1行を追加する
  wwv_flow_api.set_security_group_id;
  アプリケーション内から送信する場合は不要
• Source

	create or replace procedure "APEX_MAIL_IN_PROCEDURE"
	is
	l_to VARCHAR2(100) :='<test_email_address>';
	l_from VARCHAR2(100) :='<test_email_address>';
	l_cc VARCHAR2(100);
	l_body VARCHAR2(2000) :='test';
	l_body_html VARCHAR2(4000);
	l_subj VARCHAR2(300) :='test';
	BEGIN
	--
	--Need to add this line.
	wwv_flow_api.set_security_group_id;
	--
	--
	apex_mail.send (p_to => l_to,
	p_from => l_from,
	p_cc => l_cc,
	p_body => l_body,
	p_body_html => l_body_html,
	p_subj => l_subj);
	END;

view raw APEX_MAIL in PROCEDURE.sql hosted with ❤ by GitHub

トリガーで更新日付の自動セット - LAST_UPDATE_DATE

• Goal
  レコードの最終更新日付・最終更新者を自動でセットする
• How
  triggerで実現する
  このソースは、
       LAST_UPDATE_DATE
       LAST_UPDATED_BY
       CREATION_DATE  (INSERT時のみ)
       CREATED_BY (INSERT時のみ)
  を更新する
• SQL

	create or replace TRIGGER "<TRIGGER NAME>"
	BEFORE
	insert or update on "<table name>"
	for each row
	begin
	IF INSERTING
	THEN
	:new.creation_date := sysdate;
	--adding user name(This is for apex apps case.)
	:new.created_by := NVL(v('APP_USER'), 0);
	END IF;
	:new.last_update_date := sysdate;
	--adding user name(This is for apex apps case.)
	:new.last_updated_by := NVL(v('APP_USER'), 0);
	end;

view raw LASTUPDATED_TRIGGER.sql hosted with ❤ by GitHub