10 Replies Latest reply on Sep 18, 2018 3:58 PM by rp0428

    How to display output of Dbms sentences at debugging time?

    Blue Bird

      Hi,

       

      On Oracle 11gR2 I'm debugging stored procedure in SQL Developer 18.2.x and I would like to see my added Dbms output sentences while I'm debugging. I got results in Debugging panel but after I finish debugging (blue colored output below). How to see this results in real time while I'm going over that portion of code. For example I have simple procedure:

       

      Create or Replace Procedure AddNumbers
      (
        par_Num1 Number,
        par_Num2 Number
      )
      Is
        var_Sum Number;
      Begin
        DBMS_OutPut.Put_Line('par_Num1: ' || par_Num1);
        DBMS_OutPut.Put_Line('par_Num2: ' || par_Num2);
      
      
        var_Sum:= par_Num1 + par_Num2;
        DBMS_OutPut.Put_Line('Sum is: ' || var_Sum);
        DBMS_OutPut.Put_Line('End of Procedure AddNumbers');
      End AddNumbers;
      

       

      And I see this at the end of debugging:

       

      I also run sentence Set ServerOutPut On; and left window (Dbms Output) is still empty. I know that this working in the past versions. I didn't use this for a while. Can someone tell me what I'm doing wrong or how to see this results while I'm going through code with F7 (Step Into)?

       

      BB

        • 1. Re: How to display output of Dbms sentences at debugging time?
          thatJeffSmith-Oracle

          that's how dbms_output works - you see it AFTER the fact

           

          if you want to see stuff while you debug, then add a watch to par_num1, and observe it as you step through the code.

          • 2. Re: How to display output of Dbms sentences at debugging time?
            Blue Bird

            Thank you for your answer. I was thinking that you can use this sentence also in this context like in other environments (e.g. like Debug.Writeline() in VS). Many times such line contain information from many variables and is easier to read that way, then paying attention to each individually. I move all this text now into a variable and add watch on to that.

             

            BB

            • 3. Re: How to display output of Dbms sentences at debugging time?

              In Oracle 11gR2 I'm debugging stored procedure in SQL Developer 18.2.x

              First - that is NOT the type of thing you should be doing to 'debug' code.

               

              Code should be properly instrumented to log needed info to a customs logging table.

               

              and I would like to see my added Dbms output sentences while I'm debugging. I got results in Debugging panel but after I finish debugging

              Correct - those DBMS_OUTPUT calls simply go into a memory buffer and will NOT be 'seen' at all unless a client app/code makes calls to get the buffer contents.

               

              Those calls can ONLY be made after the code finishes executing. Another reason why that is NOT the way to try to debug code.

               

              There are even more reasons why you shouldn't do that:

               

              1. Such calls use valuable memory and can, and have, caused memory issues

               

              2. If you try to remedy that by removing the calls before you deploy to production you are now altering the code that was tested. That is a security risk since someone could also alter other portions of the code.

               

              3. In a production, or even qa, environment there IS NO 'console display' for anyone to even see those messages.

               

              I agree it can be useful for debugging particular types of problems that can't be easily found in other ways but those use cases are few and far between. They generally involve sql code embedded in pl/sql rather than the typical use of pl/sql code to process data.

               

              For example a function that will be called from sql that must be high-performing will NOT generally be instrumented so that the instrumentation code won't affect the normal performance of the function. But any exception handling portion of the code WILL BE instrumented and not by using dbms_output.

               

              I suggest you take this opportunity to rewrite you code to instrument it properly and get rid of those statements. Then your problem goes away.

              1 person found this helpful
              • 4. Re: How to display output of Dbms sentences at debugging time?
                thatJeffSmith-Oracle

                The debugger isn't the right thing to use to debug code?

                • 5. Re: How to display output of Dbms sentences at debugging time?
                  Blue Bird

                  I agree with both of your concerns. Some of them are good for thinking. But I still think for beginner it's the fastest way to find a bug, why final result or output isn't as expected. And sometimes the only way to figure out where things went wrong. And you can display many values at once in one row and is not necessary to look for each separate variable in watch panel.

                  • 6. Re: How to display output of Dbms sentences at debugging time?

                    The debugger isn't the right thing to use to debug code?

                    We aren't talking about the debugger. We are talking about the use of DBMS_OUTPUT statements in PL/SQL code.

                    • 7. Re: How to display output of Dbms sentences at debugging time?
                      thatJeffSmith-Oracle

                      Sorry, I get you now.

                       

                      And I agree, but.

                       

                      DBMS Output will always win out because it's 'easy.'

                       

                      Keep up the good fight though.

                      • 8. Re: How to display output of Dbms sentences at debugging time?
                        Blue Bird

                        RP just one more Q: this topic has been already closed, but I would still ask you one more time about this to be sure if I understood this correct and follow good practice:

                        May I / can leave DBMS_OUTPUT active (not commented out) in final code or I have to comment them ALL out so it won't be active anywhere in the PL/SQL code?

                         

                        I'm asking this because even If I "translate" procedure / code using "Compile" or "Compile for Debug" command, DBMS_OUTPUT text is visible in the console (If I have used for example Set ServerOutPut On), so additional CPU cycles must be used for this purpose at compile time. In VS for example sentences Debug.Writeline() won't be compiled in Release version, nor visible in Output window, even are active in the code.

                         

                        • 10. Re: How to display output of Dbms sentences at debugging time?

                          RP just one more Q: this topic has been already closed, but I would still ask you one more time about this to be sure if I understood this correct and follow good practice:

                          The typical/expected use of DBMS_OUTPUT by experts is when prototyping or exploring new code, new DB features or new techniques.

                           

                          In other words it was never really meant to be used in actual code that was intended to be released.

                          May I / can leave DBMS_OUTPUT active (not commented out) in final code or I have to comment them ALL out so it won't be active anywhere in the PL/SQL code?

                          There are (rare) use cases where DBMS_OUTPUT is appropriately added to code that goes through the full development cycle from dev/test/qa/prod.

                           

                          But for these cases there are other, fairly major, things that are done as part of that:

                           

                          1. that dbms_output capability goes through the SAME dev/test/qa/prod documentation and development process that all other production code goes thru. That means developers do not just throw something in for their own use or to make development easier

                           

                          2. the ability to enable the actual use of the capability is tightly restricted so that it can NOT be enabled simply by using a tool like sql*plus. The enabling code is buried/controlled in a procedure/function whose access is restricted so that any 'enabling' will be intentional. That means its use is also restricted to functionality that is 'internal' (e.g. package private).

                           

                          3. for less stringent cases the dbms_output capability is embedded within 'conditional compilation' blocks so that it is available in dev/test environments but can be removed by Oracle when the code is compiled for production or performance test environments.

                          https://docs.oracle.com/database/121/LNPLS/fundamentals.htm#LNPLS294

                          Conditional Compilation

                          Conditional compilation lets you customize the functionality of a PL/SQL application without removing source text.

                          For example, you can:

                          • Use new features with the latest database release and disable them when running the application in an older database release.
                          • Activate debugging or tracing statements in the development environment and hide them when running the application at a production site.

                          . . .

                          Conditional compilation uses selection directives, which are similar to IF statements, to select source text for compilation. The condition in a selection directive usually includes an inquiry directive. Error directives raise user-defined errors. All conditional compilation directives are built from preprocessor control tokens and PL/SQL text.

                          Using conditional compilation properly takes some skill and training and can also complicate the testing process since there can be multiple code paths to test.

                           

                          In my experience it is NOT commonly used. If it is not used properly (i.e. with proper documentation, code review and testing) you run the risk of the wrong code being executed in the wrong environment.

                           

                          That is because the flags that control the compilation need to be set properly at the time the code is compiled. If someone forgets to do that the compiled code won't be what you think it is. And to avoid that it means your compilation process generally needs to be controlled by a script.

                           

                          If you are writing an anonymous block or simple code for reports or things you know YOU will run yourself only in sql*plus (e.g. in your own sandbox database) then go ahead and use DBMS_OUTPUT.

                           

                          But as part of a developer team in an org I don't recommend it. Medium to large orgs I have worked with have some standard 'code review' checks that we make to try to prevent problems:

                           

                          1. scanning of all source code checking for certain keywords (WHEN OTHER, DBMS_OUTPUT to mention just two)

                           

                          2. use of code review checklists that developers complete and submit with their code that are part of any code review. Those check lists include questions (boxes to check) about: any dbms_output?, any exception handlers?, any bulk processing?, any use of database links?, any logging?

                           

                          That is the questions are simple enough for even a novice developer to answer without having to write an essay or description. That lets the code reviewers or other developers easily identify the code that is most likely to be problematic.