?
Solved

Formatting the o/p of the procedure

Posted on 2011-04-25
7
Medium Priority
?
416 Views
Last Modified: 2013-12-07
Friends
I am writing a Stored procedure to send an email when something goes wrong. Im using utl_smtp to achieve this . However in the body of an email I would want the following output

The following Level2s are not mapped to a  Product .

0102 – Treasury Govt Bonds

0104 – Treasury Bills

The code for above is as below

How do I modify my cde to display the
V_Unmapped := rec.tlevel2;
v_Unmapped := rec.description;
in
0102 – Treasury Govt Bonds

0104 – Treasury Bills

?
Many thanks

UTL_SMTP.write_data(l_mail_conn, 'The following Level2s are not mapped to a Product'|| Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
FOR rec IN (SELECT m.tlevel2 , m.description
        FROM TBL_SFS_TLEVEL2_PRODUCT_MAP m
       WHERE m.status LIKE '%UN%')
LOOP

V_Unmapped := rec.tlevel2;
v_Unmapped := rec.description;
utl_smtp.write_data( l_mail_conn,V_Unmapped||chr(13), '' || Chr(13) );
END LOOP ;
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));

Open in new window

0
Comment
Question by:ronan_40060
  • 4
  • 3
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 35459973
FOR rec IN (SELECT m.tlevel2 , m.description
        FROM TBL_SFS_TLEVEL2_PRODUCT_MAP m
       WHERE m.status LIKE '%UN%')
LOOP

utl_smtp.write_data( l_mail_conn,rec.tlevel2 ||' -' || rec.description || chr(13) );
END LOOP;

v_unmapped isn't really needed in the loop
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35459983
note, if your query takes a long time, you might get SMTP timeouts
0
 
LVL 9

Author Comment

by:ronan_40060
ID: 35460726

Should utl_smtp.write_data( l_mail_conn,rec.tlevel2 ||' -' || rec.description || chr(13) );
be always outside the loop ?
How should I inform end -user about   SMTP timeouts ?
A way to handle this may be ?
Thanks
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35460748
the write_data must be inside the loop as shown, otherwise the rec-virtual record won't be defined

if you get a timeout or any other error, then utl_smtp will raise an exception.

if you query is fast, it likely won't be a problem.

if you want to prevent it,  write your data to a string variable prior to opening the collection.  then write the string with write_data after opening the connection

0
 
LVL 9

Author Comment

by:ronan_40060
ID: 35460774
if you want to prevent it,  write your data to a string variable prior to opening the connection.  then append the string.  ===>> Please provide an example in my case
Thanks a lot
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35474491
example?

move your cursor loop to before you open the connection

FOR rec IN (SELECT m.tlevel2 , m.description
        FROM TBL_SFS_TLEVEL2_PRODUCT_MAP m
       WHERE m.status LIKE '%UN%')
LOOP
    -- don't do this
    --    utl_smtp.write_data( l_mail_conn,rec.tlevel2 ||' -' || rec.description || chr(13) );

    -- do this instead
    v_some_string := v_some_string || rec.tlevel2 ||' -' || rec.description || chr(13);
END LOOP;


Then where you curretly have your loop

utl_smtp.write_data( l_mail_conn, v_some_string);

0
 
LVL 9

Author Comment

by:ronan_40060
ID: 35476366
Thanks , that helps :)
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question