PL/SQL将查询结果分配给CLOB [英] PL/SQL assigning query results to a CLOB

问题描述

我有一个问题，我正在使用oracle中查询的内容创建一个CLOB变量，以通过电子邮件发送给用户，问题是它以.csv的形式发送电子邮件，但没有任何内容.我找不到问题:

I have a problem, I am creating an CLOB variable with the contents of a query in oracle to email to users, the problem is that it does email as .csv but with no contents. I can not find the problems:

CREATE OR REPLACE PROCEDURE trackekr(cursor1 IN OUT SYS_REFCURSOR)
AS
    v_connection  UTL_SMTP.connection;
    v_clob        CLOB := EMPTY_CLOB();
    v_len         INTEGER;
    v_index       INTEGER;
 c_mime_boundary CONSTANT VARCHAR2(256) := 'the boundary can be almost anything';

    rec           NUMBER(10, 0) := 0;
    d_id          NUMBER(10, 0) := 0;
    customer      VARCHAR2(20);
    wife          VARCHAR2(20);
    date_rec      DATE;
    special_h     VARCHAR2(20);
    g_amount      NUMBER(10, 0) := 0;
    credit_amount NUMBER(10, 0) := 0;
    a_number      VARCHAR2(20);
    a__name       VARCHAR2(20);
BEGIN
    OPEN cursor1 FOR
        SELECT rec,
               d_id,
               customer,
               wife,
               date_rec,
               special h,
               g_amount
          FROM  (your query here);

    WHILE cursor1%NOTFOUND
    LOOP
        FETCH cursor1
              INTO rec,
                   d_id,
                   customer,
                   wife,
                   date_rec,
                   special_h,
                   g_amount,
                   credit_amount,
                   a_number,
                   a__name;

        v_clob :=
               v_clob
            || rec
            || ','
            || d_id
            || ','
            || customer
            || ','
            || wife
            || ','
            || date_rec
            || ','
            || special_h
            || ','
            || g_amount
            || ','
            || credit_amount
            || ','
            || a_number
            || ','
            || a__name
            || UTL_TCP.crlf;
    END LOOP;

    -- UTL

    v_connection := UTL_SMTP.open_connection(mailhost, 25);  
    SMTP server name or ip address
    UTL_SMTP.helo(v_connection, mail.exchange.mydomain.com);
    UTL_SMTP.mail(v_connection, 'mylogin.Exchange.mydomain.com');
    UTL_SMTP.rcpt(v_connection, 'mylogin.Exchange.mydomain.com'); 
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data(v_connection, 'From: ' || 'mylogin.Exchange.mydomain.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'To: ' || 'mylogin.Exchange.mydomain.com' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Subject: test subject' || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);

    UTL_SMTP.write_data(
        v_connection,
        'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
    );
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data(
        v_connection,
        'This is a multi-part message in MIME format.' || UTL_TCP.crlf
    );

    UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);

    -- Set up attachment header
    UTL_SMTP.write_data(
        v_connection,
        'Content-Disposition: attachment; filename="' || 'FIRSTFILE.csv' || '"' || UTL_TCP.crlf
    );
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

    -- Write attachment contents

    v_len := DBMS_LOB.getlength(v_clob);
    v_index := 1;

    WHILE v_index <= v_len
    LOOP
        UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
        v_index := v_index + 32000;
    END LOOP;

    -- End attachment
    UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

    UTL_SMTP.close_data(v_connection);
    UTL_SMTP.quit(v_connection);
END; 

正如我所说，它通过电子邮件发送.csv文件，但为空.

As I said, it emails a .csv file but empty.

推荐答案

请在您的代码中注意这一部分:

Note this part in your code:

WHILE cursor1%NOTFOUND

您的循环将永远不会对非空数据集执行.改用它:

Your loop will never be executed for non-empty dataset. Use this instead:

WHILE cursor1%FOUND

或者甚至更好地使用隐式游标:

Or even better use implicit cursor:

FOR cursor1 in
        (SELECT rec,
               d_id,
               customer,
               wife,
               date_rec,
               special_h,
               g_amount,
               credit_amount,
               a_number,
               a__name
          FROM  (your query here))
LOOP
v_clob :=
               v_clob
            || cursor1.rec
            || ','
            || cursor1.d_id
            || ','
            || cursor1.customer
            || ','
            || cursor1.wife
            || ','
            || cursor1.date_rec
            || ','
            || cursor1.special_h
            || ','
            || cursor1.g_amount
            || ','
            || cursor1.credit_amount
            || ','
            || cursor1.a_number
            || ','
            || cursor1.a__name
            || UTL_TCP.crlf;
END LOOP;

这篇关于PL/SQL将查询结果分配给CLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！