7 Replies Latest reply on May 11, 2016 10:31 AM by Mac_Freak_Rahul

    sql developer debugging question




      My db : 11g

      sql developer version : Version


      I am using sql developer for debugging, it works fine, I am looking for an answer, please help me out:


      1) while I am debugging and lets say i encounter a dbms_output.put_line statement and then I step into , such that I execute this dbms_output.put_line statement, whats happening is that I am seeing its output after my debugger finishes executing the last statement and in fact when the debugger ends its operations, is there any way to see the output on console while I am running the debugger.


      Any help is greatly appreciated.




        • 1. Re: sql developer debugging question

          Now the way dbms_output works is, that it writes into a buffer and the IDE reads that buffer AFTER the program unit that invoked dbms_output has completely finished...so no (not that I am aware off). This is perhaps also the reason why debugging is not done using dbms_output. There are other ways to look into what you are trying to display using the dbms_output package....



          1 person found this helpful
          • 2. Re: sql developer debugging question

            Execute your script as 'execute script' F5 command and it displays the results instantly on the script output console.

            • 3. Re: sql developer debugging question



              The debugger lets you inspect all the program's variables, so why would you need to use dmbs_output?

              • 4. Re: sql developer debugging question

                *** moderator action: question moved to SQL Developer space as it is not a specific SQL or PL/SQL question ***


                In answer to your question...


                DBMS_OUTPUT is not a method of displaying things in real-time.  By using the PUT/PUT_LINE statements of the package you are putting data into the DBMS_OUTPUT buffer.

                Whether that buffer is read or not depends on the client interface settings, that's why, in SQL*Plus you have to issue a "set serveroutput on" statement first to indicate you want SQL*Plus to obtain the contents of the buffer, and likewise in SQL Developer, PL/SQL Developer or TOAD etc. they have similar options for switching on the ability to read the buffer.


                However, control is not returned to the interface until after the PL/SQL code (that is running on the server) has completed, so it is only upon completion of the code that the interface can query the contents of the buffer, obtain whatever has been put in there, and then render it to it's display.


                If you want to see things running in real time either use the SQL Developer debugger tool that lets you inspect variables etc. as you debug, as Greg says, or write things out using an pragma autonomous_transaction procedure to a debugging table on the database, which can then be inspected from another session.

                • 5. Re: sql developer debugging question

                  Thanks all for the help, as per your suggestions, I have managed to figure out a way.


                  PS: I know I can see the variables interactively but I wanted to see the text I am printing on the console as I am cooking a lot of sql statements that I am firing using execute immediate dynamically.




                  • 6. Re: sql developer debugging question

                    Get the idea out of your head that you are "printing on the console" using dbms_output.  processes running on the server (e.g. SQL or PL/SQL) cannot print to any client console as they have no interface to either keyboard or screen.  All dbms_output is doing is storing data in a buffer.


                    As for why you're firing lots of dynamic SQL using execute immediate, well, that's another issue.

                    • 7. Re: sql developer debugging question

                      Yeah I got your point, I understood the thing but I might have stumbled while writing my text, and as far as firing dynamic sql using execute immediateis concerned I guess Its working for me so I keep it as it is without much discussion on this part, thanks for helping.