Re: Trying to combine 2 scripts

On Tue, 25 May 2004 14:47:17 GMT, Vince Laurent <vincelaurent_at_sbcglobal.net> wrote:

>This script does the other half:
>
>col sql_text format a80 word_wrapped
>
>select sql_text
>from v$sqltext
>where address = (select sql_address from v$session where sid=524)
>and hash_value = (select sql_hash_value from v$session where
>sid=524)
>order by piece
>
>/
>
>So... when I run the first I get:
>Sid OS-User Owner.Object Name Object Type
>-------------------------------------------------------------------------------
>226 SISIUSER 002s06 SISI.MODULE_USAGE TABLE
>240 SISIUSER 113-s06 SISI.MODULE_USAGE TABLE
>459 SISIUSER 007s05 SISI.MODULE_USAGE TABLE

The sql statement should be written as
select sql_text
from v$sqltext
>where (address, hash_value) = (select sql_address, sql_hash_value from v$session where sid=524)
>order by piece

eliminating one subquery

then convert it into a cursor

cursor csql_text(p_sid in number) is
select sql_text
from v$sqltext
where (address, hash_value) = (select sql_address, sql_hash_value from v$session where sid= p_sid)
order by piece

and that is all there is to it.

open the cursor with
open csql_text(your_sid)

--
Sybrand Bakker, Senior Oracle DBA