PL/SQL EXCEL OLUŞTURMA ve MAIL OLARAK GÖNDERME
Merhaba,
PL/SQL kullanarak UTL_FILE standart paketi ile XML oluşturup Excel ile açma imkanı mevcuttur. Ancak sadece XLS dosyaları oluşturulabilinir. 2010 Destekli Excel dosyaları oluşturmak için Java (Apache POI) gibi daha geniş bir programlama dili kullanılmalıdır. Bu örnekte oluşturacağımız Excel dosyasını bir mail sunucusu üzerinden mail olarak da göndereceğiz. Mail gönderebilmek için daha önceden yazılmış olan UTL_TCP kullanarak mail gönderen hazır bir paket kullanacağız. Paketi aşağıdaki link’ten download edebilirsiniz.
Paketi derlerken pks spec dosyası içinde tanımlanan global değişkenlerin kendi mail domain ve smtp portunuza uygun olarak değiştirilmesi gerekmektedir.
|
1 2 3 4 5 |
smtp_host VARCHAR2(256) := 'mail.domain.com'; smtp_port PLS_INTEGER := 587; --25 smtp_domain VARCHAR2(256) := 'domain.com'; |
Gerekli değişiklik yapıldıktan sonra son olarak mail adreslerini tutacak olan custom bir varchar2 collection objesi aşağıdaki kodla yaratılır.
|
1 2 3 |
create or replace type hr."VARCHAR2_TABLE_100" is table of varchar2 ( 100 ); |
Yukarıdaki işlemler tamamlandıktan sonra mail paketi uygun ortamda derlenir.
Bu aşamada örnek için kullanacağımız bir Custom tablo, sequence, directory ve insert scriptleri için aşağıdaki kodu kullanabilirsiniz. Directory için kendi lokal ortamınızda veya kodu çalıştırdığınız sunucuda uygun bir dizin belirleyiniz. Bu dizin excel dosyanızın oluşacağı dizin olacaktır.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE OR REPLACE DIRECTORY XX_EXCEL_DIR AS 'C:\Users\Anil\Desktop'; CREATE TABLE hr.excel_test_table ( sira_no NUMBER, ad VARCHAR2 (200), soyad VARCHAR2 (200), dogum_tarihi DATE, maas NUMBER ); CREATE SEQUENCE hr.excel_test_table_s START WITH 1 INCREMENT BY 1 MINVALUE 1 NOCACHE NOCYCLE NOORDER; ALTER TABLE hr.excel_test_table ADD CONSTRAINT excel_test_table_pk PRIMARY KEY (sira_no); INSERT INTO hr.excel_test_table VALUES (hr.excel_test_table_s.NEXTVAL, 'ANIL', 'ALTUNKAN', TO_DATE ('30.12.1987', 'dd.mm.rrrr'), 6000); INSERT INTO hr.excel_test_table VALUES (hr.excel_test_table_s.NEXTVAL, 'TEST1', 'TEST1', TO_DATE ('05.08.1989', 'dd.mm.rrrr'), 5000); INSERT INTO hr.excel_test_table VALUES (hr.excel_test_table_s.NEXTVAL, 'TEST2', 'TEST2', TO_DATE ('10.10.1988', 'dd.mm.rrrr'), 4000); INSERT INTO hr.excel_test_table VALUES (hr.excel_test_table_s.NEXTVAL, 'TEST3', 'TEST3', TO_DATE ('17.01.1990', 'dd.mm.rrrr'), 3750); |
Excel dosyasını oluşturmak ve mail olarak göndermek için yeni bir PL/SQL paketi yaratılır. Spec dosyasında 8 adet prosedür ve 1 adet global değişken yer almaktadır.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE OR REPLACE PACKAGE hr.create_excel_pkg AS l_directory varchar2(100) := 'XX_EXCEL_DIR'; PROCEDURE p_start; PROCEDURE start_workbook (p_file_type in utl_file.file_type); PROCEDURE end_workbook (p_file_type in utl_file.file_type); PROCEDURE start_worksheet (p_sheetname IN VARCHAR2, p_file_type in utl_file.file_type); PROCEDURE end_worksheet (p_file_type in utl_file.file_type); PROCEDURE set_style (p_file_type in utl_file.file_type); PROCEDURE run_query (p_sql IN VARCHAR2, p_file_type in utl_file.file_type); PROCEDURE p_send_mail (p_file_name IN VARCHAR2, p_to_mail IN VARCHAR2); END create_excel_pkg; |
l_directory daha önce oluşturduğumuz dosyanın oluşacağı lokal dizindir. p_start prosedürü diğer prosedürleri çağırarak excel oluşturma işlemini ve en sonda da mail gönderme işlemini yapan prosedürdür.
Paketin Body’si için aşağıdaki kodu derleyebilirsiniz;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 |
CREATE OR REPLACE PACKAGE BODY hr.create_excel_pkg AS PROCEDURE p_start IS l_file_type UTL_FILE.file_type; l_file_name VARCHAR2 (200); l_sql VARCHAR2 (600) := 'select sira_no, ad, soyad, dogum_tarihi, maas from hr.excel_test_table'; BEGIN l_file_name := 'ANIL_TEST.xls'; l_file_type := UTL_FILE.fopen (l_directory, l_file_name, 'w'); start_workbook (l_file_type); set_style (l_file_type); start_worksheet ('Personel Listesi', l_file_type); run_query (l_sql, l_file_type); end_worksheet (l_file_type); end_workbook (l_file_type); UTL_FILE.fclose (l_file_type); p_send_mail (l_file_name, 'anil@natica.com.tr'); END p_start; PROCEDURE start_workbook (p_file_type IN UTL_FILE.file_type) IS BEGIN UTL_FILE. PUT_LINE (p_file_type, '<?xml version="1.0" encoding="windows-1254"?>'); UTL_FILE. PUT_LINE ( p_file_type, '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'); END; PROCEDURE end_workbook (p_file_type IN UTL_FILE.file_type) IS BEGIN UTL_FILE.PUT_LINE (p_file_type, '</ss:Workbook>'); END; -- PROCEDURE start_worksheet (p_sheetname IN VARCHAR2, p_file_type IN UTL_FILE.file_type) IS BEGIN UTL_FILE. PUT_LINE (p_file_type, '<ss:Worksheet ss:Name="' || p_sheetname || '">'); UTL_FILE.PUT_LINE (p_file_type, '<ss:Table>'); END; PROCEDURE end_worksheet (p_file_type IN UTL_FILE.file_type) IS BEGIN UTL_FILE.PUT_LINE (p_file_type, '</ss:Table>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Worksheet>'); END; -- PROCEDURE set_style (p_file_type IN UTL_FILE.file_type) IS BEGIN UTL_FILE.PUT_LINE (p_file_type, '<ss:Styles>'); UTL_FILE.PUT_LINE (p_file_type, '<ss:Style ss:ID="OracleDate">'); UTL_FILE. PUT_LINE (p_file_type, '<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Style>'); UTL_FILE.PUT_LINE (p_file_type, '<ss:Style ss:ID="bgColor">'); UTL_FILE. PUT_LINE (p_file_type, '<ss:Interior ss:Color="#FFA500" ss:Pattern="Solid"/>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Style>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Styles>'); END; PROCEDURE run_query (p_sql IN VARCHAR2, p_file_type IN UTL_FILE.file_type) IS v_v_val VARCHAR2 (4000); v_n_val NUMBER; v_d_val DATE; v_ret NUMBER; c NUMBER; d NUMBER; col_cnt INTEGER; f BOOLEAN; rec_tab DBMS_SQL.DESC_TAB; col_num NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (c, p_sql, DBMS_SQL.NATIVE); d := DBMS_SQL.EXECUTE (c); DBMS_SQL.DESCRIBE_COLUMNS (c, col_cnt, rec_tab); FOR j IN 1 .. col_cnt LOOP CASE rec_tab (j).col_type WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN (c, j, v_v_val, 4000); WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN (c, j, v_n_val); WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN (c, j, v_d_val); ELSE DBMS_SQL.DEFINE_COLUMN (c, j, v_v_val, 4000); END CASE; END LOOP; -- Output the column headers UTL_FILE.PUT_LINE (p_file_type, '<ss:Row>'); FOR j IN 1 .. col_cnt LOOP UTL_FILE.PUT_LINE (p_file_type, '<ss:Cell>'); UTL_FILE. PUT_LINE ( p_file_type, '<ss:Data ss:Type="String">' || rec_tab (j).col_name || '</ss:Data>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Cell>'); END LOOP; UTL_FILE.PUT_LINE (p_file_type, '</ss:Row>'); -- Output the data LOOP v_ret := DBMS_SQL.FETCH_ROWS (c); EXIT WHEN v_ret = 0; UTL_FILE.PUT_LINE (p_file_type, '<ss:Row>'); FOR j IN 1 .. col_cnt LOOP CASE rec_tab (j).col_type WHEN 1 THEN DBMS_SQL.COLUMN_VALUE (c, j, v_v_val); UTL_FILE.PUT_LINE (p_file_type, '<ss:Cell>'); UTL_FILE. PUT_LINE ( p_file_type, '<ss:Data ss:Type="String">' || v_v_val || '</ss:Data>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Cell>'); WHEN 2 THEN DBMS_SQL.COLUMN_VALUE (c, j, v_n_val); IF v_n_val > 4500 THEN UTL_FILE. PUT_LINE (p_file_type, '<ss:Cell ss:StyleID="bgColor">'); ELSE UTL_FILE.PUT_LINE (p_file_type, '<ss:Cell>'); END IF; UTL_FILE. PUT_LINE ( p_file_type, '<ss:Data ss:Type="Number">' || TO_CHAR (REPLACE (v_n_val, ',', '.')) || '</ss:Data>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Cell>'); WHEN 12 THEN DBMS_SQL.COLUMN_VALUE (c, j, v_d_val); UTL_FILE. PUT_LINE (p_file_type, '<ss:Cell ss:StyleID="OracleDate">'); UTL_FILE. PUT_LINE ( p_file_type, '<ss:Data ss:Type="DateTime">' || TO_CHAR (v_d_val, 'YYYY-MM-DD"T"HH24:MI:SS') || '</ss:Data>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Cell>'); ELSE DBMS_SQL.COLUMN_VALUE (c, j, v_v_val); UTL_FILE.PUT_LINE (p_file_type, '<ss:Cell>'); UTL_FILE. PUT_LINE ( p_file_type, '<ss:Data ss:Type="String">' || v_v_val || '</ss:Data>'); UTL_FILE.PUT_LINE (p_file_type, '</ss:Cell>'); END CASE; END LOOP; UTL_FILE.PUT_LINE (p_file_type, '</ss:Row>'); END LOOP; DBMS_SQL.CLOSE_CURSOR (c); END run_query; PROCEDURE p_send_mail (p_file_name IN VARCHAR2, p_to_mail IN VARCHAR2) IS l_conn UTL_SMTP.CONNECTION; l_subject VARCHAR2 (100); l_subject_utf8 VARCHAR2 (200); l_mime_type_bin VARCHAR2 (30) := 'application/xls'; BOUNDARY CONSTANT VARCHAR2 (256) := '-----7D81B75CCC90D2974F7A1CBD' ; MULTIPART_MIME_TYPE CONSTANT VARCHAR2 (256) := 'multipart/mixed; boundary="' || BOUNDARY || '"' ; l_file BFILE; l_file_len PLS_INTEGER; L_MAX_LINE_WIDTH PLS_INTEGER := 54; l_buf RAW (4000); l_amt BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */ l_pos PLS_INTEGER := 1; /* pointer for each piece */ l_filepos PLS_INTEGER := 1; /* pointer for the file */ l_file_handle UTL_FILE.FILE_TYPE; l_line VARCHAR2 (1000); l_mesg VARCHAR2 (32767); l_mesg_len NUMBER; l_crlf VARCHAR2 (2) := CHR (13) || CHR (10); l_data RAW (4000); l_chunks PLS_INTEGER; l_len PLS_INTEGER := 1; l_modulo PLS_INTEGER; l_pieces PLS_INTEGER; l_err_num NUMBER; l_err_msg VARCHAR2 (100); L_FROM_ENC VARCHAR2 (50) := 'AMERICAN_AMERICA.WE8ISO8859P9'; L_TO_ENC VARCHAR2 (50) := 'TURKISH_TURKEY.TR8MSWIN1254'; BEGIN l_subject := 'Test.'; l_subject_utf8 := UTL_RAW. cast_to_varchar2 ( UTL_RAW. CONVERT (UTL_RAW.CAST_TO_RAW (l_subject), L_TO_ENC, L_FROM_ENC)); l_conn := xx_mail_package.begin_mail (sender => 'altunkan@gmail.com', recipients => p_to_mail, subject => l_subject_utf8, mime_type => MULTIPART_MIME_TYPE); xx_mail_package.begin_attachment (conn => l_conn, mime_type => l_mime_type_bin, inline => TRUE, filename => p_file_name, transfer_enc => 'base64'); BEGIN l_file := BFILENAME (l_directory, p_file_name); l_file_len := DBMS_LOB.getlength (l_file); l_modulo := MOD (l_file_len, l_amt); l_pieces := TRUNC (l_file_len / l_amt); IF l_modulo <> 0 THEN l_pieces := l_pieces + 1; END IF; DBMS_LOB.fileopen (l_file, DBMS_LOB.file_readonly); DBMS_LOB.read (l_file, l_amt, l_filepos, l_buf); l_data := NULL; FOR i IN 1 .. l_pieces LOOP l_filepos := i * l_amt + 1; l_file_len := l_file_len - l_amt; l_data := UTL_RAW.CONCAT (l_data, l_buf); l_chunks := TRUNC (UTL_RAW.LENGTH (l_data) / L_MAX_LINE_WIDTH); IF i <> l_pieces THEN l_chunks := l_chunks - 1; END IF; xx_mail_package. write_raw (conn => l_conn, MESSAGE => UTL_ENCODE.base64_encode (l_data)); l_data := NULL; IF l_file_len < l_amt AND l_file_len > 0 THEN l_amt := l_file_len; END IF; DBMS_LOB.read (l_file, l_amt, l_filepos, l_buf); END LOOP; END; DBMS_LOB.fileclose (l_file); xx_mail_package.end_attachment (conn => l_conn); xx_mail_package.end_mail (conn => l_conn); EXCEPTION WHEN NO_DATA_FOUND THEN xx_mail_package.end_attachment (conn => l_conn); DBMS_LOB.fileclose (l_file); WHEN OTHERS THEN xx_mail_package.end_attachment (conn => l_conn); l_err_num := SQLCODE; l_err_msg := SUBSTR (SQLERRM, 1, 100); DBMS_OUTPUT.put_line ('Error number is ' || l_err_num); DBMS_OUTPUT.put_line ('Error message is ' || l_err_msg); DBMS_LOB.fileclose (l_file); END p_send_mail; END create_excel_pkg; |
Daha sonra SQL’i çalıştırarak verileri içerisine aynı hizada oluşturur.
Excel dosyasını oluştururken hücreler için koşul bazlı stil belirleyebilirsiniz. Bu örnekte maaşı 4500’den büyük olan kişiler için arka plan turuncu yapılmaktadır. Bunun için set_style prosedürü içerisinde oluşturulan “bgColor” stili kullanılır.
Son olarak mail göndermek için daha önce oluşturduğumuz XX_MAIL_PACKAGE paketinin yardımıyla Excel’i ek olarak gönderen bir mail p_send_mail prosedürü içerisinde hazırlanır.
Sonuç:
