-- Create type
create type alert_row_type as object
(
line number(10),
text varchar2(2000)
);
-- Create type table
create type alert_type is table of alert_row_type;
-- Create directory
create or replace directory ALERT_DIR
as '/oracle/admin/bdump/';
-- Create function
create or replace function sf_get_alert return alert_type is
alert_tab alert_type := alert_type();
l_file utl_file.file_type;
l_line number(10) := 1;
l_text varchar2(2000);
begin
l_file := utl_file.fopen('ALERT_DIR', 'ALRT.LOG', 'r');
begin
loop
utl_file.get_line(l_file, l_text);
alert_tab.extend;
alert_tab(alert_tab.last) := alert_row_type(l_line, l_text);
l_line := l_line + 1;
end loop;
exception
when no_data_found then
null;
end;
utl_file.fclose(l_file);
return alert_tab;
end;
-- Create or replace view
create or replace view diy$alert as
select "LINE","TEXT"
from table(cast(sf_get_alert() as alert_type));
-- Select alert
select * from diy$alert;
출처
https://oracle-base.com/articles/8i/diy-dynamic-views
'Oracle(DB관련)' 카테고리의 다른 글
oracle java source (0) | 2020.04.02 |
---|---|
oracle 비율로 배분 후 나머지 마지막에 합산 (0) | 2019.12.02 |
oracle string util package (0) | 2018.12.28 |
oracle audit (0) | 2018.11.22 |
user_source, sys.all_objects (0) | 2018.11.05 |