4 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    Data on some transactions and not on others

    mcfallm _

      I am trying to capture data from an error log in an Oracle program.  Some of the transactions show that the addition was a success and others give error messages.  These messages may or may not have multiple errors.  I cannot figure out how to trap the lines to give me those successful transactions and those that have errors.  The error messages are appearing on the table on the successful transactions.  The asset number is the field I want to select first and then capture any messages after that.  These fields are all on a separate line and follow one right after another.  Here is a sample of what the report looks like.

       

       

      Book: VMC TAX FED                                                              

      Request ID: 296880                                                             

      Asset number C30E30399                                                         

      Transaction header id 1                                                        

      Addition - Success                                                             

      Asset number C30E30409                                                         

      Transaction header id 3                                                        

      Addition - Success                                                             

      Asset number C30E30410                                                         

      Transaction header id 7                                                        

      Addition - Success                                                             

      Asset number C30E10160                                                         

      Transaction header id 37                                                       

      Cache: Error retrieving row from FA_METHODS table                              

      Depreciation Method: 200DB                                                     

      Life in Months: 468                                                                               

      Cause: You have a database problem.                                            

      Action: Contact your system administrator.                                                                               

      ORA-01403: no data found                                                       

      Error: function fa_cache_pkg.fazccmt returned failure                          

      ORA-0000: normal, successful completion                                        

      Error: function fa_asset_val_pvt.validate_jp250db returned failure             

      Error: function fa_asset_val_pvt.validate returned failure                     

      Error: function fa_addition_pub.do_addition returned failure                   

      Mass Copy failed on this asset and transaction                                 

      Error: function fa_masscp_pkg.mcp_addition returned failure                    

      Error: function fa_masscp_pkg.do_mass_copy returned failure                    

      Asset number C30E1016001                                                       

      Transaction header id 39                                                       

      Cache: Error retrieving row from FA_METHODS table                              

      Depreciation Method: 200DB                                                     

      Life in Months: 468                                                                               

      Cause: You have a database problem.                                            

      Action: Contact your system administrator.                                                                               

      ORA-01403: no data found                                                       

      Error: function fa_cache_pkg.fazccmt returned failure                          

      ORA-0000: normal, successful completion                                        

      Error: function fa_asset_val_pvt.validate_jp250db returned failure             

      Error: function fa_asset_val_pvt.validate returned failure                     

      Error: function fa_addition_pub.do_addition returned failure                   

      Mass Copy failed on this asset and transaction                                 

      Error: function fa_masscp_pkg.mcp_addition returned failure                    

      Error: function fa_masscp_pkg.do_mass_copy returned failure                    

      Asset number C30B3307601                                                       

      Transaction header id 41                                                       

      Cache: Error retrieving row from FA_METHODS table                              

      Depreciation Method: 200DB                                                     

      Life in Months: 468                                                                               

      Cause: You have a database problem.                                            

      Action: Contact your system administrator.

       

      Can anyone possibly help me with this?

        • Data on some transactions and not on others
          Grant Perkins

          Hi mcfallm and welcome to the forum.

           

          Not being an Oracle user let alone a reader of the log files I would find it useful if you could somehow identify the different record blocks (lines that relate to each other) in the sample.

           

          Are you lookinig for a single extraction that can identify both success and failure reports or could they each be obtained with separate models? Now sure what is possible yet but it's the sort of typical second question we may as well ask now!

           

           

          Grant

            • Data on some transactions and not on others
              mcfallm _

              The record block will begin with the line that has the asset number on it.  It will continue until the next group begins with asset number.  In those records with errors, I don't need all of those lines but I do want to get those lines that begin with the word "Error".  There will be several instances of lines that begin with "Error" within the same record and I would like to list each of them.

               

              I can do a separate extraction of the "Addition Success" assets and that would weed out some of the problem.  It doesn't solve the problem of attaching error messages to a particular asset number when the number of messages varies with many of the transactions.

               

              Thank you for responding.

               

              Missy

                • Data on some transactions and not on others
                  Olly Bond

                  Hello Missy,

                   

                  Have you tried defining your Detail template so that it selects the lines with "Error" in, and defining a memo field with the "none of the above" as the "end field on" value in the advanced field properties?

                   

                  Then define an Append template that traps the Asset value. That should give you something to start with. If you need to record the Transaction or ORA record above each Error, it might get slightly trickier.

                   

                  There's a three pass fix that comes to mind using Page & Line to glue the data back together, but I think I'm thinking in v9. Your profile says v10, so it's simple. Two more append templates, one for ORA and one for Transaction, which are set to stop on the appearance of the other.

                   

                  HTH

                   

                  Olly

                  • Data on some transactions and not on others
                    Grant Perkins

                    The record block will begin with the line that has the asset number on it. It will continue until the next group begins with asset number. /quote

                     

                    Hi Missy,

                     

                    If you are still searching for a solution for this here is another possible approach. Whether it will be of any use to you will depend on what you need to do with the error lines (and any others) after extraction.

                     

                    If you trap using a 2 line sample (Asset number and Transaction Header lines always seem to go in pairs) using Asset Number you can take the asset number from the first line and, assuming you don't specifically need Transaction Header, simply paint a wide line field across the entire second line and make it a text line (Memo Type probably sensible). In the field Advanced Properties make it 'End On' something suitable - likely to be 'None of the above'.

                     

                    Now you should have all the text in your table in a single field and we can 'slice and dice' that field for specific lines using the TEXTLINE() function and a suitable numbero of calculated fields, most of which will be much the same and so creation will mean siimply copy one field and make a small edit or two. The work of minutes.

                     

                    There are two modes to the Textline function - line number (as originally present in the block of lines in the report) and Search.

                     

                    Search would be great EXCEPT that for ERROR it will only find the first occurrence. It is likely that the error message will have some sort of pattern as to which lines they might appear on and identifying that will cut down the effort required.

                     

                    Basically you can use the Search mode for amy lines you may need way a unique search string exists only once in the text block. If, like 'error', it exists more often then you need to do a compare. something like;

                     

                    IF(left((Textline(,3)),5)="error",(TextLine([TextBlockField,3)),"")

                     

                    Which, if I got the syntax and parentheses right should interpret as;

                     

                    If line 3 of the text block starts with the string "error" then put the text on that line into this field otherwise leave it blank.

                     

                    You can of course combine this with quite a few of the other text manipulation Functions to get the results you need.

                     

                    This is probably not the complete solution but may offer another approach that gets you close.

                     

                     

                    HTH.

                     

                     

                    Grant