Home » SQL & PL/SQL » Client Tools » dbms_output in Toad or SQL Plus (Oracle 11g Rel2 , SQL Plus, and Toad)
dbms_output in Toad or SQL Plus [message #614528] Thu, 22 May 2014 16:28 Go to next message
azeem87
Messages: 76
Registered: September 2005
Location: dallas
Member
Hi,

i have read and implemented few suggestions from this site on dbms_output.. still when i run the below block.. i am not able to see any output.. in Toad, Sql developer and SQL Plus.. data is there i have taken the top select from this code and ran it runs successfully..

This code is to get the SQL last executed by session(SID) , before we plan to kil these SID we want see the code.
if there is other way to do that please suggest..

SET SERVEROUTPUT ON
set serveroutput on size 100000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||
                ') ospid = ' ||  process ||
                ' program = ' || program username,
             to_char(LOGON_TIME,' Day DD YYYY HH24:MI') logon_time,
             to_char(sysdate,' Day DD YYYY HH24:MI') current_time,
             sql_address, LAST_CALL_ET
        from gv$session
       where status = 'INACTIVE'
       and last_call_ET >1000
       and MACHINE='HUB' 
       and username ='APPLICATION_USER' 
     -- and rawtohex(sql_address) <> '00'
         and username is not null order by last_call_et )
    loop
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null))
                               sql_text
                     from gv$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4)
        loop
            if ( y.sql_text not like '%listener.get_cmd%' )
            --and           y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
            then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' ||
                                      x.current_time||
                                      ' last et = ' ||
                                      x.LAST_CALL_ET);
                dbms_output.put_line(
                          substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;
    end loop;
end;



Thanks
Re: dbms_output in Toad or SQL Plus [message #614531 is a reply to message #614528] Thu, 22 May 2014 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
Senior Member
>This code is to get the SQL last executed by session(SID) , before we plan to kil these SID we want see the code.
BOGGLES my mind!
I seriously doubt this is a Good Thing.
Why don't you fix the underlying problem, if it really exists, instead of fighting only what you observe as a symptom.

I choose to NOT assist doing what I think is foolish actions.

what problem are you really trying to solve?
Re: dbms_output in Toad or SQL Plus [message #614541 is a reply to message #614528] Fri, 23 May 2014 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i am not able to see any output


set serveroutput on

Re: dbms_output in Toad or SQL Plus [message #614544 is a reply to message #614541] Fri, 23 May 2014 00:58 Go to previous messageGo to next message
Littlefoot
Messages: 19890
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel, he already did that (see top of code he posted).

In TOAD, you have to switch output ON. How? While in Editor, select "DBMS Output" tab, push the red button to turn it ON, optionally modify polling frequency and pay attention that polling button is also ON.
Re: dbms_output in Toad or SQL Plus [message #614915 is a reply to message #614544] Wed, 28 May 2014 11:55 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 449
Registered: July 2003
Location: WPB, FL
Senior Member
Littlefoot wrote on Fri, 23 May 2014 01:58
Michel, he already did that (see top of code he posted).

In TOAD, you have to switch output ON. How? While in Editor, select "DBMS Output" tab, push the red button to turn it ON, optionally modify polling frequency and pay attention that polling button is also ON.

PS: In SQL Developer there is also a DBMS Output tab...

[Updated on: Wed, 28 May 2014 12:05] by Moderator

Report message to a moderator

Re: dbms_output in Toad or SQL Plus [message #614917 is a reply to message #614915] Wed, 28 May 2014 12:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
OP mentioned SQLPLUS too. But it seems he did not set serveroutput on in sqlplus. How is it possible that he didn't get an output in Sqlplus?

Edit : typo

[Updated on: Wed, 28 May 2014 12:23]

Report message to a moderator

Re: dbms_output in Toad or SQL Plus [message #618906 is a reply to message #614541] Wed, 16 July 2014 07:22 Go to previous message
javed.khan
Messages: 312
Registered: November 2006
Location: Banglore
Senior Member

Will that help
./fa/12029/0/


[mod-edit: image inserted into message body by bb]

[Updated on: Sun, 20 July 2014 08:31] by Moderator

Report message to a moderator

Previous Topic: SQLPLUS Commands
Next Topic: good idea try to write only ANSI SQL? Use online SQL Validator? Is there a local tool?
Goto Forum:
  


Current Time: Sat Dec 20 17:10:06 CST 2014

Total time taken to generate the page: 0.04872 seconds