Use Oracle stored procedures to send messages

Recently, some programs written as stored procedures, has always wanted to notify me by email, the results of each implementation is correct, on the Internet to find a bit through the Oracle email, a find it, debugging can be used, now recorded, so that later learning to use!

At present, I plan to some statistical reports and database information, and so on, every day can be sent to my mailbox, so that daily routine work simple, efficient, although it is from the Internet "copy" to, but will carefully study the code, improve!

1. First create DEMO_BASE64 package, this includes a function encode is mainly used to decode

CREATE OR REPLACE PACKAGE demo_base64 IS
-- Base64-encode a piece of binary data.
--
-- Note that this encode function does not split the encoded text into
-- multiple lines with no more than 76 bytes each as required by
-- the MIME standard.
- -
FUNCTION encode(r IN RAW) RETURN VARCHAR2;
END;

2. Create Demo_base64 Package Body

CREATE OR REPLACE PACKAGE body demo_base64 is


TYPE vc2_table is Table of VARCHAR2 (1) INDEX by Binary_integer;


map vc2_table;


--Initialize the Base64 mapping


PROCEDURE Init_map is


BEGIN


Map (0): = ' A '; Map (1): = ' B '; Map (2): = ' C '; Map (3): = ' D '; Map (4): = ' E ';


Map (5): = ' F '; Map (6): = ' G '; Map (7): = ' H '; Map (8): = ' I '; Map (9): = ' J ';


map: = ' K '; Map (one): = ' L '; Map (a): = ' M '; Map (a): = ' N '; Map (a): = ' O ';


map: = ' P '; Map (): = ' Q '; Map (a): = ' R '; Map: = ' S '; Map (n): = ' T ';


map: = ' U '; Map: = ' V '; Map (+): = ' W '; Map (%): = ' X '; Map (in): = ' Y ';


map: = ' Z '; Map (num): = ' a '; Map (a): = ' B '; Map (a): = ' C '; Map (c): = ' d ';


Map: = ' E '; Map (a): = ' F '; Map (+): = ' g '; Map (a): = ' h '; Map (a): = ' I ';


map (k): = ' J '; Map (+): = ' k '; Map (Panax Notoginseng): = ' l '; Map (pdf): = ' m '; Map (+): = ' n ';


map (+): = ' O '; Map (a): = ' P '; Map (a): = ' Q '; Map (a): = ' R '; Map: = ' s ';


map (): = ' t '; Map (+): = ' u '; Map (+): = ' V '; Map (m): = ' W '; Map (a): = ' x ';


map (m): = ' Y '; Map (Wuyi): = ' Z '; Map (52): = ' 0 '; Map (53): = ' 1 '; Map (54): = ' 2 ';


Map (55): = ' 3 '; Map (56): = ' 4 '; Map (57): = ' 5 '; Map (58): = ' 6 '; Map (59): = ' 7 ';


Map (60): = ' 8 '; Map (61): = ' 9 '; Map (62): = ' + '; Map (63): = '/';


end;


FUNCTION encode (R in RAW) return VARCHAR2 is


i Pls_integer;


x Pls_integer;


y Pls_integer;


v VARCHAR2 (32767);


BEGIN


-For every 3 bytes, split them into 4 6-bit units and map them to


--The Base64 characters


I: = 1;


while (i + 2 <= utl_raw.length (r)) LOOP


x: = To_number (Utl_raw.substr (R, I, 1), ' 0X ') * 65536 +


To_number (Utl_raw.substr (R, i + 1, 1), ' 0X ') * 256 +


To_number (Utl_raw.substr (R, i + 2, 1), ' 0X ');


y: = Floor (x/262144); V: = V | | Map (y); x: = XY * 262144;


y: = Floor (x/4096); V: = V | | Map (y); x: = XY * 4096;


Y: = Floor (X/64); V: = V | | Map (y); x: = XY * 64;


V: = v | | Map (x);


I: = i + 3;


End LOOP;


-Process The remaining bytes that has fewer than 3 bytes.


IF (Utl_raw.length (r)-i = 0) THEN


x: = To_number (Utl_raw.substr (R, I, 1), ' 0X ');


y: = Floor (X/4); V: = V | | Map (y); x: = XY * 4;


x: = x * 16; V: = V | | Map (x);


V: = v | | '==';


elsif (Utl_raw.length (r)-i = 1) THEN


x: = To_number (Utl_raw.substr (R, I, 1), ' 0X ') * 256 +


To_number (Utl_raw.substr (R, i + 1, 1), ' 0X ');


y: = Floor (x/1024); V: = V | | Map (y); x: = XY * 1024;


y: = Floor (X/16); V: = V | | Map (y); x: = XY * 16;


x: = x * 4; V: = V | | Map (x);


V: = v | | '=';


End IF;


return v;


end;


BEGIN


Init_map;


end;