Enhancement request - Sessions...

[Thomas Svensen]

I understand that PL/SQL Developer's main focus is not as a DBA tool, but since it has so much excellent functionality, we still use it as one

Therefore, a minor improvement to the "Sessions" window would be nice:

The "SQL Text" tab shows the raw SQL as it is select from the internal views. Without too much effort, you could concatenate the strings and run it through the beautifier, couldn't you? It would be VERY useful to us!

- Thomas

 

[orca777]

Hi,
if the SQL is less then 1000 Bytes you would not even have to conncanate it (V$SQL)
carl

 

[Marco Kalter]

The sessions tool is a generic tool, and concatenating certain columns is a bit specific. You can however perform this concatenation in a function and return the results. Create the following function under the SYS account:

create or replace function sqltext_full(p_address    in raw,
                                        p_hash_value in number) return varchar2 is
  result varchar2(4000);
begin
  result := '';
  for c in (select sql_text
              from v$sqltext_with_newlines
             where address = p_address
               and hash_value = p_hash_value
             order by piece)
  loop
    result := result || c.sql_text;
  end loop;
  return(result);
end sqltext_full;

Grant it to public and create a public synonym:

SQL> grant execute on sqltext_full to public;
SQL> create public synonym sqltext_full for sys.sqltext_full;

Now you can add a session query like this in the Sessions tool:

select sqltext_full(:sql_address, :sql_hash_value) full_text
from dual

The result will be displayed as one line in the Sessions tool initially, but you can resize the row of the grid to show more lines, and you can always copy/paste it correctly.

 

[Thomas Svensen]

I had overlooked the ability to add customized session queries - very cool feature!

Thank you very much for the help
& good luck on version 6.0

- Thomas

 

[Christo Kutrovsky]

Thomas, if you search the list with my name, you will find several interesting queries specificalyl designed for the Sessions's tabs.

Let me know if you liked them.