ORACLE Numeric or Value Error

Question

Hi everybody,

I am trying to generate a report about the queries with the most disk reads. The generated report will be sent as email. I wrote some code as below. When i include top 15 query it works fine but if i increase hte count i get 'numeric or value error'. I guess this happens because i exceed some datatypes limit but could not identify it. Anybodyy is seeing the problem ? And how can i send huge report without an error like this?

I have a F_GENERATE_REPORT function and P_SEND_REPORT_AS_EMAIL procedure. P_SEND_REPORT_AS_EMAIL procedure uses F_GENERATE_REPORT as the body of the email like UTL_MAIL.SEND(message => F_GENERATE_REPORT(5)) where 5 used for tOP 5.

Error occures on the line UTL_MAIL.SEND() in the procedure P_SEND_REPORT_AS_EMAIL

Thanks all.

clgenerated_html_markup is an CLOB.

FOR cur_for_query IN (SELECT * 
                        FROM (SELECT buffer_gets,rows_processed,executions,
                                     fetches,hash_value,sql_text, disk_reads,
                                     rank() over(ORDER BY disk_reads DESC) AS rank FROM v$sqlarea)
                       WHERE rank <= nquery_count)
LOOP
   --dbms_output.put_line(counter);
   --counter := counter + 1;
   clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEROWOPEN || CHR(10) ;
   clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEDATA(cur_for_query.rank,null,null,null,null,null,'class=tdData') || CHR(10) ;
   clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEDATA(cur_for_query.sql_text,null,null,null,null,null,'class=tdSQLText') || CHR(10) ;
   clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEDATA(TO_CHAR(NVL(cur_for_query.disk_reads,'')),'CENTER',null,null,null,null,'class=tdData') || CHR(10) ;
   clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEDATA(TO_CHAR(NVL(cur_for_query.buffer_gets,'')),'CENTER',null,null,null,null,'class=tdData') || CHR(10) ;
   clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEDATA(TO_CHAR(NVL(cur_for_query.executions,'')),'CENTER',null,null,null,null,'class=tdData') || CHR(10) ;
   clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEDATA(TO_CHAR(NVL(cur_for_query.fetches,'')),'CENTER',null,null,null,null,'class=tdData') || CHR(10) ;
   --clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEDATA('','CENTER',null,null,null,null,'class=tdData') || CHR(10) ;
   --clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEDATA('','CENTER',null,null,null,null,'class=tdData') || CHR(10) ;
   clgenerated_html_markup := clgenerated_html_markup || HTF.TABLEROWCLOSE || CHR(10) ;
END LOOP;

Send Email Procedure

PROCEDURE P_SEND_REPORT_AS_EMAIL
(
 vreceipent VARCHAR2,
 vsubject VARCHAR2,
 nquery_count NUMBER DEFAULT 5 
 )

 IS
 BEGIN

 -- INPUT VALIDATION
 IF vreceipent IS NULL THEN
             RAISE_APPLICATION_ERROR(value_can_not_be_null,'DBA_EXHAUSTIVE_QUERY_PKG::P_SEND_REPORT_AS_EMAIL::Receipent Email Address Can Not Be Null.');
 END IF;
 -- END OF INPUT VALIDATION


 UTL_MAIL.SEND(sender => '[email protected]',
               recipients => vreceipent,
               subject => NVL(vsubject,''),
               message => F_GENERATE_REPORT(nquery_count),
               mime_type => 'text/html; charset=us-ascii');

EXCEPTION 
WHEN OTHERS THEN
   -- TODO LOG ERROR HERE
 RAISE;

 END P_SEND_REPORT_AS_EMAIL;

Answer 1

Hi mehmet,

the signature for UTL_MAIL.SEND is:

UTL_MAIL.SEND (
   sender      IN    VARCHAR2 CHARACTER SET ANY_CS,
   recipients  IN    VARCHAR2 CHARACTER SET ANY_CS,
   cc          IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   bcc         IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   subject     IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
** message     IN    VARCHAR2 CHARACTER SET ANY_CS, **
   mime_type   IN    VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
   priority    IN    PLS_INTEGER DEFAULT NULL);

As you can see you call this procedure with the parameter message with a CLOB instead of a VARCHAR2. Up to 32k bytes the implicit conversion kicks in and everything is fine. I suspect the error you are getting is when you are trying to feed the procedure with a CLOB that is not convertible to a VARCHAR2 (>32k bytes).

The easiest way to send emails with CLOB messages in Oracle is through APEX_EMAIL (installed by default on the newest versions of the DB, if not installed you can download APEX from Oracle). On older versions you would need some workaround. For example, Tom Kyte describes how to use java to send large emails in Oracle 8i on AskTom. Alternatively, you could also write your own PLSQL procedure or do some research on the net, you will find it is possible to use UTL_TCP to talk to a mail server (and send data in the format of your choosing).

Answer 2

The maximum size of a Varchar2 in PL/SQL is 32K. Since clgenerated_html_markup is using a clob you are fine there, but then when you call UTL_MAIL.SEND it is trying to convert it to a Varchar2 and cannot. You'll have to switch to a lower level email tool such as UTL_SMTP as seen here.

Looking at the link more, I'm not sure it is the best example, but I haven't found a good example of a procedure accepting a clob as the message to send out using UTL_SMTP. The concept is similar, but your header should look something more like this:

   PROCEDURE SendSMTP(
      pTo       Varchar2    Default null,
      pSubject  Varchar2    Default null,
      pBody     Clob        Default empty_clob,
      pFrom     Varchar2    Default null,
      pCC       Varchar2    Default null,
      pBCC      Varchar2    Default null,
      pMimeType Varchar2    Default cDefaultMimeType,
      pSMTPHost Varchar2    Default cDefaultMailServer,
      pSMTPPort pls_integer Default cDefaultPort)

Psoug.org has a good reference for syntax here.