Re: Parsing Alert.log via PL/SQL

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/30
Message-ID: <8h161k$pnf$1@nnrp1.deja.com>#1/1

In article <8h11co$lsp$1_at_nnrp1.deja.com>, dbadaveo_at_my-deja.com wrote:
> I'm attempting to write a PL/SQL function that will parse a specified
> number of lines from the alert.log file into a PL/SQL table. Most of
> our Oracle databases (v. 8.0.5 & 8.0.6) are running on various flavors
> of Unix. My goal is to call this funtion from a web based app (Cold
> Fusion) and view the most recent alert.log entries. I'd like to stay
> away from creating an actual table to store the alert.log info, thus
> the request for a PL/SQL table.
>
> My initial thoughts were to use the UTL_FILE package, but I'm a bit
> lost on the UTL_FILE, cursor, PL/SQL table relationship. Could anyone
> provide a some insight into the best way to accomplish this request?
>
> Many thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

a cursor is what we use to scroll through a table.

utl_file is an API to read/write text files.

A plsql table is a sparse array -- not to be confused or compared to a database table....

Don't see a cursor fitting in here but lets say you wanted the last 100 lines of the alert log. you could write a function:

ops$tkyte_at_dev8i> set echo on
ops$tkyte_at_dev8i> set serveroutput on
ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> create or replace
  2  procedure  get_alert_lines( p_data           out
dbms_sql.Varchar2_Table,
  3                              p_lines_in_alert out number,
  4                              p_bytes_in_alert out number,
  5                              p_maxlines       in number default

100 )
6 is

  7      l_input         utl_file.file_type;
  8      l_buffer        dbms_sql.varchar2_table;
  9      l_currline        number default 0;
 10      l_lines         number default 0;
 11      l_bytes         number default 0;
 12  begin
 13      l_input := utl_file.fopen
( '/export/home/oracle8i/admin/ora8idev/bdump/',
 14                                 'alert_ora8idev.log',
 15                                 'r' );
 16      begin
 17          loop
 18              utl_file.get_line( l_input, l_buffer( l_currline ) );
 19              l_lines := l_lines+1;
 20              l_bytes := l_bytes+nvl(length(l_buffer(l_currline)),0);
 21              l_currline := mod( l_currline+1, p_maxlines );
 22          end loop;
 23      exception
 24          when no_data_found then
 25              utl_file.fclose( l_input );
 26      end;
 27
 28      begin
 29          for i in 1 .. p_maxlines loop
 30              p_data(i) := l_buffer( mod( l_currline+i-1,
p_maxlines ) );
 31          end loop;
 32      exception
 33          when no_data_found then
 34              NULL;
 35      end;
 36      p_lines_in_alert := l_lines;
 37      p_bytes_in_alert := l_bytes;

38 end get_alert_lines;
39 /

Procedure created.

Elapsed: 00:00:00.26

ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> set serveroutput on
ops$tkyte_at_dev8i> declare
  2      l_data   dbms_sql.varchar2_table;
  3      l_lines     number default 0;
  4      l_bytes     number default 0;
  5  begin
  6      get_alert_lines( l_data, l_lines, l_bytes, 25 );
  7
  8      dbms_output.put_line( 'Lines ' || l_lines );
  9      dbms_output.put_line( 'Bytes ' || l_bytes );
 10      for i in 1 .. l_data.count loop
 11          dbms_output.put_line( i || ' ) ' || l_data(i) );
 12      end loop;

13 end;
14 /
Lines 2379
Bytes 104270

1 ) Recovery of Online Redo Log: Thread 1 Group 2 Seq 2481 Reading mem 0
2 )   Mem# 0 errs 0: /log1/oradata/ora8i-dev/redo02.log
3 )   Mem# 1 errs 0: /log2/oradata/ora8i-dev/redo02.log
4 ) Recovery of Online Redo Log: Thread 1 Group 3 Seq 2482 Reading mem 0
5 )   Mem# 0 errs 0: /log1/oradata/ora8i-dev/redo03.log
6 )   Mem# 1 errs 0: /log2/oradata/ora8i-dev/redo03.log
7 ) Tue May 30 14:50:27 2000
8 ) Thread recovery: finish rolling forward thread 1
9 ) Thread recovery: 1208 blocks read, 1192 blocks written
10 ) Crash recovery completed successfully
11 ) Picked broadcast on commit scheme to generate SCNs
12 ) Tue May 30 14:50:29 2000
13 ) Thread 1 advanced to log sequence 2483

14 ) Thread 1 opened at log sequence 2483 15 ) Current log# 1 seq# 2483 mem# 0: /log1/oradata/ora8idev /redo01.log
16 ) Current log# 1 seq# 2483 mem# 1: /log2/oradata/ora8i- dev/redo01.log

17 ) Successful open of redo thread 1.
18 ) Tue May 30 14:50:30 2000
19 ) SMON: enabling cache recovery
20 ) SMON: enabling tx recovery
21 ) Tue May 30 14:50:40 2000
22 ) Completed: alter database open
23 ) Tue May 30 14:50:41 2000
24 ) Restarting dead background process QMN0
25 ) QMN0 started with pid=11

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.48
ops$tkyte_at_dev8i>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue May 30 2000 - 00:00:00 CDT