9 Replies Latest reply: Oct 8, 2013 12:29 AM by LPS RSS

    interview question

    874273

      Hi Masters,

       

      Today I had a technical discussion with my client. They asked me one question.

       

      Q. We have a huge procedure which contains almost 5000 lines. if we execute that one, it shows error. How to find out which line is getting error?

       

      I said .. with dbms_errorlog table .

       

      But they said if any simple way is there?

       

      Please advise.

      SA

        • 1. Re: interview question
          BluShadow

          Answer: It depends.

           

          How is the procedure executed?

          What exception handling is included, if any?

           

          Simplest way is to remove any exception handlers and re-execute the procedure and Oracle will tell you which line the exception occurred on.

          • 2. Re: interview question
            SomeoneElse

            Probably this one:

             

            when others

               then

               dbms_output.put_line('error happened');

               raise;

            • 3. Re: interview question
              Frank Kulash

              Hi,

               

              One way is to read the error message.  (If the procedure is poorly written, this may only identify some line in the EXCEPTION section, not where the error actually occurred.)

               

              Why is it necesary to have a procedure with 5000 lines of code?  It might be better programming to write smaller modules.  Identifying exactly where errors occur would be only one of the benefits.

               

              Sometimes interviewers choose questions that hint at bad programming practices, to test if an alarm goes off in your brain when you notice something wrong, and if you have the courage to mention it.

              • 4. Re: interview question
                874273

                Hi

                 

                when others

                   then

                   dbms_output.put_line('error happened');

                   raise;

                If I give raise what happens? Please explain ..!

                 


                • 5. Re: interview question
                  BluShadow

                  Not sure why SomeoneElse gave that answer 

                   

                  That would only report the line number where the "raise" was issued, not the line where the actual error occurred in the code.

                  • 6. Re: interview question
                    SomeoneElse

                    > Not sure why SomeoneElse gave that answer

                     

                    Partly to be facetious, partly because I see this *all* the time.

                     

                    Chasing down an error line number only to find that it's the "raise" of a completely useless exception handler.


                    • 7. Re: interview question
                      rp0428
                      Today I had a technical discussion with my client. They asked me one question.

                       

                      Q. We have a huge procedure which contains almost 5000 lines. if we execute that one, it shows error. How to find out which line is getting error?

                       

                      I said .. with dbms_errorlog table .

                       

                      But they said if any simple way is there?

                      You haven't provided nearly enough information to even know if it is possible.

                       

                      One of the very first steps in ANY discussion is to make sure you agree on the terms that are being used.

                       

                      1. What does 'it shows error' mean? What 'error' are you talking about? Is that 'error' an Oracle exception? Or is it something that the user-written code spits out?

                       

                      2. What does 'execute' mean? How is the procedure executed? By a different procedure? By a simple sql*plus session? From a Java application? Does the procedure take parameters? What are they? Etc., etc, etc.

                       

                      3. what does 'simple way' mean? Provide an example.

                       

                      As others are implying it is easy to write code that makes it impossible to know what actual error is occuring or where it is occuring. People do that all the time by using 'WHEN OTHERS' and not including proper exception handling.

                      • 8. Re: interview question
                        Ramin Hashimzadeh

                        Also you can get a line number under exception block. See thread

                        https://forums.oracle.com/thread/2587032

                         

                        ----

                        Ramin Hashimzade