Toad 12.5 Session Browser, Current Statement: Sql is truncated.
I only see the first part of the statement?
Is there some setting I need?
Is this a reported problem?
What version of Oracle are you using?
11.2
I just tried a statement slightly longer than 5000 characters and the whole thing showed up under “current statement”.
If you turn on spool sql (from the main menu: Database -> Spool SQL -> Spool to screen) then you can see what queries Toad is running. For the current statement, we run one that looks like this:
Select *
from v$sql
where 1=1
and sql_id = :sqlid
(and spool sql will also show you the value we’re using in the bind variable)
and then we get the sql test from the SQL_FULLTEXT column. If you run this query in the editor, you can double-click the SQL_FULLTEXT column to see its contents. Is it truncated there too?
Too late the statement has come and gone . The truncate seems to be only on DDL. And maybe certain DDL like “ALTER TABLE BFI_BASE.”
Next time I see it I will see if it’s in v$sql.sql_fulltext.
I like this continuous Spooling.
John, sorry I just got back to this thread.
I was able to capture the Select * from gv$sql and yes, the sql_txt is truncated BUT the sql_fultext(clob) is not.
So does that mean that Toad 12.5.1.1 running aganst Oracle 11.2.0.4 is selecting from sql_txt and not sql_fulltext ?
Seems so.
I should be using SQL_FULLTEXT, but if it can’t find it there, we’ll look in V$SQLTEXT. Will you give me the spool SQL output of refreshing the Session browser and having the truncated text appear in the current statement box?
Attached is a screen shot. It shows 3 things. The sql DDL statement, the Toad Session browser with the ddl statement truncated, and your SQL Spool to screen.
Note: I see this truncation primarliy with DDL.
Anything I can do further, please let me know.
I tried to reproduce the problem with a similar statement, and for me, the ‘alter table drop column’ statements aren’t appearing in GV$SQL at all. So you are one step ahead of me!
The only thing we do with the statement before displaying it is we put it through the formatter. So I thought maybe the formatter was having a problem with the statement, but when I format it in the editor, it works fine there.
That’s really all that’s going on here, so I’m not sure what the problem is. It’s interesting that the SQL_TEXT column is truncating such a short statement. I always thought that the only reason that Oracle introduced the SQL_FULLTEXT column was for statements that wouldn’t fit into the VARCHAR2 SQL_TEXT column.
Interesting. I’m not to busy so I googled around and found this in Ask Tom.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:13948258923206
jun 2004
- they truncate ddl like that to hide passwords. think “alter user foobar identified by barfoo”
and so on. You get a flavor of the statement but not the entire thing.
That’s it for me. You can CLose this if you want.