Re: re-submit sql help

Hi Dear listers,

After another hard working day. I finally got it right. Thanks to jacques who help me parse the string. Otherwise, It definitely take me some time to figure it out.
Thanks,

Joan

Joan wrote:

> 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;
> begin
> 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;
> /
> 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>
> .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
> .gif
>
> Thanks in advance,
> Joan
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Joan
> INET: tylin_at_mediaone.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Thu Jan 11 2001 - 17:14:09 CST