Home » SQL & PL/SQL » SQL & PL/SQL » Get Last Command statement
Get Last Command statement [message #18524] Mon, 04 February 2002 07:47 Go to next message
John Augusto Charnet
Messages: 1
Registered: February 2002
Junior Member
Hello ... I need to retrieve the last command statement through a select. In this case it's not actually the last command statement, but the one before the last because the last will be the select that I've just done to find out whick statemente was executed before this select.

Thanks a lot...
Sincerely,
John
Re: Get Last Command statement [message #18535 is a reply to message #18524] Mon, 04 February 2002 16:55 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
based on code from asktom.oracle.com:
    declare
        x number;
    begin
        for x in
        ( select status, username||'('||sid||','||serial#||
                    ') ospid = ' ||  process ||
                    ' program = ' || program username,
                 to_char(LOGON_TIME,' Day HH24:MI') logon_time,
                 to_char(sysdate,' Day HH24:MI') current_time,
                 sql_address, LAST_CALL_ET
            from v$session
           where --status = 'ACTIVE'
             --and 
			 rawtohex(sql_address) != '00'
             and username is not null order by status, 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 v$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.status );
                    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;

Note - the report will only show stuff still in the shared pool.
Re: Get Last Command statement [message #18555 is a reply to message #18524] Tue, 05 February 2002 07:21 Go to previous messageGo to next message
JOHN
Messages: 182
Registered: April 1998
Senior Member
Hi Andrew, do u know if this can be applied to sql scripts which have just been run by the user ? i have a similar problem where i need to find the name of the script just run by the user...

http://www.orafaq.net/msgboard/serverutil/messages/3643.htm

any help would be much appreciated

cheers.
Re: Get Last Command statement -TO ANDREW [message #18578 is a reply to message #18524] Wed, 06 February 2002 04:47 Go to previous messageGo to next message
Nina
Messages: 113
Registered: March 2000
Senior Member
Sir,

Could you specify the name of the thread from Tom's site. This code is hanging on my instance.

Thank you
Re: Get Last Command statement -TO ANDREW [message #18584 is a reply to message #18524] Wed, 06 February 2002 07:27 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:165844
Previous Topic: The Long datatype and Oracle7 string manipulations
Next Topic: Quotation Marks in SQL output
Goto Forum:
  


Current Time: Tue Sep 28 12:30:00 CDT 2021