Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

code for the requirement

i have to send a mail, if the email address im sending to has '-test' in it like for ex: tom-test@hevars.com or tom@hevars.com-test,  i have to ignore it by sending the mail to a qa alias or dev alias mailer list (because if i send it to ids having '-test' it will show mail delivery failure). So all this happens in a non production database
i would like to know the code for the above requirement if
message_to is the variable where the address are sent
like i would to know if the below code is correct
 select name into v_db_name from v$database;
if v_db_name = 'DMPROD' then
null
else
v_final_to_ids:=message_to Not like %-test;
v_final_to_ids :=qa_alias;
v_final_cc_ids :=dev_alias;
end if;
i would like to know the part where v_final_to_ids := message_to Not like %-test is used
0
thota198
Asked:
thota198
  • 6
  • 5
  • 2
1 Solution
 
OP_ZaharinCommented:
- i might be wrong to understand your pl/sql codes but how do you get the message_to variable data? if it's from query to a table, why not using a WHERE clause on that sql to exclude for those with '-test'?

eg: SELECT email FROM mailtable WHERE email NOT LIKE '%-test%'
0
 
thota198Author Commented:
how do u write this in loop
here cq_notification is the table where  to_address field is there

 FOR j IN c_notification
      LOOP
         
         
          v_final_to_ids   :=
            REPLACE (
               TRIM (
                  cq_notification_pkg.remove_extra_comma (
                     TRANSLATE (
                        NVL (j.to_address || ' ', ','),
                        (TRANSLATE (NVL (UPPER (j.to_address || ' '), '#'),
                                    '#.@-_1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ,',
                                    '#'
                                   )),
                        ','))),
               ' ',
               '');
         v_final_cc_ids      :=
            REPLACE (
               TRIM (
                  cq_notification_pkg.remove_extra_comma (
                     TRANSLATE (
                        NVL (j.cc_address || ' ', ','),
                        (TRANSLATE (NVL (UPPER (j.cc_address || ' '), '#'),
                                    '#.@-_1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ,',
                                    '#'
                                   )),
                        ','))),
               ' ',
               '');

         
         IF (v_db_name = 'DMPROD')
         THEN
            v_final_to_ids := remove_tmp_ids (v_final_to_ids);
            v_final_cc_ids := remove_tmp_ids (v_final_cc_ids);
         END IF;
0
 
OP_ZaharinCommented:
- can u share the sql statement to get j.to_address?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
thota198Author Commented:
this is the sql statement above the code ive given in previous one

 CURSOR c_notification
      IS
     
         SELECT *
           FROM (SELECT   dmsg.object_id,
                          DECODE (
                             INSTR (dmsg.message_to, '@'),
                             0, DECODE (REPLACE (dmsg.message_to, ',', '@cisco.com,'),
                                        NULL, NULL,
                                        REPLACE (dmsg.message_to, ',', '@cisco.com,') || '@cisco.com'
                                       ),
                             dmsg.message_to)
                             to_address,
                          DECODE (
                             INSTR (dmsg.message_cc, '@'),
                             0, DECODE (REPLACE (dmsg.message_cc, ',', '@cisco.com,'),
                                        NULL, NULL,
                                        REPLACE (dmsg.message_cc, ',', '@cisco.com,') || '@cisco.com'
                                       ),
                             dmsg.message_cc)
                             cc_address,
                          dmsg.message_subject,
                          dmsg.message_body,
                          ddl.deal_type deal_type,
                          dmsg.MESSAGE_TYPE,
                          ddl.opty_number,
                          dmsg.attachment_file_name,
                          dmsg.attachment_file_data,
                          ddl.active_workflow active_workflow
                     FROM cq_notification dmsg, cq_deal ddl
                    WHERE status IN ('NEW', 'ERROR')
                          AND ddl.opty_number(+) = dmsg.deal_id
                          AND dmsg.message_to IS NOT NULL
                          AND dmsg.message_body IS NOT NULL
                          AND dmsg.message_create_on > v_buffer_date
                 ORDER BY dmsg.message_create_on ASC)
          WHERE ROWNUM <= 100;
   BEGIN
      BEGIN
         SELECT host_name INTO v_email_host FROM v$instance;
      EXCEPTION
         WHEN OTHERS
         THEN
            v_email_host := 'outbound.cisco.com';
      END;

      BEGIN
         SELECT name INTO v_db_name FROM v$database;
      EXCEPTION
         WHEN OTHERS
         THEN
            v_db_name := NULL;
      END;
0
 
OP_ZaharinCommented:
- i'm doing a quick run thru your code.
- i assume dmsg.message_to contain the email id/addresses? if yes, add a where clause:
AND dmsg.message_to NOT LIKE '%-test%'
this will exclude test emailid from the list.
0
 
thota198Author Commented:
i mean how exactly should i write in the cursor
0
 
OP_ZaharinCommented:
- i bold the new line in your cursor:

CURSOR c_notification
      IS
     
         SELECT *
           FROM (SELECT   dmsg.object_id,
                          DECODE (
                             INSTR (dmsg.message_to, '@'),
                             0, DECODE (REPLACE (dmsg.message_to, ',', '@cisco.com,'),
                                        NULL, NULL,
                                        REPLACE (dmsg.message_to, ',', '@cisco.com,') || '@cisco.com'
                                       ),
                             dmsg.message_to)
                             to_address,
                          DECODE (
                             INSTR (dmsg.message_cc, '@'),
                             0, DECODE (REPLACE (dmsg.message_cc, ',', '@cisco.com,'),
                                        NULL, NULL,
                                        REPLACE (dmsg.message_cc, ',', '@cisco.com,') || '@cisco.com'
                                       ),
                             dmsg.message_cc)
                             cc_address,
                          dmsg.message_subject,
                          dmsg.message_body,
                          ddl.deal_type deal_type,
                          dmsg.MESSAGE_TYPE,
                          ddl.opty_number,
                          dmsg.attachment_file_name,
                          dmsg.attachment_file_data,
                          ddl.active_workflow active_workflow
                     FROM cq_notification dmsg, cq_deal ddl
                    WHERE status IN ('NEW', 'ERROR')
                          AND ddl.opty_number(+) = dmsg.deal_id
                          AND dmsg.message_to IS NOT NULL
                          AND dmsg.message_to NOT LIKE '%-test%'
                          AND dmsg.message_body IS NOT NULL
                          AND dmsg.message_create_on > v_buffer_date
                 ORDER BY dmsg.message_create_on ASC)
          WHERE ROWNUM <= 100;
   BEGIN
      BEGIN
         SELECT host_name INTO v_email_host FROM v$instance;
      EXCEPTION
         WHEN OTHERS
         THEN
            v_email_host := 'outbound.cisco.com';
      END;

      BEGIN
         SELECT name INTO v_db_name FROM v$database;
      EXCEPTION
         WHEN OTHERS
         THEN
            v_db_name := NULL;
      END;
0
 
slightwv (䄆 Netminder) Commented:
If I understand the question, you are wanting to replace '-test' or 'test-' email addresses with a fixed email alias in a string of email addresses?

If so, check out the test below.

If I do understand your requirement and the code you posted, I think there might be a problem.  Check out the 'c,d' in my example.  If I understand your code it is looking to replace any non-qualified email addresses with '@cisco.com'.  If so, it isn't replacing the 'c,d' above.


drop table tab1 purge;
create table tab1(message_to varchar2(50));

insert into tab1 values('a@a.com, b@b.com, c, d');
insert into tab1 values('a@a.com, b@b-test.com, c, d');
insert into tab1 values('c-test, d');
commit;

SELECT  
regexp_replace(
DECODE (
	INSTR (message_to, '@'),
	0,
	DECODE (
		REPLACE (message_to, ',', '@cisco.com,'),
		NULL,
		NULL,
	REPLACE (message_to, ',', '@cisco.com,') || '@cisco.com'
),
message_to)
	,
	'([a-zA-Z0-9@_\.-]*@?(-test)|(test-))+[a-zA-Z0-9@_\.-]*',
	'dev-alias@cisco.com'
)
to_address
from tab1;

Open in new window

0
 
thota198Author Commented:
i added a placeholder in a package
the placeholder i added is NON PRODUCTION BANNER
<html>
<font face="Tahoma" size=2>
<NON_PRODUCTION_BANNER> <br><br>
<REALUSER> <br><br>
Your approved non-standard Deal will expire in 5 days.<br><br>

You may visit <MDM DSA> at <a href="<URL>"><URL></a> if you need to extend or modify the Deal listed below.<br><br>
<table border="0" align="center" cellspacing=0 width="100%">
<tr width="100%"><td width="30%"><font face="Tahoma" size=2>Deal ID</font></td>                    <td width="70%"><font face="Tahoma" size=2>: <DEALID></font></td></tr>
<tr width="100%"><td width="30%"><font face="Tahoma" size=2>Opportunity Name</font></td>            <td width="70%"><font face="Tahoma" size=2>: <DEALNAME></font></td></tr>
<tr width="100%"><td width="30%"><font face="Tahoma" size=2>Account Name</font></td>                <td width="70%"><font face="Tahoma" size=2>: <ACCOUNTNAME></font></td></tr>
<tr width="100%"><td width="30%"><font face="Tahoma" size=2>Opportunity Owner</font></td>        <td width="70%"><font face="Tahoma" size=2>: <OPTYOWNER></font></td></tr>
<tr width="100%"><td width="30%"><font face="Tahoma" size=2>Theater</font></td>                    <td width="70%"><font face="Tahoma" size=2>: <THEATER></font></td></tr>
<tr width="100%"><td width="30%"><font face="Tahoma" size=2>Area</font></td>                        <td width="70%"><font face="Tahoma" size=2>: <AREA></font></td></tr>
<tr width="100%"><td width="30%"><font face="Tahoma" size=2>Deal Status</font></td>                <td width="70%"><font face="Tahoma" size=2>: <DEALSTATUS></font></td></tr>

</table><br>

placeholder value for NON PRODUCTION BANNER is
select name into v_db_name from v$database;
      IF v_db_name <> 'DMPROD' THEN
      io_email_replace_vals (179).fieldvalue :=
      '============= THE FOLLOWING SECTION WILL NOT APPEAR ON PRODUCTION ============= ' || CHR(10) ||
      'DB = ' ||v_db_name  || CHR(10) ||
      'TO = <to>'  || CHR(10) ||
      'CC = <cc>'  || CHR(10) ||
      'BCC= <bcc>' ;
     
      ELSE
      io_email_replace_vals (179).fieldvalue := '';
      END IF;
 
 This placeholder is in the message_format of cq_message_type table
 now i need to get this placeholder in a different package
tht is in the package given in my previous comments which sends email
0
 
OP_ZaharinCommented:
- is this a new question?
- did u get to solve the earlier question on discarding email with '%-test%' ?
0
 
thota198Author Commented:
yes it is a new question here but it is part of the requirement where i have to send a mail printing the  banner is placeholder incase of non production database and ignore or bypass the mails containing '-test' by sending it to a qa or dev alias.
so how to get the placeholder in a different package
0
 
slightwv (䄆 Netminder) Commented:
>>yes it is a new question here

New questions need to be asked as new questions.
0
 
OP_ZaharinCommented:
hi thota198,
- i would suggest you to close this question if it already solved, and open a new question regarding the other requirement.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now