emails not sending in regular interval from oracle

user12251389

I have below code which i am using for sending emails to SMTP.

create or replace PROCEDURE                    "SEND_MAIL_SMTP" (   sender     IN VARCHAR2, recipient  IN VARCHAR2, --CC  IN VARCHAR2, subject    IN VARCHAR2, message    IN LONG )IS   err_code VARCHAR(100);   err_msg VARCHAR(200);   mailhost     VARCHAR2(30) := 'w010.k';  -- -- host mail address   mail_conn    utl_smtp.connection ;   crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );   mesg long;   l_encoded_username VARCHAR2(300):= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('[email protected]')));   l_encoded_password VARCHAR2(300):= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('s01')));BEGIN   err_code :=SUBSTR(SQLCODE, 1, 100);   err_msg := SUBSTR(SQLERRM, 1, 200);   mail_conn := utl_smtp.open_connection(mailhost, 25);   mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||          'From:  <'||sender||'>' || crlf ||          'Subject: '||subject || crlf ||          'To: '||recipient || crlf ||          --'CC: '|| CC || crlf ||          '' || crlf || message;   --utl_smtp.helo(mail_conn, '85.13.150.55');   UTL_SMTP.ehlo(mail_conn, 'w010.k');   UTL_SMTP.command(mail_conn, 'AUTH', 'LOGIN');   UTL_SMTP.command(mail_conn, l_encoded_username);   UTL_SMTP.command(mail_conn, l_encoded_password);   utl_smtp.mail(mail_conn, sender);   utl_smtp.rcpt(mail_conn, recipient);   --utl_smtp.rcpt(mail_conn, CC);   utl_smtp.data(mail_conn, mesg);   utl_smtp.quit(mail_conn);   EXCEPTION    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN    BEGIN      UTL_SMTP.QUIT(mail_conn);    EXCEPTION      WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN        NULL; -- When the SMTP server is down or unavailable, we don't have              -- a connection to the server. The QUIT call raises an              -- exception that we can ignore.        WHEN OTHERS THEN        EXECUTE IMMEDIATE 'INSERT INTO SEND_MAIL_SMTP_EXCEPTION values(''MAIL FAILED ERROR'',error_code|| '' '' || err_msg,SYSDATE)';      END;    raise_application_error(-20000,      'Failed to send mail due to the following error: ' || sqlerrm);END;

And below is the code which takes the data from Track_Records table and send mails to [email protected]. I have created DB links for Verify and Production database and i am also taking the data from Track_Records table from this databases and also sending emails ata the same time with Test_Server database. I have create scheduler jobs every 1 hour to trigger Execute_Send_Mail_Smtp procedure. I am getting emails from PreProd database regularly but for Verifiy and Prodcution database its skiping the emails in between some time. I have checked the DB links its working fine. I dont know whether its because of transaction issue from Execute_Send_Mail_Smtp procedure as i am sending the 3 emails at the same time from Test_Server,Verify and Production database or is there any other reason. I am also tracking exception if any in SEND_MAIL_SMTP_EXCEPTION table. but this table is also empty which means no error with procedure.

create or replace PROCEDURE                    "EXECUTE_SEND_MAIL_SMTP" asPRAGMA AUTONOMOUS_TRANSACTION;v_kpi_preprod varchar2(2000) :=' ';v_kpi_verification varchar2(2000) :=' ';v_kpi_production varchar2(2000) :=' ';--CURSOR KPI_TRACK_RUNNING IS--Select KPI_NAME FROM RATOR_MONITORING.Track_Records;begin-----------------------------------------Send Mail from PreProd-----------------------------------------------------------------------------FOR Track_Records_Row IN (Select KPI_NAME,KPI_DEF_ID FROM Track_Records)LOOP  IF Track_Records_Row.KPI_NAME IS NULL THEN    v_kpi_preprod :='NO RECORDS EXIST IN Track_Records';    ELSE      v_kpi_preprod :=v_kpi_preprod||Track_Records_Row.KPI_NAME|| '  ' || Track_Records_Row.KPI_DEF_ID || chr(10) || chr(10);  END IF;END LOOP;  SEND_MAIL_SMTP(    '[email protected]',           --Sender    '[email protected]',           --Recipient    'TEST_SERVER - KPIs NOT RUNNING',                    --Subject    'The Quartz job did not complete for below KPIs:' || chr(10) || v_kpi_preprod   --Message  );-------------------------------------------Send Mail from Verification--------------------------------------------------------------------------------------------FOR Track_Records_Row IN (Select KPI_NAME,KPI_DEF_ID FROM [email protected])LOOP  IF Track_Records_Row.KPI_NAME IS NULL THEN    v_kpi_verification :='NO RECORDS EXIST IN Track_Records';      ELSE    v_kpi_verification :=v_kpi_verification||Track_Records_Row.KPI_NAME|| '  ' || Track_Records_Row.KPI_DEF_ID || chr(10) || chr(10);  END IF;END LOOP;  SEND_MAIL_SMTP(    '[email protected]',           --Sender    '[email protected]',           --Recipient    'VERIFICATION - KPIs NOT RUNNING',                    --Subject    'The Quartz job did not complete for below KPIs:' ||  chr(10) || v_kpi_verification    --Message  );---------------------------------------------Send Mail from Production-------------------------------------------------------------------------------------FOR Track_Records_Row IN (Select KPI_NAME,KPI_DEF_ID FROM [email protected]_RATOR_MONITORING)LOOP  IF Track_Records_Row.KPI_NAME IS NULL THEN    v_kpi_production :='NO RECORDS EXIST IN Track_Records';      ELSE    v_kpi_production :=v_kpi_production||Track_Records_Row.KPI_NAME|| '  ' || Track_Records_Row.KPI_DEF_ID || chr(10) || chr(10);  END IF;END LOOP;  SEND_MAIL_SMTP(    '[email protected]',           --Sender    '[email protected]',           --Recipient    'PRODUCTION - KPIs NOT RUNNING',                    --Subject    'The Quartz job did not complete for below KPIs:' || chr(10) || v_kpi_production   --Message  );---------------------------------------------------------------------------------------------------------------------------------------------------------------EXCEPTION WHEN OTHERS THEN  EXECUTE IMMEDIATE 'INSERT INTO SEND_MAIL_SMTP_EXCEPTION values(''MAIL FAILED ERROR'',error_code|| '' '' || err_msg,SYSDATE)';  RAISE;end execute_send_mail_smtp;

BluShadow

Why do you want it as an autonomous transaction anyway?

It seems odd that you would be sending emails as part of a transaction, as emails cannot be rolled back if anything prevents the main transaction from completing.

A more stable way is to have you transaction queue up emails to be sent with the appropriate details on a table, and then have a scheduled job running periodically to process the queue and send emails that are visible on that table (which they will be once the main transaction has been committed).  Then you know that the data is committed and you're in a good position for sending the emails.  (You can also look at Oracle Advanced Queuing (AQ) as another method of queuing)

BluShadow

An autonomous procedure should have a commit at the end of it, especially if you issue DML statements.

Your WHEN OTHERS EXCEPTION inserts to your exception table, but does not commit (and why you are using an EXECUTE IMMEDIATE statement for it I really don't know - what's wrong with a regular INSERT statement?)

user12251389

Ok i will try to commit at the end and check for next few hours and i am using EXECUTE IMMEDIATE statement because in exception handling if i use normal INSERT statement then i am getting error as sql statement not in scope.

BluShadow

?  Of course you can use SQL statements in an exception block; there's no such thing as an SQL statement not being in scope.  I suspect the error was because you were not including a commit, and it was an autonomous transaction.  Try converting it back to a regular INSERT and include a commit after it to see.

user12251389

In the exception handling i tried to give normal INSERT statement and then try to commit inside and also tried outside exception:

EXCEPTION WHEN OTHERS THEN  INSERT INTO SEND_MAIL_SMTP_EXCEPTION values('MAIL FAILED ERROR',error_code|| ' ' || err_msg,SYSDATE);  RAISE;commit;  

And i am getting error for the line where i have used INSERT statement as:

 PL/SQL: ORA-00984: column not allowed here 

I checked the below forum where there are saying

You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement

https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/07_errs.htm

Cookiemonster76

And the advice is correct.

You also need to move the commit above the raise or it'll never get executed.

user12251389

using normal INSERT statement i am getting the same error when i move the commit above the raise. Do i need to provide to 2 commit in my procedure, one for the other insert statements and one inside exception like below ?

EXCEPTION WHEN OTHERS THEN  EXECUTE IMMEDIATE 'INSERT INTO SEND_MAIL_SMTP_EXCEPTION values(''MAIL FAILED ERROR'',error_code|| '' '' || err_msg,SYSDATE)';  commit;  RAISE;Commit;

Cookiemonster76

The word 'also' means you have to do want I said in addition to fixing the insert.

I don't know what error_code and err_msg are. You want to use sqlerrm. And you'll need to assign it to a variable and use the variable in the insert.

Chris Hunt

I think your problem is in your exception handling.

If send_mail_smtp fails due to a transient or permanent error, it tries to quit the connection and only attempts to write to your log table if the quit attempt fails for a non transient/permanent error. Then it calls raise_application error. Any other type of error goes unhandled.

If execute_send_mail_smtp fails, or it picks up an unhandled exception from send_mail_smtp, you attempt to write a row to the log table. But there's no commit to save that row so you can see it later, and in any case I think the attempt will fail because your EXECUTE IMMEDIATE code includes PL/SQL variables which are out of scope.

I'd try running it without any exception handlers, and then look in the ALL_SCHEDULER_JOB_RUN_DETAILS to see the errors.

If you want to record error information in your own table, create a procedure that does only that:

CREATE OR REPLACE PROCEDURE log_smtp_exception (p_error IN VARCHAR2, p_details IN VARCHAR2) ASPRAGMA AUTONOMOUS_TRANSACTION;BEGIN   INSERT INTO send_mail_smtp_exception VALUES (p_error, p_details, sysdate);   COMMIT;END;

Then you can call it like this in your exceptions (and your calling procedures do not need to be autonomous transactions or include extra commits):

EXCEPTION WHEN OTHERS THEN   log_smtp_exception ('MAIL FAILED ERROR',error_code||' '||err_msg);

What your actual problem is will be easier to work out once you're seeing the error messages.

BluShadow

Assign err_msg (you don't need error_code as it's in the message already) to a variable and then use that variable in your INSERT statement.  You shouldn't use err_msg directly in DML.

user12251389

Yes i did this. I have assigned the err_code and err_msg to a variable:

err_code :=SUBSTR(SQLCODE, 1, 100);err_msg := SUBSTR(SQLERRM, 1, 200);

And now my exception handling looks like below:

EXCEPTION WHEN OTHERS THEN  INSERT INTO SEND_MAIL_SMTP_EXCEPTION values('MAIL FAILED ERROR',err_code|| ' ' || err_msg,SYSDATE);  commit;  RAISE;

But when i execute the procedure now i am getting error as :

ORA-06519: active autonomous transaction detected and rolled back 

this error is coming at the line where i have used commit