Re: Formating sql_text in v$sqltext

On 16 Jan 2002 16:39:17 -0800, bchorng_at_yahoo.com (Bass Chorng) wrote:

>Does anybody have a good way to format sql_Text in v$sqltext so
>that it can be spooled and executed ?
>
>The only way I know is manually join the pieces in editor which can
>take forever if the sql_text is huge.
>
>I know there is a new view in 8i v$sqltext_with_new_lines which
>is supposed to do something, but whatever that is, I haven't
>seen it.
>
>Thanks.
>
>-Bass Chorng

I feed the spooled output from v$sqltext to a text editor where I have a macro to clean it all up. This could be done with any word processor or text editor that has a macro capability. Here's some pseudo-code to give you an idea of what I do. This doesn't catch everything (it usually leaves a few split words and run-ons that need to be touched up, but it gets 99% of it. The sequence is important.

' remove all paragraph marks
replace all x'ODOA' with nulls

' remove all double quotes
replace all " with nulls

' reduce multiple spaces

replace all space(60) with space(1)
replace all space(40) with space(1)
replace all space(20) with space(1)
replace all space(10) with space(1)
replace all space(5) with space(1)
replace all space(4) with space(1)
replace all space(3) with space(1)
replace all space(2) with space(1)

' remove embedded spaces from common words

replace "S ELECT " with "SELECT "
replace "SE LECT " with "SELECT "
(etc) and (etc for "FROM", "WHERE", "ORDER BY", others you may discover)

' Force 'SELECT' to new line with some additional spacing replace "SELECT " with x'ODOAODOAODOA'+'SELECT'

' force keyword clauses to new line
replace "FROM " with x'ODODA'+"FROM"
replace "WHERE' . . . .
replace 'ORDER BY ' . . .

' force new line after every comma, lining up multiple arguements replace "," with ","+crlf+tab

' force all 'AND' and 'OR' to new line
replace " AND " with x'ODOA'+"AND"
etc.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)