| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> re-submit sql help
Dear listers,
I post my question again. Here is the entries table. If I select
ent_url, ent_http_headers from
entries; I will get the following rows back. (Millions rows) First line
is ent_url, the others are ent_http_headers
http://www.cartoonnetwork.com:80/legal/img/boomerang.click.gif
Server: Netscape-Enterprise/2.01
Expires: Tue, 09 Jan 2001 19:47:10 GMT
Accept-ranges: bytes
Last-modified: Fri, 15 Dec 2000 22:50:40 GMT
Content-type: image/gif
http://www.coed.com:80/tgp/posing/013.jpg
Server: Apache/1.3.12 (Unix)
Last-Modified: Sat, 12 Aug 2000 12:26:03 GMT
ETag: "5330-478a-3995425b"
Accept-Ranges: bytes
Content-Type: image/jpeg
I just get partial done with this pl/sql.
!date
set serveroutput on
declare
p_directory varchar2(35) := '/disk1/oracle/joan';
p_file_name varchar2(30) := 'entry_scan.lis';
lv_file_id_num UTL_FILE.FILE_TYPE;
lv_start_time pls_integer;
lv_end_time pls_integer;
-- Local Variables --
lv_row_count pls_integer := 0;
lv_display_count pls_integer := 0;
lv_url varchar2(512) := '';
lv_content_length pls_integer := 0;
lv_content_type varchar2(25) := '';
lv_content_count pls_integer := 0;
lv_url_ext varchar2(10) := '';
lv_tag varchar2(10) := '';
ext varchar2(10) :='';
-- Summary Variables --
sv_content_t_app pls_integer := 0;
sv_content_t_aud pls_integer := 0;
sv_content_t_img pls_integer := 0;
sv_content_t_txt_p pls_integer := 0;
sv_content_t_txt_c pls_integer := 0;
sv_content_t_txt_h pls_integer := 0;
sv_content_t_txt pls_integer := 0;
sv_content_t_vid pls_integer := 0;
sv_content_t_other pls_integer := 0;
sv_content_t_missing pls_integer := 0;
cursor cur_entry is
select
ent_url,
lower(substr(ent_http_headers,
instr(lower(ent_http_headers),'content-type:',
-1,1),25)),
substr(lower(substr(ent_url,instr(ent_url,'.',-1,1),10)),10
instr(lower(ent_http_headers),'content-type:'),
lower(substr(ent_url,instr(ent_url,'.',-1,1),4))
from entries
where rownum < 200;
lv_start_time := DBMS_UTILITY.GET_TIME;
lv_file_id_num := UTL_FILE.FOPEN(p_directory, p_file_name, 'W');
open cur_entry;
loop
fetch cur_entry into
lv_url,
lv_content_type, lv_url_ext,
lv_content_count, ext;
exit when cur_entry%notfound;
lv_row_count := lv_row_count + 1;
-- Examine content type (ENT_HTTP_HEADERS)
if lv_content_count > 0 then
if lv_content_type like 'content_type: image%' then
sv_content_t_img := sv_content_t_img + 1;
elsif lv_content_type like 'content_type: text/plain%' then
sv_content_t_txt_p := sv_content_t_txt_p + 1;
elsif lv_content_type like 'content_type: text/html%' then
sv_content_t_txt_h := sv_content_t_txt_h + 1;
elsif lv_content_type like 'content_type: text/css%' then
sv_content_t_txt_c := sv_content_t_txt_c + 1;
elsif lv_content_type like 'content_type: text%' then
sv_content_t_txt := sv_content_t_txt + 1;
elsif lv_content_type like 'content_type: app%' then
sv_content_t_app := sv_content_t_app + 1;
elsif lv_content_type like 'content_type: audio%' then
sv_content_t_aud := sv_content_t_aud + 1;
elsif lv_content_type like 'content_type: video%' then
sv_content_t_vid := sv_content_t_vid + 1;
else
sv_content_t_other := sv_content_t_other + 1;
end if;
else
sv_content_t_missing := sv_content_t_missing + 1;
end if;
-- Debug output here...
lv_display_count := lv_display_count + 1;
if lv_display_count = 1000 then
UTL_FILE.PUT_LINE(lv_file_id_num,'1000 rows processed');
lv_display_count := 0;
end if;
'Entry Scan statistics based upon '||
lv_row_count||' rows');
UTL_FILE.PUT_LINE(lv_file_id_num,' ');
UTL_FILE.PUT_LINE(lv_file_id_num,' ');
UTL_FILE.PUT_LINE(lv_file_id_num,'Content type Statistics:');
UTL_FILE.PUT_LINE(lv_file_id_num,
' Image '||sv_content_t_img||' '||
trunc((sv_content_t_img/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,
' Text/HTML '||sv_content_t_txt_h||' '||
trunc((sv_content_t_txt_h/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,
' Text/plain '||sv_content_t_txt_p||' '||
trunc((sv_content_t_txt_p/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,
' Text/CSS '||sv_content_t_txt_c||' '||
trunc((sv_content_t_txt_c/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,
' Text '||sv_content_t_txt||' '||
trunc((sv_content_t_txt/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,
' App '||sv_content_t_app||' '||
trunc((sv_content_t_app/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,
' Audio '||sv_content_t_aud||' '||
trunc((sv_content_t_aud/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,
' Video '||sv_content_t_vid||' '||
trunc((sv_content_t_vid/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,
' Missing '||sv_content_t_missing||' '||
trunc((sv_content_t_missing/lv_row_count) * 100,2))
UTL_FILE.PUT_LINE(lv_file_id_num,
' Other '||sv_content_t_other||' '||
trunc((sv_content_t_other/lv_row_count) * 100,2));
UTL_FILE.PUT_LINE(lv_file_id_num,ext);
--
-- Wrap up stuff. Stop watch and close file
--
lv_end_time := DBMS_UTILITY.GET_TIME;
UTL_FILE.PUT_LINE(lv_file_id_num,' ');
UTL_FILE.PUT_LINE(lv_file_id_num,
'Elapsed time '||(lv_end_time - lv_start_time)/100);
UTL_FILE.FCLOSE_ALL;
exception
when UTL_FILE.INTERNAL_ERROR then
dbms_output.put_line('Internal error');
when UTL_FILE.INVALID_FILEHANDLE then
dbms_output.put_line('Invalid file handle error');
when UTL_FILE.INVALID_MODE then
dbms_output.put_line('Invalid mode error');
when UTL_FILE.INVALID_OPERATION then
dbms_output.put_line('Invalid operation error');
when UTL_FILE.INVALID_PATH then
dbms_output.put_line('Invalid path error');
when UTL_FILE.READ_ERROR then
dbms_output.put_line('Read error');
when UTL_FILE.WRITE_ERROR then
dbms_output.put_line('Write error');
when others then
dbms_output.put_line('ent_url:= '||lv_url);
dbms_output.put_line('sqlerrm:= '||sqlerrm);
dbms_output.put_line(' ');
dbms_output.put_line('lv_tag:= '||lv_tag);
end;
/
The output looks right with the first level.
oracle_at_c1.lon:/disk1/oracle/joan$ cat entry_scan.lis
Entry Scan statistics based upon 199 rows
Content type Statistics:
Image 178 89.44
Text/HTML 15 7.53
Text/plain 0 0
Text/CSS 1 .5
Text 0 0
App 4 2.01
Audio 1 .5
Video 0 0
Missing 0 0
Other 0 0
.gif
Elapsed time .23
I need an output with one level down and the #of rows and the % of its
parent level.
Entry Scan statistics based upon 199 rows
Content type Statistics:
Image 178 89.44
#I need one level down
.gif < #of out 178> <%of 178> through all the
level down
.html <#of out 178> <%of 178>
.jpg
.jpeg
Text/HTML 15 7.53
.txt <# of out15> <%of 15>
Received on Wed Jan 10 2001 - 19:53:56 CST
 |
 |