| 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: sql help
Hi Jacques,
Thank you so much. I worked whole day for this ps/sql. The output looks
good so far. (first level)But I need one level down. I don't care the
format, as long as I can get the statistic right. I tried couple times,
the number is not right. Tomorrow I will use your solution to parse the
string get the extension correct first. Thank you again. If someone can
help me. please cc to joan.hsieh_at_mirror-image.com.
Regard,
Joan
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>
.html
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
Elapsed time .23
!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_uncachable_f pls_integer := 0;
-- sv_uncachable_t pls_integer := 0;
sv_created_sun pls_integer := 0;
sv_created_mon pls_integer := 0;
sv_content_high pls_integer := 0;
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;
end loop;
close cur_entry;
UTL_FILE.PUT_LINE(lv_file_id_num,
'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;
/
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
Jacques Kilchoer wrote:
>
>
> > -----Original Message-----
> > From: Joan [mailto:tylin_at_mediaone.net]
> > Sent: mardi, 9. janvier 2001 16:15
> > To: Multiple recipients of list ORACLE-L
> > Subject: sql help
> >
> > Dear listers,
> > I need some sql help
>
> I'm not 100% sure I understood exactly what output you are expecting.
> In any event, here's an example of some string extraction.
>
> SQL> create table t (v1 varchar2 (1000), v2 varchar2 (1000)) ;
>
> Table created.
>
> SQL> insert into t (v1, v2) values
> 2
> ('http://www.cartoonnetwork.com:80/legal/img/boomerang.click.gif',
> 3 'Server: Netscape-Enterprise/2.01' || chr (10) ||
> 4 'Expires: Tue, 09 Jan 2001 19:47:10 GMT' || chr (10) ||
> 5 'Accept-ranges: bytes' || chr (10) ||
> 6 'Last-modified: Fri, 15 Dec 2000 22:50:40 GMT' || chr (10) ||
> 7 'Content-type: image/gif') ;
>
> 1 row created.
>
> SQL> insert into t (v1, v2) values
> 2 ('http://www.coedsexxx.com:80/tgp/posing/body/013.jpg',
> 3 'Server: Apache/1.3.12 (Unix)' || chr (10) ||
> 4 'Last-Modified: Sat, 12 Aug 2000 12:26:03 GMT' || chr (10) ||
> 5 'ETag: "5330-478a-3995425b"' || chr (10) ||
> 6 'Accept-Ranges: bytes' || chr (10) ||
> 7 'Content-Type: image/jpeg') ;
>
> 1 row created.
>
> SQL> set pagesize 100
> SQL> select v1, v2 from t ;
>
> V1
>
> ----------------------------------------------------------------------------
>
> V2
>
> ----------------------------------------------------------------------------
>
> 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.coedsexxx.com:80/tgp/posing/body/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
>
> SQL> column content_type format a20
> SQL> column url_extension format a20
> SQL> define search_string = "CONTENT-TYPE:"
> SQL> define search_string_len = 13
> SQL> define delim1 = "/"
> SQL> define delim1_len = 1
> SQL> define delim2 = "."
> SQL> define delim2_len = 1
> SQL> set verify off
> SQL> select
> 2 ltrim
> 3 (substr
> 4 (substr
> 5 (v2,
> 6 instr
> 7 (upper (v2),
> 8 '&search_string',
> 9 -1
> 10 ) + &search_string_len
> 11 ),
> 12 1,
> 13 instr
> 14 (substr
> 15 (v2,
> 16 instr
> 17 (upper (v2),
> 18 '&search_string',
> 19 -1
> 20 ) + &search_string_len
> 21 ),
> 22 '&delim1'
> 23 ) - &delim1_len
> 24 )
> 25 )
> 26 as content_type,
> 27 substr
> 28 (v1,
> 29 instr
> 30 (v1,
> 31 '&delim2',
> 32 -1
> 33 ) + &delim2_len
> 34 )
> 35 as url_extension
Received on Wed Jan 10 2001 - 19:11:00 CST
 |
 |
