UTL_SMTP PACKAGE - ORA-20001-421 SERVICE NOT AVAILABLE

[gnagesh]

Hi

When I try to send mails from DB using UTL_SMTP package, I am getting the follwoing error:

Declare
*
ERROR at line 1:
ORA-20001: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 83
ORA-06512: at "SYS.UTL_SMTP", line 121
ORA-06512: at line 12

Can any one had this kind of problem before, please help me to resolve this problem.

Thanks in advance.

Regards
Nagesh

[amar]

Do you have Java installed in the DB?

-amar

[julian]

R U using the correct email server name in the UTL_SMTP.OPEN_CONNECTION() call.

Ensure that there is SMTP server and the process is up and
running.

[gnagesh]

Hi,

I apologize for late reply. Yes, Java is loaded. and Server is up and running.

Nagesh.

[julian]

Originally posted by gnagesh
Hi,

I apologize for late reply. Yes, Java is loaded. and Server is up and running.

Nagesh.

Did you run something like:

loadjava -force -verbose -user sys/password@DB c:\ora817\plsql\jlib\plsql.jar

Is there an SMTP server and is the process up and running?

[gnagesh]

Hi Julian

Thanks for your attention. Now it is been solved. I had wrongly mentioned the IP address of mail server. Now it is working. Thank you once again.

One more query. How can I send oracle error messages to my mail id, how can i automate it. like whenever oracle writes a messages to alert files/log/trace files, It should be mailed to dba mail id's.

Thanks once again.

Regards
Nagesh

[julian]

You will have to schedule a job which reads, say, the alert file(s) (for example) ones in an hour. You read files in Oracle with UTL_FILE.

Here is an example from Metalink on how to use UTL_FILE for reading.

CREATE OR REPLACE PROCEDURE read_file AS

id UTL_FILE.FILE_TYPE;
name VARCHAR2(20) := 'my_file';
err VARCHAR2(100);
num NUMBER;
filedata VARCHAR2(2000);

BEGIN
id := UTL_FILE.FOPEN('/u05/home/tsupport/asoracco/utl_file',name,'r');

LOOP
BEGIN
UTL_FILE.GET_LINE(id,filedata);
DBMS_OUTPUT.PUT_LINE(filedata);

EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
END;
END LOOP;

UTL_FILE.FCLOSE(id);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(ID);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(ID);
WHEN OTHERS THEN
err := SQLERRM;
num := SQLCODE;
DBMS_OUTPUT.PUT_LINE(err);
DBMS_OUTPUT.PUT_LINE(num);
DBMS_OUTPUT.PUT_LINE('Error trying to read file');
END;
/

[gnagesh]

Thank You very much. I will try and let you know.

Thanks
Nagesh