Xref: alice comp.databases.oracle.server:75440
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!colt.net!dispose.news.demon.net!demon!news.demon.co.uk!demon!jlcomp.demon.co.uk!not-for-mail
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: Very long queries in V$SQLTEXT
Date: Wed, 24 Nov 1999 21:22:43 -0000
Message-ID: <943479003.20293.0.nnrp-01.9e984b29@news.demon.co.uk>
References: <383BE051.68D7@yahoo.com>
X-Trace: news.demon.co.uk 943479003 nnrp-01:20293 NO-IDENT jlcomp.demon.co.uk:158.152.75.41
X-Complaints-To: abuse@demon.net
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Lines: 48


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@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...
>
>--