plsql – Gokhan Atil’s Blog

How to Read Web Pages Using utl_http.request_pieces

UTL_HTTP package provides easy to use methods to fetch data from web using http protocol. Today, one of my co-workers complained that “utl_http.request” method returns only a small part of the web page instead of full html. She says she already uses it to fetch from other web pages without any problem. The utl_http.request method returns the requested page as a single string. When I check the documents, I see that it intentionally returns only first 2000 characters, and we should use “utl_http.request_pieces” method if we need to fetch more than 2000 characters. The UTL_HTTP.REQUEST_PIECES returns “utl_http.html_pieces” which is defined as a PLSQL table of 2000 character strings:

TYPE html_pieces IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

1	TYPE html_pieces IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

So I created a small function to demonstrate how we can fetch a web page using utl_http.request_pieces:

CREATE OR REPLACE FUNCTION readfromweb (url VARCHAR2)
   RETURN CLOB
IS
   pcs    UTL_HTTP.html_pieces;
   retv   CLOB;
BEGIN
   pcs := UTL_HTTP.request_pieces (url, 50);
   FOR i IN 1 .. pcs.COUNT
   LOOP
      retv := retv || pcs (i);
   END LOOP;
   RETURN retv;
END;

CREATE OR REPLACE FUNCTION readfromweb (url VARCHAR2)

RETURN CLOB

pcs UTL_HTTP.html_pieces;

retv CLOB;

BEGIN

pcs := UTL_HTTP.request_pieces (url, 50);

FOR i IN 1 .. pcs.COUNT

LOOP

retv := retv || pcs (i);

END LOOP;

RETURN retv;

END;

And tested it:

SELECT readfromweb ('http://..../awebpage.aspx') FROM DUAL;

1	SELECT readfromweb ('http://..../awebpage.aspx') FROM DUAL;

June 10, 2013Gokhan Atil2 CommentsOracleplsql

Create AWR Reports and Send Them via Email (RAC Compatible)

In one of my old posts, I tried to explain how to create AWR and ADDM reports by PL/SQL and send them as email. One of my reader asked me how to run them in RAC environment. As I see, my script for ADDM is already compatible but AWR was not, so I added 1-2 lines to make it compatible, and wanted to share it:

DECLARE
   bid            NUMBER;
   eid            NUMBER;
   host_name      VARCHAR2(64);
   starttime      CHAR (5);
   endtime        CHAR (5);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := 'gokhan@myemailaddress.com';
   v_mail_host    VARCHAR2 (30) := 'oursmtpserver';
   v_mail_conn    UTL_SMTP.connection;
BEGIN
   starttime := '06:00';
   endtime := '10:00';

   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

   FOR rac IN (SELECT dbid, inst_id, db_unique_name
                 FROM gv$database)
   LOOP
   
      SELECT host_name
        INTO host_name
        FROM gv$instance where inst_id = rac.inst_id;

       v_from := rac.db_unique_name ||  '@' || host_name;

       v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
       UTL_SMTP.HELO (v_mail_conn, v_mail_host);
       UTL_SMTP.MAIL (v_mail_conn, v_from);
       UTL_SMTP.RCPT (v_mail_conn, v_recipient);
       UTL_SMTP.OPEN_DATA( v_mail_conn );
       UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
       UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
       UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: ' 
            || 'AWR Report of ' || v_from || ' ' 
            || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF  );
       UTL_SMTP.WRITE_DATA ( v_mail_conn, 
            'Content-Type: text/html; charset=utf8' 
            || UTL_TCP.CRLF || UTL_TCP.CRLF );     

       FOR c1_rec IN
          (SELECT output
             FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(rac.dbid,
               rac.inst_id, bid, eid, 8 )))
       LOOP
          UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
       END LOOP;
       
       UTL_SMTP.CLOSE_DATA (v_mail_conn);  
       UTL_SMTP.QUIT (v_mail_conn);
   END LOOP;
EXCEPTION
   WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR
   THEN
      RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM);
END;
/

DECLARE

bid NUMBER;

eid NUMBER;

host_name VARCHAR2(64);

starttime CHAR (5);

endtime CHAR (5);

v_from VARCHAR2 (80);

v_recipient VARCHAR2 (80) := 'gokhan@myemailaddress.com';

v_mail_host VARCHAR2 (30) := 'oursmtpserver';

v_mail_conn UTL_SMTP.connection;

BEGIN

starttime := '06:00';

endtime := '10:00';

SELECT MIN (snap_id), MAX (snap_id)

INTO bid, eid

FROM dba_hist_snapshot

WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime

AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime

AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)

AND TRUNC (end_interval_time) = TRUNC (SYSDATE);

FOR rac IN (SELECT dbid, inst_id, db_unique_name

FROM gv$database)

LOOP

SELECT host_name

INTO host_name

FROM gv$instance where inst_id = rac.inst_id;

v_from := rac.db_unique_name || '@' || host_name;

v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);

UTL_SMTP.HELO (v_mail_conn, v_mail_host);

UTL_SMTP.MAIL (v_mail_conn, v_from);

UTL_SMTP.RCPT (v_mail_conn, v_recipient);

UTL_SMTP.OPEN_DATA( v_mail_conn );

UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );

UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );

UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '

|| 'AWR Report of ' || v_from || ' '

|| SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF );

UTL_SMTP.WRITE_DATA ( v_mail_conn,

'Content-Type: text/html; charset=utf8'

|| UTL_TCP.CRLF || UTL_TCP.CRLF );

FOR c1_rec IN

(SELECT output

FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(rac.dbid,

rac.inst_id, bid, eid, 8 )))

LOOP

UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF );

END LOOP;

UTL_SMTP.CLOSE_DATA (v_mail_conn);

UTL_SMTP.QUIT (v_mail_conn);

END LOOP;

EXCEPTION

WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR

THEN

RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM);

END;

It sends one email for each instance. So you get 3 emails if you have 3-node RAC. It doesn’t seem quite right to me, so I modified the code to send these reports as attachments in one mail:

September 13, 2012Gokhan Atil10 CommentsOracleawr, mail, plsql, utl_smtp

Create AWR and ADDM Reports and Send Them via Email

I’ve seen a question on OTN forum about how to create a job in Grid Control for generating AWR/ADDM reports and send these reports via email. As I know OEM Grid Control doesn’t have such a job template but we can write a PL/SQL script for this task and define it as a job, so we can automate it for all databases.

First, let’s check how we can generate AWR reports. To be able to get AWR reports in plain text, we can use:

Syntax (for Oracle 10.2)

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;

If we want the report in HTML, we can use:

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;

July 14, 2011Gokhan Atil14 CommentsOracleaddm, awr, mail, plsql, utl_smtp