Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Select the full Text of an SQL Statement

Posted on 2011-03-07
Medium Priority
Last Modified: 2012-05-11
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
Question by:Alex Matzinger
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 35064020
You will have to query the CLOB column in v$sqlstats.sql_fulltext

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?  

Accepted Solution

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

set long 100000


Author Closing Comment

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

636 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