SQL_TEXT in V$SQL getting truncated

[mcslain]

I am querying V$SQL and the results of SQL_TEXT are getting truncated. Is there an easy way to remedy this, like a SET command?

Thanks
-mcslain

[Mohith123]

usually the text will be displayed in inverted format....

[PAVB]

get all "pieces" together ordered by "piece" then parse it

[vnktummala]

also "set long" env. variable to a higher value.

Thanks,

[pando]

v$sql does NOT show you the entire SQL statements, you need to query v$sqltext_with_newlines until 9i, in 10g you can query sql_fulltext in v$sqlstats (for this one you have to use set long to a hugh value)

[vnktummala]

agree with you Pando. Poster can use sql_fulltext instead of sql_text even from v$sql.

Mcslain - you can get the complete sql from v$sql.sql_fulltext or v$sqltext.sql_text (order by piece) or from v$sqlstats.sql_fulltext. Advantage with v$sqlstats is you can find the SQL even after the cursor aged out of shared pool.

SQL> set long 50000
SQL> set line 100
SQL> select sql_fulltext from v$sql where hash_value=xxxxxxxxxx; or
SQL> select sql_text from v$sqltext where hash_value=xxxxxxxx order by piece; or
SQL> select sql_fulltext from v$sqlstats where plan_hash_value=xxxxxxxxxx;

Thanks,