1 person found this helpful
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....
Execute your script as 'execute script' F5 command and it displays the results instantly on the script output console.
The debugger lets you inspect all the program's variables, so why would you need to use dmbs_output?
*** 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.
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.
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.
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.