3、创建分析函数
到此为止，我们以及把监听器日志装入一个扩展表(full_listener_log)，然后又将日志信息进行了分解，并大致分解为6个字段，使用扩展表(listener_log)进行存储，但在listener_log这个表中存储的字段信息还有嵌套信息，也就是说还可以进行分解，可以再设计几个表来存储进一步分解的信息，但这里我使用一个函数来完成这件事情，它接收两个参数：查询时使用的字段值和要查询的字符串：
1 create or replace function parse_listener_log_line
2 (
3 p_in varchar2, // 你要在哪个字段中查询
4 p_param in varchar2 //你需要查询的字符串
5 )
6 return varchar2
7 as
8 l_begin number(3);
9 l_end number(3);
10 l_val varchar2(2000);
11 begin
12 if p_param not in (
13 'SID',
14 'SERVICE_NAME',
15 'PROGRAM',
16 'SERVICE',
17 'HOST',
18 'USER',
19 'PROTOCOL',
20 'TYPE',
21 'METHOD',
22 'RETRIES',
23 'DELAY',
24 'PORT',
25 'COMMAND'
26 ) then //定义要查询的字符的有效范围
27 raise_application_error (-20001,'Invalid Parameter Value '||p_param);
28 end if;
29 l_begin := instr (upper(p_in), '('||p_param||'=');
30 l_begin := instr (upper(p_in), '=', l_begin);
31 l_end := instr (upper(p_in), ')', l_begin);
32 l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);
33 return l_val;
34* end; //定义要查询的实际的值是否存在，而不是括号和等号的位置
2 (
3 p_in varchar2, // 你要在哪个字段中查询
4 p_param in varchar2 //你需要查询的字符串
5 )
6 return varchar2
7 as
8 l_begin number(3);
9 l_end number(3);
10 l_val varchar2(2000);
11 begin
12 if p_param not in (
13 'SID',
14 'SERVICE_NAME',
15 'PROGRAM',
16 'SERVICE',
17 'HOST',
18 'USER',
19 'PROTOCOL',
20 'TYPE',
21 'METHOD',
22 'RETRIES',
23 'DELAY',
24 'PORT',
25 'COMMAND'
26 ) then //定义要查询的字符的有效范围
27 raise_application_error (-20001,'Invalid Parameter Value '||p_param);
28 end if;
29 l_begin := instr (upper(p_in), '('||p_param||'=');
30 l_begin := instr (upper(p_in), '=', l_begin);
31 l_end := instr (upper(p_in), ')', l_begin);
32 l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);
33 return l_val;
34* end; //定义要查询的实际的值是否存在，而不是括号和等号的位置
4、测试分析函数
至此，准备工作已经就绪，可以开始进行数据挖掘了，但开始之前先测试一下前面这个函数是否可以正常工作，我们查询一下CONNECT_STRING字段的值：
sql>select CONNECT_STRING from listener_log;
返回：
CONNECT_STRING
--------------------------------------------------------------------- (CONNECT_DATA=(SID=proprd1)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=)))
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=prolin01)(USER=oraprol))(COMMAND=status)(ARG
UMENTS=64)(SERVICE=LISTENER_PROLIN01)(VERSION=168821760))
(CONNECT_DATA=(SID=proprd1)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=)))
(CONNECT_DATA=(SID=proprd1)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=)))
--------------------------------------------------------------------- (CONNECT_DATA=(SID=proprd1)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=)))
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=prolin01)(USER=oraprol))(COMMAND=status)(ARG
UMENTS=64)(SERVICE=LISTENER_PROLIN01)(VERSION=168821760))
(CONNECT_DATA=(SID=proprd1)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=)))
(CONNECT_DATA=(SID=proprd1)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=)))
下面我使用这个分析函数来分析一下变量USER的值，实际上，从前面返回的结果已经看出，有的行USER变量有值，有的行USER变量没有值，按照前面函数给出的定义，要给函数提供两个参数：CONNECT_STRING和’USER’，如：
SQL> set null ?
SQL> select parse_listener_log_line(connect_string,'USER')
2 from listener_log;
PARSE_LISTENER_LOG_LINE(CONNEC
-------------------------------------
?
?
?
?
?
?
oraprol
?
?
SQL> select parse_listener_log_line(connect_string,'USER')
2 from listener_log;
PARSE_LISTENER_LOG_LINE(CONNEC
-------------------------------------
?
?
?
?
?
?
oraprol
?
?
这里使用?代替空值显示，从函数返回的结果看，它能够正常分析出给定变量的值。
