Home » SQL & PL/SQL » SQL & PL/SQL » Sending line feed/carriage return for email body
Sending line feed/carriage return for email body [message #251241] Thu, 12 July 2007 13:51 Go to next message
Mrrenzo0861
Messages: 5
Registered: July 2007
Location: IN
Junior Member
I'm having a problem trying to build the body for an email. I want to place several rows into the body of code but I can't get the line feed to work. I keep getting this error message.


When I put this code in I get nothing.
v_crlf VARCHAR2(2) := chr(13) || chr(10);


But when I use this code I get the error.
v_crlf VARCHAR2(2) := + chr(13) + chr(10);

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Re: Sending line feed/carriage return for email body [message #251243 is a reply to message #251241] Thu, 12 July 2007 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58906
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
+ is a numeric operator not a string one. || is for concatenation.

Regards
Michel
Re: Sending line feed/carriage return for email body [message #251252 is a reply to message #251241] Thu, 12 July 2007 14:14 Go to previous messageGo to next message
Mrrenzo0861
Messages: 5
Registered: July 2007
Location: IN
Junior Member
but when using the || ot doesn't perform the line feed/carriage return.
Re: Sending line feed/carriage return for email body [message #251262 is a reply to message #251252] Thu, 12 July 2007 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58906
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah?! If you say so.

Regards
Michel

Re: Sending line feed/carriage return for email body [message #251269 is a reply to message #251252] Thu, 12 July 2007 15:06 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
Mrrenzo0861 wrote on Thu, 12 July 2007 15:14
but when using the || ot doesn't perform the line feed/carriage return.


Show us. It works for me.
FOO SCOTT>declare v_crlf varchar(10);
  2  begin
  3  v_crlf:=chr(13) || chr(10);
  4  dbms_output.put_line('a' || v_crlf || 'b');
  5  end;
  6  /

PL/SQL procedure successfully completed.

FOO SCOTT>set serveroutput on size 10000
FOO SCOTT>/
a
b

PL/SQL procedure successfully completed.

[Updated on: Thu, 12 July 2007 15:06]

Report message to a moderator

Re: Sending line feed/carriage return for email body [message #251271 is a reply to message #251241] Thu, 12 July 2007 15:14 Go to previous messageGo to next message
Mrrenzo0861
Messages: 5
Registered: July 2007
Location: IN
Junior Member
Here's the email:
Hello, GLEN BALDESCHWILER


INBELL, IN_ST_EFT, 15-000-0000, MHA, Invalid entity code INBELL for this payment file., 09-JUL-07 -- INBELL, IN_ST_EFT, 15-000-0000, MHA, Invalid user id MHA for this payment file., 09-JUL-07 -- INBELL, IN_ST_EFT, 15-000-0000, MHA, Tax Amount out of balance., 09-JUL-07 --



This message is from the Test System

<end of message>
Re: Sending line feed/carriage return for email body [message #251273 is a reply to message #251241] Thu, 12 July 2007 15:17 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
And where is the SQL that generated that email ?
Re: Sending line feed/carriage return for email body [message #251276 is a reply to message #251273] Thu, 12 July 2007 15:21 Go to previous messageGo to next message
Mrrenzo0861
Messages: 5
Registered: July 2007
Location: IN
Junior Member

This code makes the call:
SUTSCOI_MAIL('lc2698@att.com', 'BATCH AGENT PROCESS - BATCH AGENT UPDATE ERRORS', 'Hello, ' || v_mgr_first_name || ' ' || v_mgr_last_name, 'lc2698@att.com' || ';', v_manager_email, 'lc2698@att.com', v_error_line);



And this code sends the email:
00001 PROCEDURE SUTSCOI_MAIL (
00002 instrFrom IN VARCHAR2,
00003 instrSubject IN VARCHAR2,
00004 instrSalutation IN VARCHAR2,
00005 instrTo IN VARCHAR2,
00006 instrCc IN VARCHAR2,
00007 instrBcc IN VARCHAR2,
00008 instrMessage IN VARCHAR2)
00009 IS
00010
00011 ----------------------------------------------------------------------------------
00012 -- define variables
00013 ----------------------------------------------------------------------------------
00014 strMailHost VARCHAR2(30) := 'attrh1.attrh.att.com';
00015 conMailConnection UTL_SMTP.CONNECTION;
00016
00017 strMessage VARCHAR2(10000);
00018 strRecipients VARCHAR(4000);
00019
00020 strDbName VARCHAR2(10);
00021 strDbText VARCHAR2(50);
00022
00023 strDev VARCHAR2(50);
00024
00025 i NUMBER;
00026 j NUMBER;
00027 k NUMBER;
00028
00029 /******************************************************************************
00030 NAME: SUTSCOI_MAIL
00031 PURPOSE: To send email to SUTS-COI user when error occurs.
00032
00033 REVISIONS:
00034 Ver Date Author Description
00035 --------- ---------- --------------- ------------------------------------
00036 1.0 5/10/2007 1. Created this procedure.
00037
00038 NOTES:
00039
00040 Automatically available Auto Replace Keywords:
00041 Object Name: SUTSCOI_MAIL
00042 Sysdate: 5/10/2007
00043 Date and Time: 5/10/2007, 10:39:19 AM, and 5/10/2007 10:39:19 AM
00044 Username: (set in TOAD Options, Procedure Editor)
00045 Table Name: (set in the "New PL/SQL Object" dialog)
00046
00047 ******************************************************************************/
00048 ----------------------------------------------------------------------------------
00049 -- this is the shape of the connection record type
00050 --
00051 -- TYPE connection IS RECORD (
00052 -- host VARCHAR2(255), -- remote host name
00053 -- port PLS_INTEGER, -- remote port number
00054 -- private_tcp_con utl_tcp.commection, -- private, for implementation use
00055 -- private_state PLS_INTEGER -- private, for implementation use
00056 ----------------------------------------------------------------------------------
00057
00058 BEGIN
00059 -- determine the system.
00060
00061 SELECT SYS_CONTEXT('USERENV', 'DB_NAME'),
00062 DECODE(SYS_CONTEXT('USERENV', 'DB_NAME'),
00063 'T1', 'Test System',
00064 'P3', 'Production System',
00065 'Unknown System')
00066 INTO strDbName, strDbText
00067 FROM DUAL;
00068
00069 ----------------------------------------------------------------------
00070 -- assemble the messaage string.
00071 ----------------------------------------------------------------------
00072 strMessage := 'from: ' || instrFrom || chr(13) || chr(10);
00073
00074 IF UPPER(TRIM(strDbName)) = 'T1' THEN
00075 strMessage := strMessage || 'subject: ** TEST ** ' || instrSubject || chr(13) || chr(10);
00076 ELSE
00077 strMessage := strMessage || 'subject: ' || instrSubject || chr(13) || chr(10);
00078 END IF;
00079
00080 strMessage := strMessage || 'bcc: ' || instrBcc || chr(13) || chr(10);
00081 strMessage := strMessage || 'cc: ' || instrCc || chr(13) || chr(10);
00082 strMessage := strMessage || 'to: ' || instrTo || chr(13) || chr(10);
00083 strMessage := strMessage || instrSalutation || chr(10) || chr(10) || instrMessage|| chr(13) || chr(10);
00084
00085 strMessage := strMessage || chr(13) || chr(10) || chr(13) || chr(10);
00086 strMessage := strMessage || chr(13) || chr(10);
00087 strMessage := strMessage || 'This message is from the ' || strDbText || chr(13) || chr(10);
00088 strMessage := strMessage || chr(13) || chr(10);
00089 strMessage := strMessage || '<end of message>';
00090
00091 ----------------------------------------------------------------------------------
00092 -- open a connection to an smtp server.
00093 ----------------------------------------------------------------------------------
00094 conMailConnection := UTL_SMTP.OPEN_CONNECTION(strMailHost, 25);
00095
00096 ----------------------------------------------------------------------------------
00097 -- intitial handshake with smtp server.
00098 ----------------------------------------------------------------------------------
00099 UTL_SMTP.HELO(conMailConnection, strMailHost);
00100
00101 ----------------------------------------------------------------------------------
00102 -- initiate a mail transaction with the smtp server, destination is mailbox.
00103 ----------------------------------------------------------------------------------
00104 UTL_SMTP.MAIL(conMailConnection, instrFrom);
00105
00106 ----------------------------------------------------------------------------------
00107 -- specify the recipients.
00108 ----------------------------------------------------------------------------------
00109 strRecipients := instrTo || instrCc || instrBcc;
00110
00111 k := 1;
00112 i := 1;
00113 j := INSTR(strRecipients,';',1,k);
00114
00115 WHILE j <> 0 LOOP
00116 strDev := SUBSTR(strRecipients,i,j-i );
00117 UTL_SMTP.RCPT(conMailConnection, strDev);
00118 i := j + 1;
00119 k := k + 1;
00120 j := INSTR(strRecipients,';',1,k);
00121 END LOOP;
00122
00123 ----------------------------------------------------------------------------------
00124 -- specify the body of the message.
00125 ----------------------------------------------------------------------------------
00126 UTL_SMTP.DATA(conMailConnection, strMessage);
00127
00128 ----------------------------------------------------------------------------------
00129 -- terminate the smtp session and disconnect from the server.
00130 ----------------------------------------------------------------------------------
00131 UTL_SMTP.QUIT(conMailConnection);
00132
00133
00134 EXCEPTION
00135 WHEN NO_DATA_FOUND THEN
00136 NULL;
00137 WHEN OTHERS THEN
00138 NULL;
00139
00140 END SUTSCOI_MAIL;
Re: Sending line feed/carriage return for email body [message #251278 is a reply to message #251241] Thu, 12 July 2007 15:26 Go to previous messageGo to next message
BlackSwan
Messages: 22706
Registered: January 2009
Senior Member
>This message is from the Test System

><end of message>

Would you care to explain how or why these are on separate LINES?
Taken from your previous made at Thu, 12 July 2007 13:14

PEBKAC
Re: Sending line feed/carriage return for email body [message #251279 is a reply to message #251276] Thu, 12 July 2007 15:27 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
http://www.orafaq.com/forum/t/85036/66800/
Re: Sending line feed/carriage return for email body [message #251280 is a reply to message #251279] Thu, 12 July 2007 15:29 Go to previous messageGo to next message
Mrrenzo0861
Messages: 5
Registered: July 2007
Location: IN
Junior Member
If you are talking about the code to send email I just copied as is so I don't know why it's on seperate lines but if you are talking the code to call the other code that's the way it is.
Re: Sending line feed/carriage return for email body [message #251282 is a reply to message #251241] Thu, 12 July 2007 15:37 Go to previous message
BlackSwan
Messages: 22706
Registered: January 2009
Senior Member
Code lines 87-89 are the ones responsible for the lines I cut & pasted from your post which CLEARLY demonstrate that || chr(13) || chr(10) produce desired results.

Previous Topic: ORA-00937:not a single-group group function|ORA-06512:at"schema.procedure name)?
Next Topic: Syntax error
Goto Forum:
  


Current Time: Wed Aug 27 10:48:01 CDT 2014

Total time taken to generate the page: 0.10506 seconds