Toad 12.5 Session Browser, Current Statement: Sql is truncated

Toad for Oracle
1

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?

2

What version of Oracle are you using?

3

11.2

4

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?

5

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.

6

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.

7

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?

8

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.

Capture.jpeg
Capture.jpeg1329×842 169 KB

9

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.

10

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

  1. 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.