What's exactly the problem ?No problem as such.
DECLARE v_conn UTL_SMTP.connection; v_smtp_host VARCHAR2 (50) := '127.0.0.1'; v_smtp_port VARCHAR2 (10) := 25; BEGIN UTL_TCP.Close_All_Connections(); v_conn := UTL_SMTP.open_connection ('127.0.0.1', -- smtp host '25' -- smtp port ); UTL_SMTP.helo (v_conn, '127.0.0.1'); UTL_SMTP.mail (v_conn, 'email@example.com'); UTL_SMTP.rcpt (v_conn, 'firstname.lastname@example.org'); UTL_SMTP.open_data (v_conn); UTL_SMTP.write_data (v_conn, 'testing : Hello Universe'); UTL_SMTP.close_data (v_conn); UTL_SMTP.quit (v_conn); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('loc... ' || DBMS_UTILITY.format_error_backtrace); DBMS_OUTPUT.put_line (SQLERRM); END;
I just installed UTL_SMTP.sql but still getting this.
loc... ORA-06512: at "SYS.UTL_SMTP", line 54 ORA-06512: at "SYS.UTL_SMTP", line 138 ORA-06512: at "SYS.UTL_SMTP", line 197 ORA-06512: at line 9 ORA-29278: SMTP transient error: 421 Service not available
So you're running a mailserver on localhost? Or not? That could explain the 'service not available' error.error on executing this - ... v_smtp_host VARCHAR2 (50) := '127.0.0.1'; ... UTL_SMTP.helo (v_conn, '127.0.0.1');
Yes indeed it is possible.I thought might be connecting to an XE database remotely is also not possible.
Know the remote database host, port, and service_name. Or the ORACLE_SID for the database instance could be used in place of service_name.
tnsping xe ... TNS Ping Utility ... parameter files:... <client %ORACLE_HOME%>\network\admin\sqlnet.ora Attempting to contact (DESCRIPTION=... host ... port ... service_name ... OK (<n> msec) # Or result can also be not OK, like: TNS-12535: TNS:operation timed out # maybe invalid hostname, or host is offline/down TNS-12541: TNS:no listener # doh ... the listener is not running on that port at that host # to verify the correct service_name: sqlplus /nolog connect <username>@xe ... password ... Connected. # or not. maybe: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect # doh client specified service_name is not correct
clcarter wrote:I didn't get the question you are asking? How can i check if i'm running mailserver or not?So you're running a mailserver on localhost? Or not? That could explain the 'service not available' error.error on executing this - ... v_smtp_host VARCHAR2 (50) := '127.0.0.1'; ... UTL_SMTP.helo (v_conn, '127.0.0.1');
Try pointing smtp_host to ... a host that has a running smtp service.How to do this?
Install and configure a mailserver.How to do this?
Yes there is some setup required for sending email from the database.does mail sending mechanism need some special setting in XE?