-- v 0.9 - first public release
-- (c) 2007-2009 by Red-Database-Security GmbH
create table listener_log (
   log_date date,
   connect_string varchar2(300), 
   protocol_info varchar2(300),
   action varchar2(15),
   service_name varchar2(15),
   return_code number(10)
)
organization external (
   type oracle_loader
   default directory LISTENER_LOG_DIR
   access parameters   (
      records delimited by newline
      nobadfile 
      nologfile
      nodiscardfile
      fields terminated by "*" lrtrim
      missing field values are null  (
          log_date char(30) date_format 
          date mask "DD-MON-YYYY HH24:MI:SS",
          connect_string,
          protocol_info,
          action,
          service_name,
          return_code      )    )
   location ('listener.log'))
reject limit unlimited
/



 create or replace function parse_listener_log_line
    (
        p_in varchar2,
        p_param in varchar2
    )
    return varchar2
    as
        l_begin     number(3);
        l_end       number(3);
       l_val       varchar2(2000);
   begin
       if p_param not in (
               'SID', 'SERVICE_NAME','PROGRAM','SERVICE',
               'HOST','USER', 'PROTOCOL','TYPE',
               'METHOD','RETRIES', 'DELAY','PORT','COMMAND'
       ) then
           raise_application_error (-20001,'Invalid Parameter Value '||p_param);
       end if;
       l_begin := instr (upper(p_in), '('||p_param||'=');
       l_begin := instr (upper(p_in), '=', l_begin);
       l_end := instr (upper(p_in), ')', l_begin);
       l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);
       return l_val;
  end;
 /



end - l_begin - 1);
       return l_val;
  end;
 /