Solved

dbms_out put not working in my Toad

Posted on 2014-09-23
38
679 Views
Last Modified: 2014-09-25
hi
i'm working with toad 9.0  ,
from main menue -> view , i select dbms output
it opened in a new tab beside the editor , output already turned on , but it not output any thing when i run my script in the editor
0
Comment
Question by:NiceMan331
  • 14
  • 9
  • 9
  • +2
38 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40340922
Do it this way:
1. main menu -> session -> server output
2. main menu -> view -> code editor
3. run the code F9 or green arrow
4. after running the code at the bottom of code editor there will be Script and Spool
5. click Spool and there you will see this:
BEGIN
  s_var:='a111b-a22222b-a5555b-a6666666666b-a888888888888888888b-a44444b';
  dbms_output.put_line(s_var);
  n_length_var:=length(s_var);
  n_occurance:=0;
  FOR j in 1 .. n_length_var loop
   IF instr(s_var,'-',1,j)>0 THEN
     n_occurance:=n_occurance + 1;
   END IF;
  end loop;
  dbms_output.put_line(n_occurance);
  n_pos_1:=1;
  for i in 1 .. n_occurance loop
    n_pos:=instr(s_var,'-',1,i);
    n_length:= n_pos- n_pos_1;
    s_var_1:=substr(s_var,n_pos_1,n_length);
    dbms_output.put_line(s_var_1);
    n_pos_1:=n_pos + 1;
    IF i=n_occurance THEN
      s_var_1:=substr(s_var,n_pos_1);
      dbms_output.put_line(s_var_1);  
    END IF;
  end loop;
  end;
==>
PL/SQL block executed
a111b-a22222b-a5555b-a6666666666b-a888888888888888888b-a44444b
5
a111b
a22222b
a5555b
a6666666666b
a888888888888888888b
a44444b
--------------------------------------------------------------------------------
**** SCRIPT ENDED 24-9-2014 08:44:44 ****
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40340940
Oh sorry, my post is related to SQL Navigator.
0
 

Author Comment

by:NiceMan331
ID: 40340985
so , do you have a solution for my question here ?
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40340995
No, I have no solution here, I hope someone else can help you.
Maybe you can check some properties of TOAD.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40341030
you have two options in toad, one is DBMS Output and other is script output, check whether your output has gone in other tab..

generally when you execute it as a script, it goes into Script Output, if you have dbms_output.enable in your code or if you specify set serveroutput on, then it comes to DBMS Output tab.

Also check out if the dbms_output is enabled in DBMS Output console, then it will be enlighted to Green Colour, if its off, then it wil be in Red colour

check on this link on how to enable DBMS Output and Script Output Console tabs

http://geekbrigade.wordpress.com/2009/04/09/how-to-set-and-view-dbms-output-of-oralce-in-toad/
0
 

Author Comment

by:NiceMan331
ID: 40341089
when i added
set serveroutput on; to the code , it gives error
DECLARE
tempvar NUMBER;
BEGIN
set serveroutput on
DBMS_OUTPUT.PUT_LINE('hello world');
END;

Open in new window

missing or invalid option
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40341162
it should be added before the plsql block like this

set serveroutput on;
DECLARE
tempvar NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40341183
yes ok , it comes in the script output
but still not coming the dbms output
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40341214
thats what I had said, DBMS Output window is different from Script output window.

To get the same output in DBMS Output, you have to make sure that you enabled the DBMS Output button(Green Button) in the DBMS Output Console.

Or try this, it should enable the DBMS Output in console

DECLARE
tempvar NUMBER;
BEGIN
dbms_output.enable(100000);
DBMS_OUTPUT.PUT_LINE('hello world');
END;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40341227
NO , it sill in script output , nothing in dbms_output , i checked the console button it is green
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40341321
It seems strange..
may be its one of the reasons/bugs in early versions of the toad, you are executing the anonymous block as a script, hence its output is visible in script output

Instead of selecting the storm button(execute as script(Shortcut -> F5)), select the whole content and execute that with Green Arrow(Shortcut -> F9), i think this should work and will give you the output in DBMS Output window
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40341406
Do you have dbms output enabled but not polling or polling with a very long interval?

I don't normally use the menu-view-output window, I just use the tab next to the data grid on the sql editor.  Does your output show up there?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40341408
Not a GUI user but in sqlplus you need a '/' at the end of a pl/sql block to actually execute it.

Try this:
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END;
/                                          

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40341495
yes slightwv , now it is ok
but execuse me , i will distribute the pionts between you & waseem , because his solution helped me to show the script output also
thanx
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40341503
you don't need a "/" in Toad to get dbms output.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40341535
Yes.. I agree with sdstuber..

You just have to toggle the output button.. However in case DBMS_output.enable is used.. Output will come in console even when its off.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40341552
>>> However in case DBMS_output.enable is used.. Output will come in console even when its off.

that's because toggling the output option simply executes dbms_output.enable or dbms_output.disable.

But - if you turn off polling you won't see it either way.
0
 

Author Comment

by:NiceMan331
ID: 40341601
So why only when I used / it output even the dbms output enabled and the output turned on with green color
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 73

Expert Comment

by:sdstuber
ID: 40341636
did you check your polling?

enabled is different than polling

enabled means the output buffers will be populated
polling means toad will read those buffers
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40341646
also, I'm not sure which dbms_output area you're talking about.

Are you using the separate window or the tab within the sql editor window?
For me the options are slightly different on each one for controlling the polling.
0
 

Author Comment

by:NiceMan331
ID: 40341727
Ok , let step by step , how to make the dbms output enabled , and let me investigate it
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40341733
I guess the author is talking about DBMS Output console SQL Editor window, thats where you can see the icons turning into red and green coloured when disabled and enabled..

Yeah..sdstuber, the dbms_output.enable is called internally, i had posted the option because that would get enabled even if its not enabled by author, however I now suspect that polling timeout can be one of the reasons..  but then again the author had confused by making a comment that "/" added at the end solved the problem. Not sure where this would lead to..
0
 

Author Comment

by:NiceMan331
ID: 40341769
To be honest with you , till now I don't know the difference between enable and polling , may be language issue , any how , answering question of sdstubar , Im writing code in SQL editor tab , the I select main menus view - dbms output , it open new tab for it , so now I have 2 tabs , one for SQL editor where I run my code , the other one for dbms output where I expect to have the output there
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40342000
Both options are in the same view..:)

Enable is used to control the view.. Is., start/stop the output.

Polling is a mechanism which will specify the duration after which the output will start flowing onto console

anyways its a simple question.. Though I am amazed by the directions and possible predictions. That's why I like experts exchange
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40342008
try not using the separate dbms output window

in the sql editor where the data grid shows up, you should see a dbms_output tab there.
if you don't, right click in that tool bar and add it.
0
 

Author Comment

by:NiceMan331
ID: 40342080
No I don't have it in that area , so , where I should right click ? In behind of those tabs ?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40342091
yes


if you've set custom toolbars or locked them you may need to unlock them to get it to activate.  It is normally there by default though.  That's where I've been using it for about 17 years.  I find using that one much more convenient than swapping over to a different window.
0
 

Author Comment

by:NiceMan331
ID: 40342378
Yes now it is ok , but now if I expect too much output , this small tab is not enough to read then one time , it will be better to open new window for it
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40342659
you can always stretch the visible area of the tabs, but if you do want to go to a new window then make sure the polling settings are the same in both.

also, to check functionality, turn on command spooling and check that both the tab and the window do the same thing.  If not, then you've either run into a "feature" or more probably a "bug"  but - if you're running Toad v9 then you probably won't get support.  If you have support then I definitely recommend upgrading to 12.  There are a lot of new features including support for some oracle 12.
0
 

Author Comment

by:NiceMan331
ID: 40342671
Plz more explanation about polling setting , io not totally understand it
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40343159
I suggest you to close this post as its already well answered and open up other question about the polling topic.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40343312
you could have accepted my comment as an assisted solution

http://www.experts-exchange.com/Software/Misc/Q_28524421.html#a40341030

As I too had mentioned the same in my very first comments.. Anyways.. never mind..!!

This was quite a long thread.. not interested to pursue this further..
0
 

Author Comment

by:NiceMan331
ID: 40343364
i'm sorry wasim

when i accepted your answer as 2nd option , you didn't comment any complain to sbstubar , furthermore you replayed that sdstubar is correct
in this case , administrative comment is requested here to decide
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40343739
>>Plz more explanation about polling setting

Polling is simply a timer for when to read the dbms_output buffer.
So, if the timing interval is 30 seconds, then every 30 seconds Toad will look to see if your buffer has anything in it.  If it does it will read it and display it.

If your timing interval is small then toad will be making lots of extra to calls to the database for nothing.  If your interval is large then toad will wait a long time between calls, meaning you'll see no data.


If you want to split points between answers that were correct and helpful I won't object.  I can reopen the question for you.  I didn't do it earlier because it would have potentially benefited my myself (and it did) but I can and will do so to lose points if you want to reallocate.
0
 

Author Comment

by:NiceMan331
ID: 40344275
No , I don't want to split the cost , it is ok for me ,  in same time I really appreciate the effort of wasim , but I just want to draw his attention that he admitted earlier that your answer is the correct one , so he should not be angry if he get no point
0
 

Author Comment

by:NiceMan331
ID: 40344290
Sorry , the point , not the cost
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now