Thanks for the question, ian.
Asked: May 07, 2006 - 7:04 pm UTC
Last updated: May 08, 2006 - 1:30 pm UTC
Version: 10G express
Viewed 10K+ times! This question is
You Asked
Hi Tom,
Have recently upgraded to 10G express for my developemnt system
All has been running without any problems until I tried to compile a form which was accessing an utl_smtp procedure to send emails ( based on sample from your site )
Procedure ran fine under 8i and 9i but comes up with these errors:-
SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 7 23:49:25 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> start html_email
Warning: Procedure created with compilation errors.
Errors for PROCEDURE HTML_EMAIL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
11/21 PL/SQL: Item ignored
11/21 PLS-00201: identifier 'UTL_SMTP' must be declared
17/5 PL/SQL: Statement ignored
17/5 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
18/5 PL/SQL: Statement ignored
18/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
19/5 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
19/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
20/5 PL/SQL: Statement ignored
20/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
75/5 PL/SQL: Statement ignored
75/24 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
LINE/COL ERROR
-------- -----------------------------------------------------------------
77/9 PL/SQL: Statement ignored
77/29 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
82/5 PL/SQL: Statement ignored
82/25 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
83/5 PL/SQL: Statement ignored
83/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
LINE/COL ERROR
-------- -----------------------------------------------------------------
SQL>
Source of procedure is
create or replace procedure 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,
p_smtp_portnum in 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;
begin
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);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
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);
----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);
----------------------------------------------------
-- 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);
----------------------------------------------------
-- 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);
----------------------------------------------------
-- 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);
----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
----------------------------------------------------
-- 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);
----------------------------------------------------
-- 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);
end;
/
show errors;
Looks familiar ?
Is utl_smtp part of 10g express or do i need standard edition/Enterprise edition ?
Thanks for your time and look forward to hearing from you
Ian
and Tom said...
sys@XE> select * from dba_tab_privs where table_name = 'UTL_SMTP';
GRANTEE OWNER
------------------------------ ------------------------------
TABLE_NAME GRANTOR
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
FLOWS_020100 SYS
UTL_SMTP SYS
EXECUTE NO NO
sys@XE>
In XE - you must grant access to UTL_SMTP to the people you want to have access. It is not granted to PUBLIC.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment