Re: Very long queries in V$SQLTEXT

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 25 Nov 1999 20:20:13 +0800
Message-ID: <383D297D.FBA@yahoo.com>

Jonathan Lewis wrote:
>
> Could you be more specific about
> what you think is nonsense ?
>
> Discoverer (except perhaps for the very
> newest versions) write the most appallingly
> unreadable SQL, along the lines of:
>
> select
> t1100012.c19350011 as "meaningful name",
> t1100012.c19350012 as "another name",
> t1101322,c17633993 as "yet another name"
> from
> (select
> col1 as "c19350011",
> col2 as "c19350012"
> from
> tableX
> ) as t100012,
>
> etc.
>
> Every column gets a meaningless alias
> Every table become an inline view of itself
> Every join condition has at least two sets of brackets.
>
> V$SQLTEXT really does tell it the way it is.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Connor McDonald wrote in message <383BE051.68D7_at_yahoo.com>...
> >I'm at site where there seems to be competition to write the longest
> >SQL's in the world. Once they get above 1000 chars, the usefulness of
> >V$SQL and V$SQLAREA disappears.
> >
> >So I've been using V$SQLTEXT - which is fine except for all queries
> >created by Oracle Discoverer. In these cases, V$SQLTEXT returns
> >basically nonsense.
> >
> >Can anyone assist ? Posts of samples available...
> >
> >--

Thanks for the reply Johnathon - I've seen the Discoverer "interpretation" of the queries, but my error is getting literally garbage back from this view... ie where clauses mixed in within the from clause etc etc

Example.

SQL> l
  1 select sql_text
  2 from v$sqltext
  3 where address = '&address'
  4* order by piece

outputs..

SELECT i131535 as E131535 , i131539 as E131539 , i131541 as E131 541 , i131581 as E131581 , i131585 as E131585 , i131743 as E1317 43 , i131747 as E131747 , i131766 as E131766 , i131836 as E13183 6 , i131853 as E131853 , i131854 as E131854 , i131860 as E131860

  ..
  .. (all fine)
  ..

_DMS_ID AS i137147, PERCENTAGE_SPLIT AS i137148 FROM LIFE1.NB_AG

    and (i(i131578 = i131853)
    and (i131743 = i131745)
    and (i131534 = i137145)
    and (i137144 = i131857 AND i137147 = i131854 AND i137146 =
    AND ( o131889.i131891 LIKE 'SNU00073730A%')

ND ( o131742.i131766 >= TO_DATE('19990101000000','YYYYMMDDHH24MI

    AND (  ( o131742.i131744 <> 'LN' OR ( o131742.i131744
    AND ( UPPE1742.i131747 IN ('C','N','A') ) ) )
    AND ( o131742.i131PPER('COMPLETION'))

  GROUP BY i131535, i131539, i131541, i131581, i13158 5, i131743, i131747, i131766, i131836, i131853, i131854, i131860 , i131891, i131903, i132083, i137145

117 rows selected.

Notice the TO_DATE about 8 lines up - its not completed, and the UPPER a couple of lines below is garbled...

I've tried it with huge non-Discoverer queries and all is fine...Its only Discoverer...

Very odd...

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Nov 25 1999 - 06:20:13 CST