Re: Parsing Alert.log via PL/SQL

Thanks for your example. I'll need to experiment with this in order to get the results via ODBC/Cold Fusion. I don't think that DBMS_OUTPUT will display the results outside of SQL Plus. Please correct me if I'm wrong.

Thanks again for your assistance.

In article <8h161k$pnf$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> 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/ora8i-
> dev/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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 30 2000 - 00:00:00 CDT