Solved

Select the full Text of an SQL Statement

Posted on 2011-03-07
4
458 Views
Last Modified: 2012-05-11
Hello,
I have an oracle 11 database.  I was wondering if there is some way to select the complete text of sql statements from the v$sql... tables.  i have tried looking at sql, sqltext, sqlarea, sql_plan,  and sqltext_with_newlines, but the most the tables contain(by themselves) is 1000 characters of an sql statement.  I would like to be able to type:

Select sql_text from something...and get the complete text of all sql statements in my database no matter how long it is.  Is there a way to do this?  

Thank you
0
Comment
Question by:Alex Matzinger
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 35064020
You will have to query the CLOB column in v$sqlstats.sql_fulltext
0
 
LVL 4

Author Comment

by:Alex Matzinger
ID: 35064457
yes i have and the column returns even less text than the reg varchar2(1000) text fields.  It appears to be showing me maybe 64 chars.  Is there a way to expand how much it is showing me?  
0
 
LVL 7

Accepted Solution

by:
MrNed earned 500 total points
ID: 35064955
If you're using sqlplus, try:

set long 100000

0
 
LVL 4

Author Closing Comment

by:Alex Matzinger
ID: 35066633
Wow...thank you for the help.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question