Read listener.log from a table

Read listener.log from a table

--DROP DIRECTORY LISTENER_LOG
--DROP TABLE LISTENER_TABLE

CREATE OR REPLACE DIRECTORY LISTENER_LOG AS '/software/oracle/diag/tnslsnr/my_server/lsnr_igt/trace';

CREATE TABLE LISTENER_TABLE (line varchar2(4000) )
ORGANIZATION EXTERNAL (
 type oracle_loader
 default directory LISTENER_LOG
 access parameters
( records delimited by newline
  nobadfile
  nologfile
  nodiscardfile
  fields ldrtrim
  missing field values are null
  reject rows with all null fields
 ( line char(4000) )
 )
location ('lsnr_igt.log')
)
reject limit unlimited;

select * from LISTENER_TABLE;


CREATE TABLE LISTENER_TABLE_CONNECT (connect_date VARCHAR2(100),
                                     connect_str  VARCHAR2(4000) )
TABLESPACE IGT_TABLE;

INSERT /*+ APPEND */ INTO LISTENER_TABLE_CONNECT (connect_date, connect_str)
SELECT SUBSTR(line,1,20), line
FROM LISTENER_TABLE
WHERE line LIKE '%CONNECT_DATA%'
  AND line LIKE '%JUL-2021%'