Re: External Tables - Help Please

From: Ian Cary <>
Date: Tue, 22 Sep 2009 14:22:15 +0100
Message-ID: <>

I don't know whether this will be of any use but I overlay the alert log with an external table and then create a pipelined function to place the timestamp on each row.
The code is below for anyone that may be interested.

  db_name varchar2(8) := sys_context('USERENV','DB_NAME');   dir_name varchar2(2000);
  select value into dir_name
  from v$parameter
  where name = 'background_dump_dest';

  execute immediate 'create or replace directory bdump as '''||dir_name||'''';
    execute immediate 'drop table ext_alert_log';   exception when others then null;
  execute immediate 'create table ext_alert_log (message varchar2(4000))

                      organization external
                       type oracle_loader
                       default directory bdump
                       access parameters
                        records delimited by newline
                        fields terminated by ''~''
                       location (''alert_'||db_name||'.log'')
                      reject limit unlimited';


create or replace type alert_type as object (log_time date, log_message clob);
create or replace type alert_tab as table of alert_type; /

create or replace function alert_log_pipe return alert_tab pipelined as
v_date date := null;
v_mess clob;
  for i in (select * from ext_alert_log) loop

      if substr(i.message,1,4) in ('Mon ',

'Tue ',
'Wed ',
'Thu ',
'Fri ',
'Sat ',
'Sun ') then
if v_date is not null then pipe row(alert_type(v_date,v_mess)); end if; v_date := to_date(substr(i.message,5),'Mon dd hh24:mi:ss yyyy'); v_mess := null; else v_mess := v_mess||chr(10)||i.message; end if;

   end loop;
   if v_date is not null then

      pipe row(alert_type(v_date,v_mess));    end if;

create or replace view alert_log as select * from table(alert_log_pipe);

For the latest data on the economy and society consult National Statistics at

Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications

Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics

The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.

Received on Tue Sep 22 2009 - 08:22:15 CDT

Original text of this message