Re: Very long queries in V$SQLTEXT

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 24 Nov 1999 21:22:43 -0000
Message-ID: <943479003.20293.0.nnrp-01.9e984b29@news.demon.co.uk>

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...
>
>--
Received on Wed Nov 24 1999 - 15:22:43 CST