The SQL result set in PL/SQL sends emails in html Format

During O & M, sometimes you need to regularly send emails to the SQL query data result set in the form of html tables. Therefore, you need to splice the SQL query result set into html code. There are usually two ways to do this. First, cronjob is directly used for regular round-robin and completed with OS-level email programs. The query result set can be directly in S

During O & M, sometimes you need to regularly send emails to the SQL query data result set in the form of html tables. Therefore, you need to splice the SQL query result set into html code. There are usually two ways to do this. First, the cron job is directly used for regular round-robin and completed with OS-level email programs. The query result set can be directly in S

During O & M, sometimes you need to regularly send emails to the SQL query data html> result set in the form of html tables. Therefore, you need to splice the result set obtained by SQL query into html code. There are usually two ways to do this. First, the cron job is directly used for regular round-robin and completed with OS-level email programs. The query result set can be set in SQL * Plus to automatically implement html table format by setting html tags. One way is to use scheduler job in Oracle for regular round robin. In this way, we need to manually splice html code. This article describes the second case.

For details about how to send emails in PL/SQL, refer to the mail sending program in PL/SQL.

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

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

-- The following code snippet is mainly used to send an error message when synchronizing part of database A data to database B.

-- Table syn_data_err_log_tbl records error logs, that is, as long as a new record or old record appears in the table and the mailed column is marked as N, it indicates that an email needs to be sent.

-- The following describes the code segment information one by one. This code segment can be encapsulated into a 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 -- Use cursor to generate the table title Section

IS

SELECT'

'

| TO_CHAR (sd. log_seq)

|'

'

| Sd. process

|''

|''

| Sd. rec_id

|''

|''

| REPLACE (sd. err_msg, '<', ';'), '> ',';')

|''

|''

| TO_CHAR (sd. log_time, 'yyyy-mm-dd hh24: mi: ss ')

|'

',

Sd. log_seq

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 --> count the total number of records to be sent

FROM syn_data_err_log_tbl sd

WHERE sd. mailed = 'n ';

IF v_count> 0 --> indicates that an email needs to be sent IF a record exists.

THEN

SELECT 'job process failed on' | instance_name | 'HTTP: // www.3lian.com/'| host_name

INTO v_sub --> Generate the subject of the email

FROM v $ instance;

V_html_header: = --> define the header information of a table.

'