Re: External Tables - Help Please
- From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Tue, 22 Sep 2009 14:22:15 +0100
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.
declare
db_name varchar2(8) := sys_context('USERENV','DB_NAME');
dir_name varchar2(2000);
begin
select value into dir_name
from v$parameter
where name = 'background_dump_dest';
execute immediate 'create or replace directory bdump as
'''||dir_name||'''';
begin
execute immediate 'drop table ext_alert_log';
exception when others then null;
end;
execute immediate 'create table ext_alert_log (message varchar2(4000))
organization external
(
type oracle_loader
default directory bdump
access parameters
(
records delimited by newline
nobadfile
nologfile
fields terminated by ''~''
(message)
)
location (''alert_'||db_name||'.log'')
)
reject limit unlimited';
end;
/
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;
begin
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;
end;
/
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
http://www.statistics.gov.uk
*********************************************************************************
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
