Starting with Oracle version 21c, in addition to the alert.log, a new file is delivered: the Attention.log. This file is intended to contain important information as a summary of the alert.log.

Well, I hope this gets improved a bit, but still, it would be useful to read the file using a SELECT statement. I was hoping someone had already done the work (Google, where are you when you need it...), but unfortunately, I found nothing.

Okay, then I’ll do it myself…

We give the objects to the SYSTEM user, but of course, you can also create another user for this purpose.

ALTER SESSION SET current_schema=system;
col adir new_value adir
col afile new_value afile
with diag as (select sys_context('userenv','PLATFORM_SLASH') as ps,value from v$diag_info
where name='Attention Log')
select 
substr(value,1,instr(value,ps,-1)-1) as adir,
substr(value,instr(value,ps,-1)+1) as afile
from diag;

CREATE OR REPLACE DIRECTORY attention_dir as '&adir.';
GRANT READ ON DIRECTORY attention_dir to system;

CREATE OR REPLACE TYPE attention_type 
AS OBJECT (
   NOTIFICATION    VARCHAR2(4000), 
   ERROR           VARCHAR2(4000),
   URGENCY         VARCHAR2(4000),
   INFO            VARCHAR2(4000),
   CAUSE           VARCHAR2(4000),
   ACTION          VARCHAR2(4000),
   CLASS           VARCHAR2(4000),
   TIME            TIMESTAMP WITH TIME ZONE    
);
/

CREATE OR REPLACE TYPE attention_tab_type AS TABLE OF attention_type;
/

CREATE OR REPLACE FUNCTION read_attention_log 
RETURN attention_tab_type PIPELINED
IS
 f_handle  utl_file.file_type:=utl_file.fopen(
 location=>'ATTENTION_DIR',
 filename=>'&afile.',
 open_mode=>'r',
 max_linesize=>32767);
 text         varchar2(32767);
 v_a_log attention_type:=attention_type(null,null,null,null,null,null,null,null);
 v_i INT:=1;
BEGIN
 LOOP 
     BEGIN
     utl_file.get_line(f_handle,text); -- Neue Zeile lesen
   IF substr(text,1,16)='  "NOTIFICATION"' THEN
       v_a_log.NOTIFICATION:=rtrim(rtrim(substr(text,21),','),'"');
   END IF;
   IF substr(text,1,9)='  "ERROR"' THEN 
       v_a_log.ERROR:=rtrim(rtrim(substr(text,21),','),'"');
   END IF;
   IF substr(text,1,11)='  "URGENCY"' THEN    
       v_a_log.URGENCY:=rtrim(rtrim(substr(text,21),','),'"');
   END IF;
   IF substr(text,1,8)='  "INFO"' THEN    
       v_a_log.INFO:=rtrim(rtrim(substr(text,21),','),'"');
   END IF;  
   IF substr(text,1,9)='  "CAUSE"' THEN
       v_a_log.CAUSE:=rtrim(rtrim(substr(text,21),','),'"');
   END IF;
   IF substr(text,1,10)='  "ACTION"' THEN
       v_a_log.ACTION:=rtrim(rtrim(substr(text,21),','),'"');
   END IF;
    IF substr(text,1,9)='  "CLASS"' THEN
       v_a_log.CLASS:=rtrim(rtrim(substr(text,21),','),'"');
   END IF;
   IF substr(text,1,8)='  "TIME"' THEN
       v_a_log.TIME:=to_timestamp_tz(rtrim(substr(text,21),'"'),'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM');
       PIPE ROW (v_a_log);
       v_a_log :=attention_type(null,null,null,null,null,null,null,null);
   END IF;
     EXCEPTION 
    WHEN NO_DATA_FOUND THEN EXIT; -- Keine Zeile im Attention.log mehr gefunden oder Fehler=> Schleife verlassen
    WHEN OTHERS THEN RAISE;
     END;
 END LOOP; 
 utl_file.fclose(f_handle);
END;
/

And now you can read the attention.log via SQL...

SELECT * FROM table(read_attention_log);