1 2 Previous Next 15 Replies Latest reply on Aug 10, 2018 9:29 AM by Ankit Gohil

    Informatica Stored procedure

    Ankit Gohil Active Member

      Hello All,

       

      I am using connected stored procedure in my mapping.This stored procedure sends a mail after It has loaded the datamart tables.The code for sending the email is written in db procedure only.There is no email fired from Informatica for stored proc.Now the issue is, whenever this mapping is run It sends 2 email messages.Basically this procedure takes 10 mins to complete.but it sends 1 mail at 6th min and one at 10th minute when the proc status is completed in Informatica. Now I don't understand why it is sending 2 email message which has exactly the same email body.I have gone through entire mapping but couldn't understand the issue.The procedure is called only one.Its not called again in pre post session.

       

      Can someone please suggest,where I am going wrong.

       

      Regards,

      Ankit

        • 1. Re: Informatica Stored procedure
          Srini Veeravalli Guru

          I would suggest to go with Java transformation for emailing.

          • 2. Re: Informatica Stored procedure
            Srini Veeravalli Guru

            You just need add few lines and rest is default code from Java Transformation.

             

            try

                {

                // Get system properties

                Properties props = System.getProperties();

             

                // Setup mail server

                props.put("mail.smtp.host", SMTPSERVER);

             

                // Get session

                Session session = Session.getDefaultInstance(props, null);

             

                // Define message

                MimeMessage message = new MimeMessage(session);

             

                // Set the from address

                message.setFrom(new InternetAddress(FROM));

             

                // Set the to address

                message.addRecipient(Message.RecipientType.TO,

                  new InternetAddress(TO));

             

                // Set the subject

                message.setSubject(SUBJECT);

            BodyPart messageBodyPart = new MimeBodyPart();

             

             

            messageBodyPart.setText("If the attached file has any IsoCodes listed, those currencies were not updated in Sales Force. Note: If the file contains NO records, all applicable currencies are updated.");

            Multipart multipart = new MimeMultipart();

            multipart.addBodyPart(messageBodyPart);

             

            messageBodyPart = new MimeBodyPart();

            DataSource source = new FileDataSource(FILE);

            messageBodyPart.setDataHandler(new DataHandler(source));

             

             

            messageBodyPart.setFileName(FILE);

            multipart.addBodyPart(messageBodyPart);

             

             

            message.setContent(multipart);

                // Set the content

               // message.setText(Body);

             

                // Send message

                Transport.send(message);

                        STATUS= "True";

                } catch(Exception e)

                {

                logError(e.getMessage());

                        STATUS= "False";

                }

            • 3. Re: Informatica Stored procedure
              Ankit Gohil Active Member

              Hi Srini,

               

              Thanks for your reply.However sending an email is done by procedure itself and as per the procedure the email will be fired only when the loading is completed.But I am getting an email twice.

               

              Regards,

              Ankit

              • 4. Re: Informatica Stored procedure
                Srini Veeravalli Guru

                Looks like issue is with sp I would suggest to test that outside of Informatica first.

                 

                I dont want to use SP when I'm using specifically Informatica! Its great tool just need to know how to use it. if any other ETL tool then I may based on case. This is my opinion.

                • 5. Re: Informatica Stored procedure
                  Nico Heinze Guru

                  Ankit, are you absolutely sure that:

                  1. the SP is called nowhere else?

                  2. there is no email task, no pre- or post-session email, or whatever executed which may send the same mail twice?

                   

                  In any case I would work with your network administrators; they should check the email configuration of the email server over which this email is distributed.

                   

                  Regards,

                  Nico

                  • 6. Re: Informatica Stored procedure
                    Ankit Gohil Active Member

                    Hi Srini,

                     

                    When I execute the procedure directly from the database,the mail gets fired only once.So I believe,the procedure has no issue.

                     

                     

                    Regards,

                    Ankit

                    • 7. Re: Informatica Stored procedure
                      Ankit Gohil Active Member

                      Hi Nico,

                       

                      The SP is called no where else.When I execute the procedure directly from the database,the mail gets fired only once.So I believe,the procedure has no issue.

                       

                      Also we have other mappings where we have used email tasks.There the mails gets fired only once.There are no multiple mails.So I think email configuration on the server is correct.

                       

                      Regards,

                      Ankit

                      • 8. Re: Informatica Stored procedure
                        Nico Heinze Guru

                        But it might well be (depending on the configuration of the mail server) that this Stored Procedure when invoked via PowerCenter Stored Procedure transformation has some different properties for sending emails than when invoked in the DB.

                         

                        Believe it or not, I have a similar problem with some antivirus vendor. For months now I get emails every now and then from them (after having re-registered for our company-wide anti-virus software). No problems. For two weeks now I see each email being sent to me twice. Without any apparent reason.

                         

                        Email servers have bugs, too. So this might be an email server bug.

                         

                        Again, I can only suggest that you ask your network administration (in particular those guys responsible for the email server) to double-check the email configuration and to run debug logs so that they may be able to verify whether it's a server setup or whatever.

                         

                        Regards,

                        Nico

                        • 9. Re: Informatica Stored procedure
                          user140848 Guru

                          Hi Ankit,

                           

                          If you want the stored procedure to run once only, and you have more than one data record going through, you should not use a connected stored procedure. From the help files, describing SP modes (emphasis added):

                           

                          Normal. The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. This is useful for calling the stored procedure for each row of data that passes through the mapping, such as running a calculation against an input port. Connected stored procedures run only in normal mode.

                           

                          I suspect the issue is simply that you are sending two rows to it, so it runs twice. Either make sure you only send it one row, or use it unconnected with the stored procedure type set to Target Post-Load.

                           

                          Regards,

                          Sacha Roscoe

                          1 of 1 people found this helpful
                          • 10. Re: Informatica Stored procedure
                            Ankit Gohil Active Member

                            Hi Sacha,

                             

                            Thanks for the reply.Your suggestion may resolve my issue.One question: My procedure has 2 parameters and both of them are set as IN OUT parameter. Eg.

                             

                            Var1 IN OUT

                            Var2 IN OUT

                             

                            Do Informatica support these type of parameters?I googled a bit and It says It doesn't support.Even when I try to validate the expression I get "Invalid function reference error". @user140848 

                            @Nico Heinze

                             

                            Thanks and regards,

                            Ankit

                            • 11. Re: Informatica Stored procedure
                              user140848 Guru

                              Hi Ankit,

                               

                              There doesn't seem to be a restriction on this. From the help files:

                               

                              Parameter Types

                              There are three possible parameter types in stored procedures:

                              • IN. Defines the parameter something that must be passed to the stored procedure.
                              • OUT. Defines the parameter as a returned value from the stored procedure.
                              • INOUT. Defines the parameter as both input and output. Only Oracle supports this parameter type.

                               

                              Input/Output Port in Mapping

                              Since Oracle supports the INOUT parameter type, a port in a Stored Procedure transformation can act as both an input and output port for the same stored procedure parameter. Other databases should not have both the input and output check boxes selected for a port.

                               

                               

                              What expression is it that you are trying to validate, and how is your SP transformation configured?

                               

                              I had another thought - since you only want the stored procedure to run once, have you considered not using a Stored Procedure transformation at all, and simply putting it into post-load SQL on your target? (The feasibility of this depends on whether you need to compute the input parameters as part of your mapping, I suppose.)

                               

                              Regards, Sacha

                              • 12. Re: Informatica Stored procedure
                                Yonghe Wang New Member

                                Great explanation, the SP can be connected or not-connected in mapping, also clarify how Informatica deal with the data.

                                Could not agree more.

                                • 13. Re: Informatica Stored procedure
                                  Ankit Gohil Active Member

                                  Hi Sacha,

                                   

                                  I have configured the unconnected SP with Target post load as per your suggestion.But when I run the session,It fails with below error.

                                   

                                  *****************************************************************************************************

                                  ERROR   7/26/2018 4:28:30 PM    NODE_PWDEV_PWNLD1022v03 POST-SESS       TM_6159 An error occurred executing the stored procedure.

                                  TM_6159 [4294965496] [ 
                                  ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
                                  ORA-06512: at "GIBIX.PKG_GSGIB_EQUIPMENT_DM", line 1466 
                                  ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
                                  ORA-06512: at "GIBIX.PKG_GSGIB_EQUIPMENT_DM", line 75 
                                  ORA-06512: at line 2

                                  **************************************************************************************************

                                   

                                  Strange thing is,when I run the same proc from DB,It runs fine.

                                   

                                  Thanks and regards,

                                  Ankit

                                  • 14. Re: Informatica Stored procedure
                                    user140848 Guru

                                    Hi Ankit,

                                     

                                    This means that the size of the stored procedure parameter ports you're using in Informatica is too small; it's trying to output a string that's too long for Informatica to accept. You need to identify the longest possible output string for each out (or inout) parameter and make sure you allocate enough space for it in your SP's output ports. (Also, if you're using a multi-byte character encoding, you have to allow for that or make sure your session semantics are set to CHAR rather than BYTE. I can help you with that if it's an issue.)

                                     

                                    Regards, Sacha

                                    1 2 Previous Next