5 Replies Latest reply on Feb 5, 2020 2:01 PM by mmoayed

    Email is not sent when Source is SQL and Target is Odisendmail Command

    mmoayed

      Hello All,


      I am trying to return Error_message and session ID from SNP_LPI_STEP_LOG table in source  and use them  target using procedure in ODI. SQL genarted succefully,but is not used in Target. The following are snapshot of what I did .

      Load Plan:

       

      Procedure:

      Target Command:


      Source Command:

      Generated SQL is executed :


      When i delete SQl from source command, mail server sends the Email without any problem.

      What do you think what is the problem here?  Is it because the source is using ODI Tool and the target is using Oracle?
      I tried in command to use

      #err_mesg

      #see_no

      instead of using

      :err_mesg

      :see_no

       

      but it is not working too

      BR,

      Mohammed


        • 1. Re: Email is not sent when Source is SQL and Target is Odisendmail Command
          Rodrigo Radtke Souza

          Probably your load plan is not in 'E' status when you run the procedure. If the procedure is inside the load plan that you want to check for 'E', then the status is probably 'R' at the moment (Running). Since your session is not 'E' yet, the source tab does not return rows and does not trigger the email. The sql works outside because the Load plan already completed and then it is set to 'E'. When you remove the source SQL it also works because it is not using the source tab at all.

           

          Thanks

          • 2. Re: Email is not sent when Source is SQL and Target is Odisendmail Command
            mmoayed

            Thank you.

            Where I can change this Status?

            BR,

            • 3. Re: Email is not sent when Source is SQL and Target is Odisendmail Command
              mmoayed

              The load plan after execution is generating two sessions one for the Load  load plan which 'E' and one for a package that contains the procedure and where email should be sent.

              What I did is as following:

              1- create a Package that contains steps and final steps is a procedure where OdisendMail is used.

               

              2-  create a load Plan

               

              3-  insert Step that will create an error for Testing.

              4- Create an exception where scenario package in step one will be running.

              5- the following are sessions after running load Plan

               

              • 4. Re: Email is not sent when Source is SQL and Target is Odisendmail Command
                Rodrigo Radtke Souza

                I do something very similar in my projects. Below is the query that I generally use. Please analyze it and see if it works in your case:

                 

                WITH STEPS AS (

                SELECT '<%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> ' || S.I_LP_INST  || ' has finished with errors' AS SUBJECT

                     , IL.SESS_NO

                  FROM <%=odiRef.getSchemaName( "D" )%>.SNP_LPI_STEP S

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_LPI_EXC_LOG IL

                        ON S.I_LP_INST = IL.I_LP_INST

                        AND S.I_LP_STEP = IL.I_LP_STEP_EXCEPT

                        AND IL.NB_RUN = '<%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>'

                WHERE S.I_LP_INST = (SELECT I_LP_INST FROM <%=odiRef.getSchemaName( "D" )%>.SNP_LP_INST WHERE GLOBAL_ID = '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>')

                   AND LP_STEP_NAME = '<%=odiRef.getSession("SESS_NAME")%>'

                UNION

                SELECT '<%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> ' || S.I_LP_INST  || ' has finished with success but has internal issues' AS SUBJECT

                     , IL.SESS_NO

                  FROM <%=odiRef.getSchemaName( "D" )%>.SNP_LPI_STEP S

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_LPI_STEP_LOG IL

                        ON S.I_LP_INST = IL.I_LP_INST

                        AND S.I_LP_STEP = IL.I_LP_STEP       

                        AND IL.NB_RUN = '<%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>'

                WHERE S.I_LP_INST = (SELECT I_LP_INST FROM <%=odiRef.getSchemaName( "D" )%>.SNP_LP_INST WHERE GLOBAL_ID = '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>')

                   AND LP_STEP_NAME = '<%=odiRef.getSession("SESS_NAME")%>'

                )

                , SUBJECT AS (

                SELECT SUBJECT

                    FROM STEPS ST

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION S

                        ON ST.SESS_NO = S.SESS_NO  

                WHERE NVL((SELECT TO_CHAR(PARENT_SESS_NO) FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION WHERE SESS_NO = '<%=odiRef.getSession("SESS_NO")%>'), 'DUMMY') = 'DUMMY'       

                UNION

                SELECT S.SESS_NAME || ' ' || S.SESS_NO || ' has errors' AS SUBJECT

                  FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION S

                WHERE S.SESS_NO = (SELECT PARENT_SESS_NO FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION WHERE SESS_NO = '<%=odiRef.getSession("SESS_NO")%>')       

                )

                , LP_ERROR AS (

                SELECT I.LOAD_PLAN_NAME AS STEP_NAME

                     , I.I_LP_INST

                     , TO_CHAR(R.END_DATE, 'MM-DD-YYYY HH24:MI:SS') ERROR_TIME

                     , TRIM(REPLACE(REPLACE(REPLACE(SUBSTR(TO_CHAR(DBMS_LOB.SUBSTR(R.ERROR_MESSAGE,4000,1)), 1, DECODE(INSTR(TO_CHAR(DBMS_LOB.SUBSTR(R.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '), 0, LENGTH(TO_CHAR(DBMS_LOB.SUBSTR(R.ERROR_MESSAGE,4000,1))), INSTR(TO_CHAR(DBMS_LOB.SUBSTR(R.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '))), CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')) AS ERROR_MESSAGE

                  FROM <%=odiRef.getSchemaName( "D" )%>.SNP_LPI_RUN R

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_LP_INST I

                        ON R.I_LP_INST = I.I_LP_INST

                        AND R.NB_RUN = '<%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>'

                        AND I.GLOBAL_ID = '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>'

                )

                , LP_STEPS AS (

                SELECT SLS.LP_STEP_NAME AS STEP_NAME

                     , SLSL.SESS_NO

                     , TO_CHAR(SLSL.END_DATE, 'MM-DD-YYYY HH24:MI:SS') ERROR_TIME

                     , TRIM(REPLACE(REPLACE(REPLACE(SUBSTR(TO_CHAR(DBMS_LOB.SUBSTR(SLSL.ERROR_MESSAGE,4000,1)), 1, DECODE(INSTR(TO_CHAR(DBMS_LOB.SUBSTR(SLSL.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '), 0, LENGTH(TO_CHAR(DBMS_LOB.SUBSTR(SLSL.ERROR_MESSAGE,4000,1))), INSTR(TO_CHAR(DBMS_LOB.SUBSTR(SLSL.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '))), CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')) AS ERROR_MESSAGE

                  FROM <%=odiRef.getSchemaName( "D" )%>.SNP_LP_INST LI

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_LPI_STEP_LOG SLSL

                      ON LI.I_LP_INST = SLSL.I_LP_INST

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_LPI_STEP SLS

                        ON SLSL.I_LP_INST = SLS.I_LP_INST

                        AND SLSL.I_LP_STEP = SLS.I_LP_STEP

                        AND SLS.IND_ENABLED = 1

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION SS --CHANGE TO LEFT JOIN TO SHOW LP STEPS THAT HAVE NO SESSION ASSOCIATED

                        ON SLSL.SESS_NO = SS.SESS_NO

                WHERE LI.GLOBAL_ID =  '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>'

                   AND SLSL.NB_RUN = '<%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>'

                   AND NVL((SELECT TO_CHAR(PARENT_SESS_NO) FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION WHERE SESS_NO = '<%=odiRef.getSession("SESS_NO")%>'), 'DUMMY') = 'DUMMY'

                )

                , LP_SESSION AS (

                SELECT 'Load Plan' AS SRC

                      , S.SESS_NAME AS STEP_NAME

                      , S.SESS_NO

                      , SUBSTR(SYS_CONNECT_BY_PATH(S.SESS_NO, '-'), 2) AS SESS_NO_PATH

                  FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION S

                WHERE NVL( '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>', 'DUMMY') <> 'DUMMY'

                CONNECT BY PRIOR S.SESS_NO = S.PARENT_SESS_NO

                  START WITH S.SESS_NO IN (SELECT SESS_NO FROM LP_STEPS)

                , INDIVIDUAL_SESSIONS AS (

                SELECT 'Session' AS SRC

                      , S.SESS_NAME AS STEP_NAME

                      , S.SESS_NO

                      , SUBSTR(SYS_CONNECT_BY_PATH(S.SESS_NO, '-'), 2) AS SESS_NO_PATH

                   FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION S

                CONNECT BY PRIOR S.SESS_NO = S.PARENT_SESS_NO

                  START WITH S.SESS_NO = (SELECT PARENT_SESS_NO FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION WHERE SESS_NO = '<%=odiRef.getSession("SESS_NO")%>')

                UNION

                SELECT 'Session' as src

                      , S.SESS_NAME AS STEP_NAME

                      , S.SESS_NO

                      , SUBSTR(SYS_CONNECT_BY_PATH(S.SESS_NO, '-'), 2) AS SESS_NO_PATH

                   FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION S

                CONNECT BY PRIOR S.PARENT_SESS_NO = S.SESS_NO

                  START WITH S.SESS_NO = (SELECT PARENT_SESS_NO FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION WHERE SESS_NO = '<%=odiRef.getSession("SESS_NO")%>')

                )

                , TASKS AS (

                SELECT SRC

                     , A.SESS_NO

                     , A.SESS_NO_PATH

                     , A.STEP_NAME

                     , DENSE_RANK() OVER (PARTITION BY S.SESS_NO, S.SB_NO ORDER BY S.SESS_NO, S.SB_NO, SL.NNO DESC, SBT.SCEN_TASK_NO DESC) AS ORDER_

                     , TO_CHAR(L.TASK_END, 'MM-DD-YYYY HH24:MI:SS') AS ERROR_TIME

                     , NVL(TRIM(REPLACE(REPLACE(REPLACE(SUBSTR(TO_CHAR(DBMS_LOB.SUBSTR(L.ERROR_MESSAGE,4000,1)), 1, DECODE(INSTR(TO_CHAR(DBMS_LOB.SUBSTR(L.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '), 0, LENGTH(TO_CHAR(DBMS_LOB.SUBSTR(L.ERROR_MESSAGE,4000,1))), INSTR(TO_CHAR(DBMS_LOB.SUBSTR(L.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '))), CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')), TRIM(REPLACE(REPLACE(REPLACE(SUBSTR(TO_CHAR(DBMS_LOB.SUBSTR(S.ERROR_MESSAGE,4000,1)), 1, DECODE(INSTR(TO_CHAR(DBMS_LOB.SUBSTR(S.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '), 0, LENGTH(TO_CHAR(DBMS_LOB.SUBSTR(S.ERROR_MESSAGE,4000,1))), INSTR(TO_CHAR(DBMS_LOB.SUBSTR(S.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '))), CHR(10), ' '), CHR(13), ' '), CHR(9), ' '))) AS ERROR_MESSAGE

                  FROM LP_SESSION A

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION S

                        ON A.SESS_NO = S.SESS_NO

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_STEP_LOG SL

                        ON S.SESS_NO = SL.SESS_NO

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SB_STEP SBS

                        ON S.SB_NO = SBS.SB_NO

                        AND SL.NNO = SBS.NNO

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SB_TASK SBT

                        ON 1 =1

                        AND S.SB_NO = SBT.SB_NO

                        AND SBS.NNO = SBT.NNO

                        AND SL.NNO = SL.NNO

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SESS_TASK_LOG L

                        ON S.SESS_NO = L.SESS_NO

                        AND S.SB_NO = L.SB_NO

                        AND SL.NNO = L.NNO

                        AND SL.NB_RUN = L.NB_RUN

                        AND L.SCEN_TASK_NO = SBT.SCEN_TASK_NO

                        AND L.TASK_STATUS = 'E'

                WHERE 1=1

                  AND L.ERROR_MESSAGE IS NOT NULL

                  AND (L.ERROR_MESSAGE LIKE '%stopped (%) by user%SUPERVISOR%' OR L.ERROR_MESSAGE NOT LIKE '%stopped (%) by user%')

                  AND L.ERROR_MESSAGE NOT LIKE '%ODI-99999:%' 

                UNION ALL

                SELECT SRC

                     , A.SESS_NO

                     , A.SESS_NO_PATH

                     , A.STEP_NAME

                     , DENSE_RANK() OVER (PARTITION BY S.SESS_NO, S.SB_NO ORDER BY S.SESS_NO, S.SB_NO, SL.NNO DESC, SBT.SCEN_TASK_NO DESC) AS ORDER_

                     , TO_CHAR(L.TASK_END, 'MM-DD-YYYY HH24:MI:SS') AS ERROR_TIME

                     , NVL(TRIM(REPLACE(REPLACE(REPLACE(SUBSTR(TO_CHAR(DBMS_LOB.SUBSTR(L.ERROR_MESSAGE,4000,1)), 1, DECODE(INSTR(TO_CHAR(DBMS_LOB.SUBSTR(L.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '), 0, LENGTH(TO_CHAR(DBMS_LOB.SUBSTR(L.ERROR_MESSAGE,4000,1))), INSTR(TO_CHAR(DBMS_LOB.SUBSTR(L.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '))), CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')), TRIM(REPLACE(REPLACE(REPLACE(SUBSTR(TO_CHAR(DBMS_LOB.SUBSTR(S.ERROR_MESSAGE,4000,1)), 1, DECODE(INSTR(TO_CHAR(DBMS_LOB.SUBSTR(S.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '), 0, LENGTH(TO_CHAR(DBMS_LOB.SUBSTR(S.ERROR_MESSAGE,4000,1))), INSTR(TO_CHAR(DBMS_LOB.SUBSTR(S.ERROR_MESSAGE,4000,1)), CHR(9) || 'at '))), CHR(10), ' '), CHR(13), ' '), CHR(9), ' '))) AS ERROR_MESSAGE

                  FROM INDIVIDUAL_SESSIONS A

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION S

                        ON A.SESS_NO = S.SESS_NO

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_STEP_LOG SL

                        ON S.SESS_NO = SL.SESS_NO

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SB_STEP SBS

                        ON S.SB_NO = SBS.SB_NO

                        AND SL.NNO = SBS.NNO

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SB_TASK SBT

                        ON 1 =1

                        AND S.SB_NO = SBT.SB_NO

                        AND SBS.NNO = SBT.NNO

                        AND SL.NNO = SL.NNO

                    INNER JOIN <%=odiRef.getSchemaName( "D" )%>.SNP_SESS_TASK_LOG L

                        ON S.SESS_NO = L.SESS_NO

                        AND S.SB_NO = L.SB_NO

                        AND SL.NNO = L.NNO

                        AND SL.NB_RUN = L.NB_RUN

                        AND L.SCEN_TASK_NO = SBT.SCEN_TASK_NO

                        AND L.TASK_STATUS = 'E'

                WHERE 1=1

                  AND L.ERROR_MESSAGE IS NOT NULL

                  AND (L.ERROR_MESSAGE LIKE '%stopped (%) by user%SUPERVISOR%' OR L.ERROR_MESSAGE NOT LIKE '%stopped (%) by user%')

                  AND L.ERROR_MESSAGE NOT LIKE '%ODI-99999:%'

                )

                , ALL_ AS (

                SELECT 'Load Plan error' AS SRC

                     , TO_CHAR(I_LP_INST) AS SESS_NO

                     , NULL AS SESS_NO_PATH

                     , STEP_NAME

                     , ERROR_TIME

                     , SUBSTR(ERROR_MESSAGE, DECODE(INSTR(UPPER(ERROR_MESSAGE), 'ORA-'), 0, 1, INSTR(UPPER(ERROR_MESSAGE), 'ORA-', -1, 1))) AS ERROR_MESSAGE

                  FROM LP_ERROR

                WHERE ERROR_MESSAGE IS NOT NULL 

                UNION ALL

                SELECT DISTINCT

                       'Load Plan error' as SRC

                     , 'No ODI Session' AS SESS_NO

                     , NULL AS SESS_NO_PATH

                     , STEP_NAME

                     , ERROR_TIME

                     , SUBSTR(ERROR_MESSAGE, DECODE(INSTR(UPPER(ERROR_MESSAGE), 'ORA-'), 0, 1, INSTR(UPPER(ERROR_MESSAGE), 'ORA-', -1, 1))) AS ERROR_MESSAGE

                  FROM LP_STEPS

                WHERE SESS_NO IS NULL

                   AND ERROR_MESSAGE IS NOT NULL

                UNION ALL

                SELECT DISTINCT

                       SRC

                     , TO_CHAR(SESS_NO) AS SESS_NO

                     , SESS_NO_PATH || ': '

                     , STEP_NAME

                     , ERROR_TIME

                     , SUBSTR(ERROR_MESSAGE, DECODE(INSTR(UPPER(ERROR_MESSAGE), 'ORA-'), 0, 1, INSTR(UPPER(ERROR_MESSAGE), 'ORA-', -1, 1))) AS ERROR_MESSAGE

                  FROM TASKS

                WHERE ORDER_ = 1

                )

                SELECT SUBJECT

                     , LISTAGG(SESS_NO_PATH || STEP_NAME || ' - ' || ERROR_TIME || ' - ' || SUBSTR(ERROR_MESSAGE, DECODE(INSTR(ERROR_MESSAGE, 'ODI-', -1, 1), 0, 0, INSTR(ERROR_MESSAGE, 'ODI-', -1, 1))), CHR(10)) WITHIN GROUP(ORDER BY SESS_NO, STEP_NAME) AS ERROR_MESSAGE

                  FROM ALL_

                      , SUBJECT

                HAVING LISTAGG(SESS_NO_PATH || STEP_NAME || ' - ' || ERROR_TIME || ' - ' ||  SUBSTR(ERROR_MESSAGE, DECODE(INSTR(ERROR_MESSAGE, 'ODI-', -1, 1), 0, 0, INSTR(ERROR_MESSAGE, 'ODI-', -1, 1))), CHR(10)) WITHIN GROUP(ORDER BY SESS_NO, STEP_NAME) IS NOT NULL 

                GROUP BY SUBJECT

                UNION

                SELECT I.LOAD_PLAN_NAME || ' ' || I.I_LP_INST || ' has finished with success' AS SUBJECT

                     , NULL

                  FROM <%=odiRef.getSchemaName( "D" )%>.SNP_LP_INST I

                WHERE GLOBAL_ID = '<%=odiRef.getLoadPlanInstance("BATCH_GUID")%>'

                   AND NOT EXISTS (SELECT 1 FROM All_ WHERE ERROR_MESSAGE IS NOT NULL)

                   AND NVL((SELECT TO_CHAR(PARENT_SESS_NO) FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION WHERE SESS_NO = '<%=odiRef.getSession("SESS_NO")%>'), 'DUMMY') = 'DUMMY'

                UNION

                SELECT S.SESS_NAME   || ' ' || S.SESS_NO || ' has finished with success' AS SUBJECT

                     ,  NULL

                  FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION S

                WHERE S.SESS_NO = (SELECT PARENT_SESS_NO FROM <%=odiRef.getSchemaName( "D" )%>.SNP_SESSION WHERE SESS_NO = '<%=odiRef.getSession("SESS_NO")%>')

                   AND NOT EXISTS (SELECT 1 FROM All_ WHERE ERROR_MESSAGE IS NOT NULL)

                • 5. Re: Email is not sent when Source is SQL and Target is Odisendmail Command
                  mmoayed

                  Thank you for your reply.
                  Well, I just update the SQL, and it works. I just want to share with you what I did to resolve it.

                   

                  The problem was, No data was retrieved when executing the SQL.