In the process of operation, it is sometimes necessary to send the data result set of SQL query in HTML form, so it is necessary to stitch the result set of SQL query into HTML code. There are usually two different ways to do this. One is to use the cron job directly to poll and do it using the OS level mail program. Its query result set can be directly under Sql*plus by setting HTML tags to automatically implement HTML table Form. One way is to use the scheduler job in Oracle to poll periodically. This way we need to manually splice HTML code. This article is to expand the description of the second case.
About Pl/sql How to send a message can be referred to: Pl/sql Mail Send program
1, Code description
?| 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 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 5, 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 11 9 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 | --The following code snippet is mainly used to send the database a part of the data synchronization to database B is the error message--table syn_data_err_log_tbl is mainly to record error log, that is, whenever a new record or old record appears in the table and the mailed column flag is n, This means that you need to send a message--the following describes the code snippet information, which can be encapsulated into package. procedure Email_on_syn_data_err_log (err_num out number, err_msg out VARCHAR2) as v_msg_txt VARCHAR2 (32767); v_sub VARCHAR2 (100); v_html_header VARCHAR (4000); v_html_content VARCHAR (32767); v_count number; v_log_seq number (12); v_loop_count number: = 0; cursor cur_errlog --using CURSOR to generate table header sections is select ' <tr > &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;<TD style= "vertical-align:top;padding:5px;" > ' | | To_char (SD.LOG_SEQ) | | ' </td> &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;<TD style= "Vertical-align:top; padding:5px; " > ' | | Sd.process | | ' </td> ' | | ' <td style= ' vertical-align:top;padding:5px; > ' | | sd.rec_id | | ' </td> ' | | ' <td style= ' padding:5px; > ' &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp; | | Replace (replace (sd.err_msg, ' < ', '; '), ' > ', '; ') | | ' </td> ' | | ' <td style= ' vertical-align:top;padding:5px; > ' | | To_char (Sd.log_time, ' yyyy-mm-dd hh24:mi:ss ') | | ' </td> </tr> ', sd.log_seq &NBSP;&NBSp; from syn_data_err_log_tbl SD where sd.mailed = ' N ' order by Sd.log_seq; begin err_num: = common_pkg.c_suc_general; select COUNT (*) into v_count --> Statistics The total number of records to be sent at the time from syn_data_err_log_tbl SD where sd.mailed = ' N '; if v_count > 0 --> Indicates that there are records to send mail then SELECT ' Job process failed on ' | | instance_name | | '/' || HOST_NAME &Nbsp; into v_sub --> Generate mail subject from v$instance; v_html_header:= --> defines the header part information of the table ' <html><header><style> #log-table { margin:0; padding:0; width:90%; border-collapse:collapse; font:12px "Lucida Grande", Helvetica, Sans-serif; border:1px solid #CCC; } #log-table td { padding:5px; border:1px solid #CCC; } #log-table th { padding:5px; background:black; color:white; text-align:left; } #log-table Tr:nth-child (even) TD { background: #eee; } </style></header><body> <table id= "log-table" style= "width:100%;border-collapse:collapse;font-size:12px ;" > '; v_html_header:= --> The following is the information for stitching each field &NBSP;&NBSP;&NBSP;&NBSP;&NBSp; v_html_header | | ' <tr style= ' background:black; > <th style= "color:white;width:100px;padding:5px;" >log sequence</th> <th style= "color:white;width:100px;padding:5px;" >Process</th> <th style= "color:white;width:100px;padding:5px;" >rec id</th> <th style= "color:white;width:100px;padding:5px;" >error messAge</th> <th style= "color:white;padding:5px;" >log time</th></tr> '; open cur_errlog; --> Open cursor loop fetch cur_errlog into V_msg_txt, V_log_seq; exit when Cur_errlog%NOTFOUND; v_loop_count: = V_loop_count + 1; v_html_content: = V_html_content | | v_msg_txt; ---> Note here, keep err_msg from the original table to be spliced through V_msg_txt &NBsp --maximun record = M- if v_loop_count > 50 ---> The judgment here is to control how many rows the table shows in total THEN -- -> is primarily used to truncate from 50 lines if too many rows are required to splice so that they exceed the character length 32767 v_html_content: = v_html_header | | v_html_content | | ' </table></body></html> '; ---> Add HTML tail here sendmail_pkg.sendmail ( bo_system_pkg.get_sys_para_value (' email_sender_hc_email '), --- > Call function to obtain the recipient of the message, where you can write directly to the recipient v_sub, v_html_content, err_num, &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;ERR_MSG); v_msg_txt: = '; ---> Note, place null for three local variables &Nbsp; v_html_content: = '; v_loop_count: = 0; update Syn_data_err_log_tbl sd ---> marks the records that have been sent according to the Log_seq field as Y set mailed = ' Y ' where sd.mailed = ' N ' and log_seq <= v_log_seq; --COMMIT; elsif V_count = cur_errlog%ROWCOUNT ---> When v_count and cursors get the same number of records, splice the end of the table HTML code, send mail and update mailed columns then v_ Html_content: = v_html_header | | v_html_content | | ' </table></body></html> '; sendmail_ Pkg.sendmail ( bo_system_pkg.get_sys_para_value (' Email_sender_hc_email '), v_sub, v_html_content, err_num, &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;ERR_MSG); v_msg_txt: = '; v_html_content: = '; update Syn_data _ERR_LOG_TBL SD set mailed = ' Y ' where sd.mailed = ' N ' and log_seq <= v_log_seq; end IF; end LOOP; COMMIT; close Cur_errlog; &nBsp; end IF; exception when No_data_found then err_num: = Common_pkg.c_fail_data_not_found; when others then err_num: = Common_pkg.c_fail_user_define; err_msg: = ' Fail in process Sendmail_pkg.email_on_syn_data_ Err_log. '; END; |
2. Calling examples and message styles
?| 1 2 3 4 5 6 7 8 9 10 11 12 13-14 | Gx_admin@sybo2sz> DECLARE 2 err_num number; 3 err_msg VARCHAR2 (32767); 4 5 BEGIN 6 Err_num: = NULL; 7 err_msg: = NULL; 8 9 Gx_admin. Sendmail_pkg. Email_on_syn_data_err_log (Err_num, err_msg); Ten commits; one end; 12/pl/sql procedure successfully completed. |
More references
Using Dbms_profiler to locate Pl/sql bottleneck code
Profiling Pl/sql code with PL/SQL developer
Contrast Pl/sql Profiler Analysis results
Pl/sql Profiler profiling report generates HTML
DML Error Logging characteristics
Pl/sql--> Cursors
Pl/sql--> implicit cursors (sql%found)
ForAll Statement of Batch SQL
BULK COLLECT clause of batch SQL
Initialization and assignment of Pl/sql sets
Pl/sql combined arrays with nested tables
SQL Tuning Steps
Efficient SQL statements must kill technology
Parent cursors, child cursors, and shared cursors
Binding variables and their pros and cons
The use of the Display_cursor function of Dbms_xplan
The use of display function of Dbms_xplan
Module descriptions for each field in the execution plan
Get SQL statement execution plans using EXPLAIN plan
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
