0

I want to send newly inserted entries of audit table through EMAIL along with newly inserted data in CSV format. I am using after insert trigger to keep track of inserted rows, but my code is unable to send email. It's throwing error ORA-29279 : SMTP permanent error : 550 RBL: http://www.barracudanetwork.com/reputation/>pr=1&ip=xxx.xxx.xxx.xx

I am using my organization's email server. Below is my Code:

   CREATE OR REPLACE TRIGGER emp_after_insert
                   AFTER INSERT
                           ON scott.emp
                           FOR EACH ROW
                        enable
                       DECLARE
                          v_username varchar2(10);
                     v_no number(4);
                     v_name varchar2(10);
                 DIR_NAME  VARCHAR2(10):= 'UTL_FILE';
                 FILE_NAME  VARCHAR2(10):= 'EINFO.csv';
                 F1 UTL_FILE.FILE_TYPE;
                   PRESENT BOOLEAN;
                   FLENGTH NUMBER;
                   BSIZE PLS_INTEGER;
                 CNTR NUMBER:=0;
                   BEGIN
                    insert into scott.emp_audit(id,name,insertion_date) values(:new.empno,:new.ename,sysdate);
                    dbms_output.put_line('inserted');
           UTL_FILE.FGETATTR(LOCATION=>DIR_NAME,FILENAME=>FILE_NAME,
                FEXISTS=>PRESENT,FILE_LENGTH=>FLENGTH,
                BLOCK_SIZE=>BSIZE);
                IF PRESENT THEN
                F1:=UTL_FILE.FOPEN(DIR_NAME,FILE_NAME,'a');     ------APPEND MODE-
                UTL_FILE.PUT_LINE(F1,RPAD('',LENGTH(CURRENT_TIMESTAMP),''));
                ELSE
                F1:=UTL_FILE.FOPEN(DIR_NAME,FILE_NAME,'W');   -----WRITE MODE-----
                END IF;
           UTL_FILE.PUT_LINE(F1,RPAD(:new.empno,10,' ')||RPAD(:new.ename,10,' '));
         DBMS_OUTPUT.PUT_LINE('FILE CREATED ...'||FILE_NAME||'...AS ON ...'||CURRENT_TIMESTAMP);
         UTL_FILE.FCLOSE(F1);
          UTL_MAIL.send_attach_varchar2 (
       sender       => 'abc@gmail.com',
       recipients   => 'xyz123@gmail.com',
       subject      => 'UTL_MAIL Test',
       message      => 'If you get this message it worked!',
       attachment   => 'The is the contents of the attachment.',
       att_filename => 'C:\export\EINFO.csv'
     );
end;

I also execute ALTER SYSTEM SET smtp_out_server='mail.abc.com'

Also executed the scripts of utlmail and prvtmail. Please Help!

APC
  • 144,005
  • 19
  • 170
  • 281
Sumaiya Qureshi
  • 31
  • 9
  • 2
    The SMTP server is rejecting the email, not Oracle. Look up SMTP error 550 and real-time blackhole list (RBL - from the error text). – Alex Poole Feb 16 '20 at 13:44
  • Kindly elaborate how to do this? – Sumaiya Qureshi Feb 16 '20 at 14:57
  • 1
    At a guess it thinks it's spam because you're trying to send from a gmail address - which doesn't match the server you're sending through. If you have an email address from the domain you're sending through, use that as sender, possibly with a reply-to header. (Also it is not a good idea to have real email/host/IP in your posts.) – Alex Poole Feb 16 '20 at 15:39
  • 1
    Do you really want to send a mail for each inserted row? What happens when the user makes a ROLLBACK? The mail is sent anyway. DBMS_OUTPUT may raise additional error. Some of your procedures may issue a COMMIT where you get other errors. – Wernfried Domscheit Feb 16 '20 at 15:40
  • @WernfriedDomscheit yeah that's the issue but for now this is the requirement! – Sumaiya Qureshi Feb 16 '20 at 15:50
  • Hey @Wernfried. This will be the loan tracking trigger so there will be no rollback. – Sumaiya Qureshi Feb 17 '20 at 17:22

0 Answers0