1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Showing Tabular grid lines in mail

Discussion in 'SQL PL/SQL' started by sneha.bharti, Dec 15, 2014.

  1. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    Hi All,

    I am trying to send mail through scheduled Oracle PL/SQl procedure using utl_smtp package connected through Oracle database.I am able to send datas through mail but now the requirement is, i have to show datas with tabular grid lines in mail.

    Kindly help
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    Thank you so much Mr. Sergey,

    but i am able to send datas through mail but just i want to format it with tabular grid lines without using html.
    here is my code for the refernce.
    please help

    CREATE OR REPLACE PROCEDURE TEST_MAIL IS

    MSG VARCHAR2(20000);
    MSG1 VARCHAR2(5000);
    MSG2 VARCHAR2(10000):=' ';
    MSG3 VARCHAR2(5000);
    MSG4 VARCHAR2(5000);
    to_mail VARCHAR2(250);
    cc_mail VARCHAR2(250);
    bcc_mail VARCHAR2(250);
    to_user_name VARCHAR2(250);
    V_TOTAL NUMBER:=0;
    V_TXN VARCHAR2(60);

    V_NUM NUMBER:=0;

    mail_rec om_mail_parameters%ROWTYPE;
    v_sender VARCHAR2(80);
    v_auth_user VARCHAR2(80);
    v_Subject VARCHAR2(80);
    v_Mail_Host VARCHAR2(30) ;
    v_Mail_Conn utl_smtp.Connection;
    crlf VARCHAR2(2) := chr(13)||chr(10);

    cursor c_mail is
    select * from om_mail_parameters;

    CURSOR c1(V_TXN VARCHAR2) IS
    SELECT tmm_txn_code , miue_user_id, miue_email , miue_email_id_flag FROM om_txn_mail_matrix,
    om_mail_intl_user_email WHERE
    miue_tmm_sys_id = tmm_sys_id AND tmm_intl_extl_flag = 'I'
    AND TMM_TXN_CODE=V_TXN;

    cursor C_SO IS
    SELECT SOH_CR_DT,SOH_TXN_CODE,SOH_NO,SOH_CUST_CODE,CUST_ANLY_CODE_01 ZONE,CUST_NAME,SOH_APPR_STATUS,
    SUM(NVL(SOI_QTY_BU,0)-NVL(SOI_DNI_QTY_BU,0)) PENDING_SO
    FROM SAMPLE_HEAD,SAMPLE_ITEM,OM_CUSTOMER
    WHERE SOI_SOH_SYS_ID=SOH_SYS_ID
    AND SOH_CUST_CODE=CUST_CODE
    AND NVL(SOH_CLO_STATUS,0)=0
    AND SOI_ITEM_CODE='test'
    AND NVL(SOI_QTY_BU,0)<>NVL(SOI_DNI_QTY_BU,0)
    GROUP BY SOH_CR_DT,SOH_TXN_CODE,SOH_NO,SOH_CUST_CODE,CUST_ANLY_CODE_01,CUST_NAME,SOH_APPR_STATUS
    ORDER BY SOH_CR_DT ASC ;



    BEGIN

    EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_SO_MAIL';

    V_TXN:='JTEST';

    IF C_MAIL%ISOPEN THEN
    CLOSE C_MAIL;
    END IF;

    open c_mail;
    fetch c_mail into mail_rec;
    close c_mail;

    v_mail_host:=mail_rec.mp_mail_server_ip_addr;


    v_sender := mail_rec.mp_from_email;
    v_auth_user := mail_rec.mp_auth_user_id;

    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_auth_user);
    /* end connection*/


    to_mail := NULL;
    cc_mail := NULL;
    bcc_mail := NULL;
    to_user_name := NULL;


    FOR c1_rec IN c1(V_TXN) LOOP
    utl_smtp.Rcpt(v_Mail_Conn, c1_Rec.miue_email);

    IF c1_rec.miue_email_id_flag = 'T' THEN
    IF to_mail IS NOT NULL THEN
    to_mail := to_mail ||';'||c1_Rec.miue_email;
    ELSE
    to_mail := c1_Rec.miue_email;
    END IF;


    ELSIF c1_rec.miue_email_id_flag = 'C' THEN
    IF cc_mail IS NOT NULL THEN
    cc_mail := cc_mail ||';'||c1_Rec.miue_email;
    ELSE cc_mail := c1_Rec.miue_email;
    END IF;


    ELSIF c1_rec.miue_email_id_flag = 'B' THEN
    IF bcc_mail IS NOT NULL THEN
    bcc_mail := bcc_mail ||';'||c1_Rec.miue_email;
    ELSE bcc_mail := c1_Rec.miue_email;
    END IF;

    END IF;

    END LOOP;

    MSG1 := 'Dear Sir ,'||CHR(10)||CHR(10)
    ||' REPORT : '
    ||CHR(10)||CHR(10)
    ||' test '||CHR(10)||CHR(10)
    ||RPAD('Cr Dt',11)||RPAD('Txn Code',9)||RPAD('Txn No.',12)||RPAD('Cust Code',10)||RPAD('Zone',10)||RPAD('Cust Name',25)||RPAD('Appr?',6)||Lpad('test',10)
    ||CHR(10)||CHR(10);


    FOR V_SO IN C_SO LOOP



    MSG2:=RPAD(V_SO.SOH_CR_DT,11)||RPAD(V_SO.SOH_TXN_CODE,9)||RPAD(V_SO.SOH_NO,12)||
    RPAD(V_SO.SOH_CUST_CODE,10)||RPAD(V_SO.ZONE,10)||RPAD(V_SO.CUST_NAME,25)||RPAD((CASE WHEN V_SO.SOH_APPR_STATUS=3 THEN 'Y' ELSE 'N' END),6)||LPAD(V_SO.PENDING_SO,10)
    ;


    INSERT INTO TEST_SO_MAIL VALUES(V_NUM,MSG2);
    COMMIT;
    V_NUM:=V_NUM+1;

    V_TOTAL:=V_TOTAL+V_sO.PENDING_SO ;

    END LOOP;


    DECLARE
    CURSOR C IS SELECT MSG,ID FROM TEST_SO_MAIL;
    BEGIN
    FOR V IN C LOOP
    MSG2:=MSG2||CHR(10)||CHR(10)||V.MSG;
    END LOOP;
    END;

    MSG3:=CHR(10)||CHR(10)||' TOTAL '||V_TOTAL;

    MSG4:=CHR(10)||CHR(10)
    ||'Kindly Plan accordingly.'
    ||CHR(10)||CHR(10)||CHR(10)
    ||'Regards,'||CHR(10)||CHR(10)||' ';

    MSG:=MSG1||MSG2||MSG3||MSG4;

    v_subject := 'test' ;

    IF MSG2 IS NOT NULL THEN
    utl_smtp.Data(v_Mail_Conn,
    'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
    'From: ' ||v_sender || crlf ||
    'Subject: '|| v_Subject || crlf ||
    'To: ' || to_mail || crlf ||
    'CC: ' ||cc_mail || crlf ||
    'BCC: ' ||bcc_mail|| crlf||



    'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
    'Content-Type: multipart/mixed;'|| crlf ||
    ' boundary="-----SECBOUND"'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/plain;'|| crlf ||
    'Content-Transfer_Encoding: 7bit'|| crlf ||
    crlf ||
    MSG ||crlf||crlf||
    '-------SECBOUND--'
    );
    END IF;


    to_mail := null;
    cc_mail := null;
    bcc_mail := null;

    UTL_SMTP.QUIT(v_Mail_Conn);

    EXCEPTION

    WHEN UTL_SMTP.INVALID_OPERATION THEN

    dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');

    WHEN UTL_SMTP.TRANSIENT_ERROR THEN

    dbms_output.put_line(' Temporary e-mail issue - try again');

    WHEN UTL_SMTP.PERMANENT_ERROR THEN
    dbms_output.put_line(sqlerrm);

    dbms_output.put_line(' Permanent Error Encountered.');


    END;
    /
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    It is necessary to provide also scripts of tables and examples of their filling....

    1) Note : it is better for commit not to use in a loop body

    TAB - this non-printable symbol.

    this character requires changeover by the character-screen
    or changeovers on n-gaps



    2)
    yours of tasks to send the report in a look :

    +------------------+
    | Col1 | Col2 | Col3 |
    +------------------+
    | Val1 | Val2 | 123 |
    +------------------+

    ?

    3) How many the report will contain lines?
    can be better to use attachment?
     
  5. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    thanks for your suggestions Mr. Sergey.

    Yes i want the format like that which you have mentioned.
    Column heading should be like cr dt,cust code etc. and datas to be filled should come from the fetched values of cursor c_So.
     
  6. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    sg1 := '<html>
    <head>
    <title>Pending qty Report as on today </title>
    </head>
    <body TEXT="#000000" BGCOLOR="#FFFFFF">
    <h4 style="font-weight:normal;">Dear Sir, </h4>
    <h4 style="font-weight:normal;">Pending item report as on today</h4>
    <table height="100" border="1" cellpadding="3" cellspacing="0" width="1200">
    <p style="font-size:15px">
    <tr width="1200">
    <th width="100" align="start">Created Date</th>
    <th width="90" align="start">Txn Code</th>
    <th width="100" align="start">No.</th>

    <th width="100" align="start">Qty</th>
    </tr>
    </p>
    </table>
    <table height="100" border="1" cellpadding="3" cellspacing="0" width="1200">
    <p style="font-size:15px" "font-weight:normal;">';
    FOR V_SO IN C_SO LOOP

    MSG2:=
    MSG2||
    '<tr width="1200"> <td width="100" align="start"> '|| V_SO.SOH_CR_Dt||' </td>
    <td width="90" align="start">'||V_SO.SOH_TXN_CODE ||' </td>
    <td width="100" align="start"> '|| V_SO.txn_NO ||' </td>
    <td width="100" align="end">'||TO_CHAR(V_SO.PENDING_qty,'999,999,999,999') ||' </td> </tr>';

    END LOOP;


    MSG3 :='
    <p style="font-weight:normal;" style="font-size:15px">
    <h4 style="font-weight:normal;">Regards,</h4>
    </p>';

    msg2:= msg1 || msg2||'</p>
    </table>'||msg3||'
    </body>
    </html> ';

    MSG:=MSG2;

    I used HTML finally and now datas are coming with tabular grid lines.
    Just replace this "MSG" with the above "MSG" in code mentioned.