This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Jul 26, 2010 3:44 AM by 787390 RSS

DBMS Output

benton Newbie
Currently Being Moderated
Hi,

I have a Oracle Database 10g Express Edition Release 10.2.0.1.0 installed along with SQL Developer Version 1.2.0 on my pc with an XP platform.

Using SQL Developer, I have been successfully using scripts and viewing the output on the tab named Script Output, after ensuring "set serveroutput on" is activated on the tab named DBMS Output.

I am now in the process of testing my database on the companies oracle installation, which I have no privileges on other than those the DBA has granted me, I want to run the same scripts and view the output by means of the tab named Script Output, but all I get is the response "anonymous block completed". Would anyone know what the issue could be, the reason they display in one instance but on another database they don't. My scripts are of the type below,
DECLARE num NUMBER;
rec_count varchar2(128);

BEGIN

  FOR c1 IN
    (SELECT object_name
       FROM user_objects
      WHERE object_type = 'TABLE')
  LOOP
    EXECUTE IMMEDIATE 'select count(*) from ' || c1.object_name
    INTO num;

    rec_count := 'Table ' || TRIM(c1.object_name) || ' has : ' || num || ' records';

    dbms_output.put_line(rec_count);

  END LOOP;

END;
Thank You
Ben

Message was edited by:
Benton
  • 1. Re: DBMS Output
    -K- Guru
    Currently Being Moderated
    There are several serious bugs with DBMS_OUTPUT in sqldev.
    To track down if you're hitting one of them, first, make yourself a working case on your Express Edition (start sqldev, open script, enable output, etc.). Then follow the exact same steps on the company's DB. Make sure the first step is starting sqldev.

    K.
  • 2. Re: DBMS Output
    SueHarper Oracle ACE
    Currently Being Moderated
    Several serious bugs?

    The only open DBMS_OUPUT bug I see is a recent entry where not all output lines are displayed to the DBMS_OUTPUT display tab. However if you re-run your query they do. I have experienced the sporadic nature of commands displayed to the tab and so typically rerun my queries. This is what I recommend in this situation.

    However, I would like to review the serious bugs you mention.

    Sue
  • 3. Re: DBMS Output
    -K- Guru
    Currently Being Moderated
    Sorry, but this really pisses me off.
    This is not the first time (and I'm pretty sure not the last one either) we're having this conversation. Sorry if I get rude.

    We are many users on this forum trying to help you improve your product reporting problems. But only in a few cases someone from the team reacts.
    On other occasions, you said that only happens if a new problem is reported deep in another thread. OK, I can understand that things get lost, but if you would read what people write, you would catch those anyway (like I and so many others do).
    I have a fairly occupied working day, but manage to follow the forum and try to help out others. It's your program, your forum, you're getting paid to make this work!
    As I said on other occasions, if we don't have access to the bug database (just a few items on Metalink), at least give us confirmation of the issues here on the forum. But it seems you only take interest in some very specific ones. Granted, once you do, you follow up very correctly.
    But even after bumping threads without response, even after putting BUG in the subject, even after putting the subject in bold, you still manage to ignore them completely.
    Given that on top all that is free help and testing, I really don't know what kind of management that is.

    Sheez, after that, I really don't want to look them up again. The forum has a perfectly working search function, I think you're pretty capable of using it yourself, aren't you? That's what I would have to do and did on so many other occasions. Else keep an eye out until someone bumps the threads again.

    Again sorry for being rude, but that's the way I feel.

    Regards,
    K.
  • 4. Re: DBMS Output
    Jim Smith Expert
    Currently Being Moderated
    I'm not sure I follow you. If I take your code and run it with F5 (Run Script), I get the code in "Script Output" and the results (Table X has : y records) in "DBMS Output".

    As far as I can tell, this is the expected behaviour.
  • 5. Re: DBMS Output
    -K- Guru
    Currently Being Moderated
    For him, it works on 1 of 2 databases.
    I regularly miss output too. To get it working again, I often have to disable, then enable output again, multiple times. Sometimes I even get unrecoverable crashes manipulating the button.
    As I'm pretty sure it always works the first time, I suggested to restart before trying on the second one.

    K.
  • 6. Re: DBMS Output
    Jim Smith Expert
    Currently Being Moderated
    What I don't follow is that he seems to be saying he was getting dbms_output in the Script Output window. I'll wait till he responds rather than take your interpretation of what is going on.
  • 7. Re: DBMS Output
    -K- Guru
    Currently Being Moderated
    Well yes, I read that too. But as it shouldn't be possible, I interpreted he's just executing as script.
    If he did get it there, he just hit another bug I guess...

    So Ben, any word?

    K.
  • 8. Re: DBMS Output
    561237 Journeyer
    Currently Being Moderated
    I don't tend to run things as scripts - I normally just run statements, but didn't the DBMS output get put on the Script Output tab when running scripts in at least one of the older versions of SQL Developer? In 1.2.1.32.12, when running Ben's statement as a script I get the "anonymous block completed" in the Script Output and the desired output on the DBMS Output tab.

    One possible cause is that the Enable DBMS Output button for new windows remembers the state of earlier windows, even for different connections. For example, I connect to DB A and Enable DBMS Output in the SQL Worksheet for DB A - DBMS Output works fine in the DBMS Output tab. If I then connect to DB B I get a new SQL Worksheet for DB B with the Enable DBMS Output button showing as enabled but it isn't really enabled - you need to "disable" it and then re-enable it before the DBMS Output tab works.

    Sue - this is also an example of why we feel frustrated at times. This sticky Enable DBMS Output button problem has been in every version of SQL Developer that I have used (which is back to almost the first pre-release version) and I can find an 20 month old post (Enable DBMS Output and multiple worksheets/connections where the SQL Dev team have responded that it will get looked at, but it has never been fixed.
  • 9. Re: DBMS Output
    benton Newbie
    Currently Being Moderated
    Hi Guys,

    Did'nt expect so much passion.

    Just to clarify my problem, I am using my local machine XE on XP to develop my database as explained above and I have no problems getting the product to ouput the script in the output window, using either SQL Developer or SQL*Plus.

    The problem in SQL Developer ...

    Using our company database here I will call server-test-2.dev and using SQL Developer on my machine the scripts output to the window no problem.

    We have another company database here and I will call it server-test-2.tst and using SQL Developer on my local machine the scripts will not show there output other than "anonymous block completed".


    The problem in SQL*Plus ...

    Now I didnt know at the time that the issue might be related but I posted another message at the following address SQL*Plus output for SQL*Plus.

    I am having the same issue with SQL*Plus. I get output spooled to a file on server-test-2.dev,but when I try to run my scripts against server-test-2.tst I only get the following 'PL/SQL procedure successfully completed.', but when I don't use a PL/SQL procedure and just use a straight SELECT or DESCRIBE statement, this gets spooled to the file as correct output.

    So anyway I am flumoxed as to what the problem is.

    Ben

    Message was edited by:
    Benton
  • 10. Re: DBMS Output
    Jim Smith Expert
    Currently Being Moderated
    I think we need a bit more clarity.

    There is a difference between output from dbms_output and the direct results of a query.
    Direct query results go to the script output window and dbms_output goes to the dbms_output window, provided you have enabled serveroutput. In SQL*Plus you will only get dbms_output showing if you add "set serveroutput on" at the top of your file.
    Using our company database here I will call server-test-2.dev and using SQL Developer on my machine the scripts output to the window no problem.
    Which window? Are you saying that the dbms_output goes to the "Script output" window? That is not normal behaviour in the current version.
  • 11. Re: DBMS Output
    491884 Newbie
    Currently Being Moderated
    > ...
    Are you saying that the dbms_output goes to the "Script output" window? That is not
    normal behaviour in the current version.

    Suppose the following script:
    begin dbms_output.put_line('Text line 1'); end;
    /
    select 'Text line 2' from dual
    /
    begin dbms_output.put_line('Text line 3'); end;
    /
    select 'Text line 4' from dual
    /
    I run the script using F5 key and I can see all its output in Script Output tab and nothing in DBMS Output tab (SQLDev 1.2.0.29.98). And I believe this is normal behaviour. As I can understand you, you expect Text line 1 and Text line 3 in DBMS Output tab and Text line 2 and Text line 4 in Script Output tab. Or am I wrong?
  • 12. Re: DBMS Output
    -K- Guru
    Currently Being Moderated
    I get:

    anonymous block completed
    'TEXTLINE2'
    -----------
    Text line 2

    1 rows selected

    anonymous block completed
    'TEXTLINE4'
    -----------
    Text line 4

    1 rows selected

    K.
  • 13. Re: DBMS Output
    491884 Newbie
    Currently Being Moderated
    And I get:

    anonymous block completed
    Text line 1

    'TEXTLINE2'
    -----------
    Text line 2

    1 rows selected

    anonymous block completed
    Text line 3

    'TEXTLINE4'
    -----------
    Text line 4

    1 rows selected


    Well, I believe you have different version of SQLDev. But what output should be considered as correct? Yours or mine?
  • 14. Re: DBMS Output
    Jim Smith Expert
    Currently Being Moderated
    I get the same as Matt except that I get
    begin dbms_output.put_line('Text line 1'); end; succeeded.
    instead of "anonymous block completed".

    According to various threads here, and the documentation, Matt's is the correct output. It is open to debate whether that is the right thing to do. I tend not to mix queries and dbms_output so the current arrangement suits me. YMMV.
1 2 Previous Next