8 Replies Latest reply on Aug 9, 2011 11:24 AM by _Phil

    Conditional email

    _Phil
      Hi,

      I am trying to use the OdiSendMail tool with a conditional piece of code to change the content of the email depending on whether there were errors in the previous step. I have written the following piece of code:

      <% if (odiRef.getPrevStepLog("ERROR_COUNT")=="0"){ %>

      OdiSendMail "-MAILHOST=mail.server.com" "-FROM=name@server.com" "-SUBJECT=Test Success Email" "-TO=me@server.com"
      <%=odiRef.getPrevStepLog( "STEP_NAME" )%> completed successfully.

      Start: <%=odiRef.getPrevStepLog( "BEGIN" )%>
      End: <%=odiRef.getPrevStepLog( "END" )%>
      Duration: <%=odiRef.getPrevStepLog( "DURATION" )%>s

      <% } else { %>

      OdiSendMail "-MAILHOST=mail.server.com" "-FROM=name@server.com" "-SUBJECT=Failure Email" "-TO=you@server.com"
      <%=odiRef.getPrevStepLog( "STEP_NAME" )%> completed with <%=odiRef.getPrevStepLog( "ERROR_COUNT" )%> errors.

      Start: <%=odiRef.getPrevStepLog( "BEGIN" )%>
      End: <%=odiRef.getPrevStepLog( "END" )%>
      Duration: <%=odiRef.getPrevStepLog( "DURATION" )%>s

      <%}%>


      When I execute this, it appears to be falling into the ELSE category every time, regardless of whether there were errors or not. Does anyone know if what I am trying to do is possible, and if so, is there something wrong with my code above?

      Thanks.

      Edited by: _Phil on Aug 1, 2011 5:10 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
        • 1. Re: Conditional email
          actdi
          Try using this instead:
          <% if (odiRef.getPrevStepLog("ERROR_COUNT").equals("0")){ %>                                                                                                                                                                           
          • 2. Re: Conditional email
            _Phil
            Hi, and thanks for the reply. I tried that syntax previously but it behaved in the same way.
            • 3. Re: Conditional email
              Guru Sankar
              Hi,

              You didnt mentioned what TECHNOLOGY u have used in the procedure coding?

              Thanks,
              Guru
              • 4. Re: Conditional email
                _Phil
                Hi Guru,

                That's because I'm not using a procedure. As I mentioned, I'm using the OdiSendMail tool within an ODI package. I'm attempting to use the code in the 'Command' tab.

                I am going to try using a procedure, but I need to set up my logical schemas for 'In Memory' first. I haven't had time to make any further progress on this, but will update when I do.

                _Phil                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                • 5. Re: Conditional email
                  Guru Sankar
                  Hi Phil,

                  Try the below,

                  Add a new ODI procedure Conditional Mail and add a step, let me say ODISendMail,

                  Tech : Sunopsis API

                  Command on target:

                  <? if ("0".equals( odiRef.getPrevStepLog( "ERROR_COUNT" ) )) { ?>

                  OdiSendMail "-MAILHOST=mail.server.com" "-FROM=name@server.com" "-SUBJECT=Test Success Email" "-TO=me@server.com"
                  <%=odiRef.getPrevStepLog( "STEP_NAME" )%> completed successfully.

                  Start: <%=odiRef.getPrevStepLog( "BEGIN" )%>
                  End: <%=odiRef.getPrevStepLog( "END" )%>
                  Duration: <%=odiRef.getPrevStepLog( "DURATION" )%>s

                  <? } else { ?>

                  OdiSendMail "-MAILHOST=mail.server.com" "-FROM=name@server.com" "-SUBJECT=Failure Email" "-TO=you@server.com"
                  <%=odiRef.getPrevStepLog( "STEP_NAME" )%> completed with <%=odiRef.getPrevStepLog( "ERROR_COUNT" )%> errors.

                  Start: <%=odiRef.getPrevStepLog( "BEGIN" )%>
                  End: <%=odiRef.getPrevStepLog( "END" )%>
                  Duration: <%=odiRef.getPrevStepLog( "DURATION" )%>s

                  <? }; ?>

                  Thats it.

                  Try this and let me know how it goes.

                  P.S: You can also plug this procedure at any package wherever u want to do conditional mail sending.

                  Thanks,
                  Guru
                  • 6. Re: Conditional email
                    _Phil
                    Thanks for the responses Guru. OK, so I tried the procedure this morning but I'm getting the following error. It looks like the code isn't treating each OdiSendMail statement separately within the IF/ELSE block but rather merging them together:

                    ODI-1228: Task email_test (Procedure) fails on the target SUNOPSIS_ENGINE connection SUNOPSIS_MEMORY_ENGINE.
                    Caused By: java.sql.SQLException: Unexpected token: ODISENDMAIL in statement [OdiSendMail]
                         at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
                         at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
                         at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java:665)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeUpdate(SnpSessTaskSql.java:3218)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java:1785)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2805)
                         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
                         at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
                         at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:322)
                         at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:246)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:237)
                         at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:794)
                         at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
                         at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
                         at java.lang.Thread.run(Thread.java:662)
                    • 7. Re: Conditional email
                      Guru Sankar
                      Hi Phil,

                      My bad, i didnt know u are using ODI 11g. This example is relevant for ODI 10g.

                      So u need to select Technology as ODI Tools for the procedure everything remains as is.

                      I tried this in my local and its successful. Try this and let me know.

                      Thanks,
                      Guru
                      • 8. Re: Conditional email
                        _Phil
                        Now we're in business. Thanks for your help.

                        Just for completeness, the above code also works correctly from the OdiSendMail tool which is actually more useful to me in my scenario.

                        I'm sure we'll speak again, Guru!

                        Edited by: _Phil on Aug 9, 2011 12:23 PM