ORACLE 数字或值错误

Question

我正在尝试生成有关磁盘读取次数最多的查询的报告。生成的报告将通过电子邮件发送。我写了一些代码如下。 当我包含前 15 个查询时，它工作正常，但如果我增加 hte 计数，我会收到“数字或值错误”。我想发生这种情况是因为我超出了某些数据类型限制 但无法识别。有人看到问题了吗？我怎样才能发送巨大的报告而不出现这样的错误？

我有一个 F_GENERATE_REPORT 函数和 P_SEND_REPORT_AS_EMAIL 过程。 P_SEND_REPORT_AS_EMAIL 过程使用 F_GENERATE_REPORT 作为正文 像 UTL_MAIL.SEND(message => F_GENERATE_REPORT(5)) 这样的电子邮件，其中 5 用于 tOP 5。

P_SEND_REPORT_AS_EMAIL 过程中的 UTL_MAIL.SEND() 行发生错误

谢谢大家。

cl generated_html_markup 是一个 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;

发送电子邮件程序

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

UTL_MAIL.SEND 的签名是：

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);

如您所见，您使用 CLOB 而不是 VARCHAR2 参数 message 来调用此过程。高达 32k 字节时，隐式转换开始，一切正常。我怀疑您遇到的错误是当您尝试使用不可转换为 VARCHAR2（> 32k 字节）的 CLOB 提供过程时。

在 Oracle 中发送包含 CLOB 消息的电子邮件的最简单方法是通过 APEX_EMAIL（默认安装在最新版本的数据库上，如果未安装，您可以从 Oracle 下载 APEX）。在旧版本上，您需要一些解决方法。例如，Tom Kyte 描述了如何 在AskTom上使用java在Oracle 8i中发送大电子邮件。或者，您也可以编写自己的 PLSQL 程序或在网上进行一些研究，您会发现可以使用 UTL_TCP 与邮件服务器通信（并以您选择的格式发送数据）。

Answer 2

PL/SQL 中 Varchar2 的最大大小为 32K。由于 cl generated_html_markup 使用的是 clob，所以你没问题，但是当你调用 UTL_MAIL.SEND 时，它会尝试将其转换为 Varchar2，但不能。您必须切换到较低级别的电子邮件工具，例如 UTL_SMTP，如下所示 此处。

更多地查看该链接，我不确定它是最好的示例，但我还没有找到接受 clob 作为使用 UTL_SMTP 发送的消息的过程的好示例。概念相似，但您的标题应该看起来更像这样：

   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 有一个很好的语法参考 在这里。