How to send more than 32K message
A reader, July 02, 2002 - 9:58 am UTC
Tom,
It would be really helpful for me if you can give me an example. because I am not much aware of lob.
I think i am asking too much from you.
Thanks,
Venkat
July 02, 2002 - 11:35 am UTC
Surprisingly -- there are actually documents describing this!
http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/index.htm
application developers guides for large objects....
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_clob clob;
3 begin
4 dbms_lob.createTemporary( l_clob, TRUE );
5
6 for i in 1 .. 100
7 loop
8 dbms_lob.writeAppend( l_clob, 32000, rpad('*',32000,'*') );
9 end loop;
10
11 dbms_output.put_line( 'I have put ' || dbms_lob.getLength(l_clob) || ' bytes of data into a clob!' );
12 end;
13 /
I have put 3200000 bytes of data into a clob!
PL/SQL procedure successfully completed.
Lee, July 02, 2002 - 11:14 am UTC
I have a proc that uses Tom's html email proc with some LOB modification, I'll posted here if Tom do not mind.
July 02, 2002 - 2:35 pm UTC
Sure. Go for it. (try to keep it 72 characters wide)
How to send more than 32K message
A reader, July 02, 2002 - 12:20 pm UTC
Hi Tom,
Thank you very very...............very much. this really
helped me.
Venkat
Lee, July 02, 2002 - 2:49 pm UTC
This is the function, I have a nother one that wil parse an sql statment and return the results as an HTMS then call this function with the results. Again, if you like I can post that one too.
I hope it's helpfull.
P.S.
Tom, if you see anything that can be improved I'll appritiate your comments (arter all, it is your proc)
FUNCTION HTML_EMAIL(P_TO IN VARCHAR2,
P_FROM IN VARCHAR2,
P_SUBJECT IN VARCHAR2,
P_TEXT IN VARCHAR2 DEFAULT NULL,
P_HTML IN VARCHAR2 DEFAULT NULL,
P_SMTP_HOSTNAME IN VARCHAR2 DEFAULT 'MAIL.DEALTIME.COM',
P_SMTP_PORTNUM IN NUMBER DEFAULT 25,
P_LOB_HTML IN CLOB DEFAULT NULL)
RETURN VARCHAR2
IS
L_BOUNDARY VARCHAR2(255) DEFAULT 'a1b2c3d4e3f2g1';
L_CONNECTION UTL_SMTP.CONNECTION;
L_BODY_HTML CLOB := EMPTY_CLOB; --THIS LOB WILL BE THE EMAIL MESSAGE
L_OFFSET NUMBER;
L_AMMOUNT NUMBER;
L_TEMP VARCHAR2(32767) DEFAULT NULL;
V_TO VARCHAR2(1000) := LOWER(P_TO);
V_FLAG BOOLEAN := FALSE;
V_PROC_STEP VARCHAR2(4000);
BEGIN
V_PROC_STEP := 'STEP 1';
IF V_TO NOT LIKE '%lsagi@dealtime.com%' THEN
V_TO := V_TO||';lsagi@dealtime.com';
END IF;
V_PROC_STEP := 'STEP 1.5';
L_CONNECTION := UTL_SMTP.OPEN_CONNECTION( P_SMTP_HOSTNAME, P_SMTP_PORTNUM );
UTL_SMTP.HELO( L_CONNECTION, P_SMTP_HOSTNAME );
UTL_SMTP.MAIL( L_CONNECTION, P_FROM );
-- UTL_SMTP.RCPT( L_CONNECTION, P_TO );
L_TEMP := L_TEMP || 'MIME-Version: 1.0' || CHR(13) || CHR(10);
V_PROC_STEP := 'STEP 2';
IF INSTR(V_TO, ';', 1, 1) = 0 THEN
L_TEMP := L_TEMP || 'To: ' || V_TO || CHR(13) || CHR(10);
UTL_SMTP.RCPT( L_CONNECTION, V_TO );
ELSE
WHILE V_FLAG = FALSE LOOP
SQL_STM := SUBSTR(V_TO, 1, INSTR(V_TO, ';', 1, 1) - 1);
V_FLAG := INSTR(V_TO, ';', 1, 1) = 0;
V_TO := NVL(SUBSTR(V_TO, LENGTH(SQL_STM) + 2), V_TO);
L_TEMP := L_TEMP || 'To: ' || NVL(SQL_STM, V_TO) || CHR(13) || CHR(10);
UTL_SMTP.RCPT( L_CONNECTION, NVL(SQL_STM, V_TO));
END LOOP;
END IF;
L_TEMP := L_TEMP || 'From: ' || P_FROM || CHR(13) || CHR(10);
L_TEMP := L_TEMP || 'Subject: ' || P_SUBJECT || CHR(13) || CHR(10);
L_TEMP := L_TEMP || 'Reply-To: ' || P_FROM || CHR(13) || CHR(10);
L_TEMP := L_TEMP || 'Content-Type: multipart/alternative; boundary=' ||
CHR(34) || l_BOUNDARY || CHR(34) || CHR(13) ||
CHR(10);
----------------------------------------------------
V_PROC_STEP := 'STEP 3';
-- Write the headers
DBMS_LOB.CREATETEMPORARY( L_BODY_HTML, FALSE, 10 );
DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),1,L_TEMP);
----------------------------------------------------
V_PROC_STEP := 'STEP 4';
-- WRITE THE TEXT BOUNDARY
L_OFFSET := DBMS_LOB.GETLENGTH(L_BODY_HTML) + 1;
L_TEMP := '--' || L_BOUNDARY || CHR(13)||CHR(10);
L_TEMP := L_TEMP || 'content-type: text/plain; charset=us-ascii' ||
CHR(13) || CHR(10) || CHR(13) || CHR(10);
DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),L_OFFSET,L_TEMP);
----------------------------------------------------
V_PROC_STEP := 'STEP 5';
IF P_TEXT IS NOT NULL THEN
-- WRITE THE PLAIN TEXT PORTION OF THE EMAIL
L_OFFSET := DBMS_LOB.GETLENGTH(L_BODY_HTML) + 1;
DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(P_TEXT),L_OFFSET,P_TEXT);
END IF;
----------------------------------------------------
V_PROC_STEP := 'STEP 6';
-- WRITE THE HTML BOUNDARY
L_TEMP := CHR(13)||CHR(10)||CHR(13)||CHR(10)||'--' || L_BOUNDARY ||
CHR(13) || CHR(10);
L_TEMP := L_TEMP || 'content-type: text/html;' ||
CHR(13) || CHR(10) || CHR(13) || CHR(10);
L_OFFSET := DBMS_LOB.GETLENGTH(L_BODY_HTML) + 1;
DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),L_OFFSET,L_TEMP);
----------------------------------------------------
V_PROC_STEP := 'STEP 7';
BEGIN
IF P_HTML IS NOT NULL THEN
-- WRITE THE HTML PORTION OF THE MESSAGE
L_OFFSET := DBMS_LOB.GETLENGTH(L_BODY_HTML) + 1;
V_PROC_STEP := 'STEP 7.1';
DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(P_HTML),L_OFFSET,P_HTML);
V_PROC_STEP := 'STEP 7.2';
ELSIF P_LOB_HTML IS NOT NULL THEN
-- WRITE THE LOB HTML PORTION OF THE MESSAGE
L_OFFSET := DBMS_LOB.GETLENGTH(L_BODY_HTML) + 1;
V_PROC_STEP := 'STEP 7.3';
DBMS_LOB.COPY(L_BODY_HTML, P_LOB_HTML, DBMS_LOB.GETLENGTH(P_LOB_HTML), L_OFFSET, 1);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.WRITE(L_BODY_HTML,LENGTH('STEP 7 ~ '||SQLERRM),L_OFFSET, 'STEP 7 ~ '||SQLERRM);
END;
----------------------------------------------------
V_PROC_STEP := 'STEP 8';
-- WRITE THE FINAL HTML BOUNDARY
L_TEMP := CHR(13) || CHR(10) || '--' || L_BOUNDARY || '--' || CHR(13);
L_OFFSET := DBMS_LOB.GETLENGTH(L_BODY_HTML) + 1;
DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),L_OFFSET,L_TEMP);
----------------------------------------------------
V_PROC_STEP := 'STEP 9';
-- SEND THE EMAIL IN 1900 BYTE CHUNKS TO UTL_SMTP
L_OFFSET := 1;
L_AMMOUNT := 1900;
UTL_SMTP.OPEN_DATA(L_CONNECTION);
WHILE L_OFFSET < DBMS_LOB.GETLENGTH(L_BODY_HTML) LOOP
UTL_SMTP.WRITE_DATA(L_CONNECTION,
DBMS_LOB.SUBSTR(L_BODY_HTML,L_AMMOUNT,L_OFFSET));
L_OFFSET := L_OFFSET + L_AMMOUNT ;
L_AMMOUNT := LEAST(1900,DBMS_LOB.GETLENGTH(L_BODY_HTML) - L_AMMOUNT);
END LOOP;
UTL_SMTP.CLOSE_DATA(L_CONNECTION);
UTL_SMTP.QUIT( L_CONNECTION );
DBMS_LOB.FREETEMPORARY(L_BODY_HTML);
RETURN 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
-- SQL_STM := GET_PARAMETER_VAL('HTML_EMAIL');
-- IF SQL_STM = 'SUCCESS' THEN
-- SQL_STM := 'SELECT ';
-- FOR I IN 1 .. T_VAR.COUNT LOOP
-- SQL_STM := SQL_STM||''''||T_VAR(I)||''' = '||T_VAR(I)||'||'' ~ ''||';
-- END LOOP;
-- END IF;
--
-- SQL_STM := RTRIM(SQL_STM, '||'' ~ ''||')||' FROM DUAL';
--SQL_STM := 'SELECT P_TO FROM DUAL';
--DBMS_OUTPUT.PUT_LINE(SQL_STM);
--EXECUTE IMMEDIATE SQL_STM INTO V_PARAMETERS;
VAR := Lee_Utl.STATS_AUD ('LEE_UTL.HTML_EMAIL',
P_TO||' ~ '||P_FROM||' ~ '||V_PROC_STEP,
SYSDATE,
SYSDATE,
V_EXEC_USER,
'N1 ~ '||SQLERRM);
VAR := Email (V_EXEC_USER,
'lsagi@dealtime.com',
NULL,
NULL,
'ERROR ~ LEE_UTL.HTML_EMAIL',
SQLERRM);
RETURN SQLERRM;
END;
Lee's code:
Samosa Kachori, July 02, 2002 - 4:33 pm UTC
Hi Lee,
can you post that one also? This is very useful! Thank you so much!
How to pass clob from frontend? and convert to varchar2 in backend
A reader, September 19, 2002 - 10:18 am UTC
I am using COMMA_TO_TABLE to convert a string passed from java/jsp frontend into a plqsltable and loop that plsqltable to update a database table value.
Since only 32k sring can be passed inside the proc,
i have a to use IN parameter as CLOB (which was currently varchar2).
My databse table col1 datatype is varchar2(20). I should be able to pass any number of col1 values seperated by comma.
My question is :
1.How do i pass this 'comma seperated string' as a CLOB from the frontend into the procedure?
2.How do i convert that long string (clob) back into a varchar2 inside my proc and update/insert my table.
Ex: I need to do something like
create procedure x(v in clob)
as
...
p dbms_utility.uncl_array;
n number
DBMS_UTILITY.COMMA_TO_TABLE('"'||REPLACE(v, ',', '","')||'"', n, p);
Then Loop the table populated, and update some table:
for i in 1..n
update t set col1=p(i) where something...
or insert p(i) into col1
So i just need to use CLOB to 'pass' a long string from frontend whil the underlying datatype of teh col1 is varchar2.
How do i do this both frontend (how to convert string into clob and pass as proc parameter from java/jsp) and backend (how to insert it as varchar2 afer converting clob string)
The string is a user selection of certin values which the user checks (checkboxes for example), each string checked is appended with a comma and sent into a proc. I have to eliminate the 32k limit.
Thanks.
September 20, 2002 - 7:39 am UTC
comma to table is NOT designed to do that at all! Beware. If you have my book -- I describe what it does, and it is not for this.
Now, you want to pass a string of more then 32k -- so you are thinking "clob", however the clob will require you to make a couple of round trips back and forth in order to construct it (hence the single call over, single call back is somewhat defeated).
My solution to this would DEFINITELY be the following:
o make sure the first line of my application in java is "conn.setAutoCommit (false);"
o one time, i would (in sqlplus)
create global temporary table listOfValues ( data <whatever datatype> )
on commit delete rows;
o in the java program, set the .setDefaultExecuteBatch on a prepared statement to say 100 or so
o insert the values into the listOfValues table (they will be batched and sent 100 or so at a time)
o run the procedure and have it use the global temp table.
It'll save tons of angst in
a) constructing this big string
b) destructing this big string
c) using clobs for something that can be done easier
d) avoiding the issues you'll hit with comma_to_table
(and you must have the biggest screens in the entire planet with very patient end users if they can select things from a list that'll exceed 32k -- especially since comma to table only handles things 30 chars or less -- they would have to pick over 1,000 things from this list! wow)
Output a big file without linefeeds!
Guy, October 31, 2003 - 5:17 am UTC
Tom,
I have an output file that I have to produce for the UK Inland Revenue (i.e. mandatory file format), where the records are not separated by linefeeds (they use pipe characters) and CR/LF's are NOT allowed in the file.
I get execptions using UTL_PUT after 32K and I tried to use UTL_FILE.FFLUSH but it also needs a CRLF.
Any way I can create this file in PLSQL?
Ta - Guy
October 31, 2003 - 8:02 am UTC
you would have to use a java stored procedure or an external procedure or an external process (program). plsql is going to put a linefeed in there.
vamsi, January 21, 2009 - 6:38 am UTC
Hi Tom
Its ok for send more than 32k message as mail.
But i want to send more than 32k as attachment, Iam using CLOB but it is not working.
send me a sample program regarding this issue.
Plz help me.
January 21, 2009 - 1:12 pm UTC
"but it is not working"
what isn't working?
how isn't it working?
Lee's Code
Kasia, July 14, 2022 - 12:32 pm UTC
How would you modify it to incorporate a CC option (Carbon Copy) ?
July 18, 2022 - 3:36 am UTC
Every recipient (To, CC etc) needs to be passed in with a call to
UTL_SMTP.RCPT( L_CONNECTION, V_TO );
After that, simply changing the "TO:" to "CC:" on particular recipients will take care of it.
However, for anyone wanting to mail from the database, my recommendation is simply to install APEX and let us do the heavy lifting for you, because then all you need is APEX_MAIL
SQL> desc apex_mail
PROCEDURE ADD_ATTACHMENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_MAIL_ID NUMBER IN
P_ATTACHMENT BLOB IN
P_FILENAME VARCHAR2 IN
P_MIME_TYPE VARCHAR2 IN
P_CONTENT_ID VARCHAR2 IN DEFAULT
PROCEDURE ADD_ATTACHMENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_MAIL_ID NUMBER IN
P_ATTACHMENT CLOB IN
P_FILENAME VARCHAR2 IN
P_MIME_TYPE VARCHAR2 IN
FUNCTION GET_IMAGES_URL RETURNS VARCHAR2
FUNCTION GET_INSTANCE_URL RETURNS VARCHAR2
PROCEDURE PREPARE_TEMPLATE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_STATIC_ID VARCHAR2 IN
P_PLACEHOLDERS CLOB IN
P_APPLICATION_ID NUMBER IN DEFAULT
P_SUBJECT VARCHAR2 OUT
P_HTML CLOB OUT
P_TEXT CLOB OUT
P_LANGUAGE_OVERRIDE VARCHAR2 IN DEFAULT
PROCEDURE PUSH_QUEUE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SMTP_HOSTNAME VARCHAR2 IN DEFAULT
P_SMTP_PORTNO VARCHAR2 IN DEFAULT
PROCEDURE SEND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TO VARCHAR2 IN
P_FROM VARCHAR2 IN
P_BODY VARCHAR2 IN
P_BODY_HTML VARCHAR2 IN DEFAULT
P_SUBJ VARCHAR2 IN DEFAULT
P_CC VARCHAR2 IN DEFAULT
P_BCC VARCHAR2 IN DEFAULT
P_REPLYTO VARCHAR2 IN DEFAULT
PROCEDURE SEND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TO VARCHAR2 IN
P_FROM VARCHAR2 IN
P_BODY CLOB IN
P_BODY_HTML CLOB IN DEFAULT
P_SUBJ VARCHAR2 IN DEFAULT
P_CC VARCHAR2 IN DEFAULT
P_BCC VARCHAR2 IN DEFAULT
P_REPLYTO VARCHAR2 IN DEFAULT
PROCEDURE SEND
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TEMPLATE_STATIC_ID VARCHAR2 IN
P_PLACEHOLDERS CLOB IN
P_TO VARCHAR2 IN
P_CC VARCHAR2 IN DEFAULT
P_BCC VARCHAR2 IN DEFAULT
P_FROM VARCHAR2 IN DEFAULT
P_REPLYTO VARCHAR2 IN DEFAULT
P_APPLICATION_ID NUMBER IN DEFAULT
P_LANGUAGE_OVERRIDE VARCHAR2 IN DEFAULT
FUNCTION SEND RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TO VARCHAR2 IN
P_FROM VARCHAR2 IN
P_BODY VARCHAR2 IN
P_BODY_HTML VARCHAR2 IN DEFAULT
P_SUBJ VARCHAR2 IN DEFAULT
P_CC VARCHAR2 IN DEFAULT
P_BCC VARCHAR2 IN DEFAULT
P_REPLYTO VARCHAR2 IN DEFAULT
FUNCTION SEND RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TO VARCHAR2 IN
P_FROM VARCHAR2 IN
P_BODY CLOB IN
P_BODY_HTML CLOB IN DEFAULT
P_SUBJ VARCHAR2 IN DEFAULT
P_CC VARCHAR2 IN DEFAULT
P_BCC VARCHAR2 IN DEFAULT
P_REPLYTO VARCHAR2 IN DEFAULT
FUNCTION SEND RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TEMPLATE_STATIC_ID VARCHAR2 IN
P_PLACEHOLDERS CLOB IN
P_TO VARCHAR2 IN
P_CC VARCHAR2 IN DEFAULT
P_BCC VARCHAR2 IN DEFAULT
P_FROM VARCHAR2 IN DEFAULT
P_REPLYTO VARCHAR2 IN DEFAULT
P_APPLICATION_ID NUMBER IN DEFAULT
P_LANGUAGE_OVERRIDE VARCHAR2 IN DEFAULT
SQL>