Prev: Seeking PL/SQL tutorials in DVD or CD format
Next: orcle details
From: bang123 on 16 Jul 2008 03:05
Hi,

We are migrating from Oracle 6i to Crystal Reports. Oracle reports
were built using Oracle 6i Data Wizard and Query Builder. How can we
see the end query (final query including joins for links) that is
being executed by Oracle Reports? Oracle Reports 'trace' shows only
parts of queries being executed, but not the entire query.

Thanks
From: Mark D Powell on 16 Jul 2008 09:14
On Jul 16, 3:05 am, bang123 <vasuad...(a)gmail.com> wrote:
> Hi,
>
> We are migrating from Oracle 6i to Crystal Reports. Oracle reports
> were built using Oracle 6i Data Wizard and Query Builder. How can we
> see the end query (final query including joins for links) that is
> being executed by Oracle Reports? Oracle Reports 'trace' shows only
> parts of queries being executed, but not the entire query.
>
> Thanks

Run SQL Trace on the database session and pull the SQL from there.

Though a programmer should be able to figure out what the query is
just by looking at the report program and the where clause information
generated by the reports trace.

HTH -- Mark D Powell --
From: Malcolm Dew-Jones on 21 Jul 2008 15:11
bang123 (vasuadiga(a)gmail.com) wrote:
: Hi,

: We are migrating from Oracle 6i to Crystal Reports. Oracle reports
: were built using Oracle 6i Data Wizard and Query Builder. How can we
: see the end query (final query including joins for links) that is
: being executed by Oracle Reports? Oracle Reports 'trace' shows only
: parts of queries being executed, but not the entire query.

: Thanks

It might not be running an "end query". Reports can loop over a query
getting one row at a time, and in between getting each row of that query
it might run another query based on all the conditions set up in the
report. The second query may or may not have any obvious connection to
the first query.

In report builder you should be able to see all the details in one of the
windows (I cant check the names right now).

From: Mark D Powell on 24 Jul 2008 11:08
On Jul 21, 3:11 pm, yf...(a)vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:
> bang123 (vasuad...(a)gmail.com) wrote:
>
> : Hi,
>
> : We are migrating from Oracle 6i to Crystal Reports. Oracle reports
> : were built using Oracle 6i Data Wizard and Query Builder. How can we
> : see the end query (final query including joins for links) that is
> : being executed by Oracle Reports? Oracle Reports 'trace' shows only
> : parts of queries being executed, but not the entire query.
>
> : Thanks
>
> It might not be running an "end query".  Reports can loop over a query
> getting one row at a time, and in between getting each row of that query
> it might run another query based on all the conditions set up in the
> report.  The second query may or may not have any obvious connection to
> the first query.  
>
> In report builder you should be able to see all the details in one of the
> windows (I cant check the names right now).

I would think the case of where Reports Builder runs the second query
using data retuned by the first query and report conditions would be
where the raw sql trace file on the database would be helpful since it
will show each query ran.

It really helps to understand the tool set in cases like the bang123
posted.

-- Mark D Powell --
From: Malcolm Dew-Jones on 28 Jul 2008 13:13
Mark D Powell (Mark.Powell(a)eds.com) wrote:
: On Jul 21, 3:11=A0pm, yf...(a)vtn1.victoria.tc.ca (Malcolm Dew-Jones)
: wrote:
: > bang123 (vasuad...(a)gmail.com) wrote:
: >
: > : Hi,
: >
: > : We are migrating from Oracle 6i to Crystal Reports. Oracle reports
: > : were built using Oracle 6i Data Wizard and Query Builder. How can we
: > : see the end query (final query including joins for links) that is
: > : being executed by Oracle Reports? Oracle Reports 'trace' shows only
: > : parts of queries being executed, but not the entire query.
: >
: > : Thanks
: >
: > It might not be running an "end query". =A0Reports can loop over a query
: > getting one row at a time, and in between getting each row of that query
: > it might run another query based on all the conditions set up in the
: > report. =A0The second query may or may not have any obvious connection to
: > the first query. =A0
: >
: > In report builder you should be able to see all the details in one of the
: > windows (I cant check the names right now).

: I would think the case of where Reports Builder runs the second query
: using data retuned by the first query and report conditions would be
: where the raw sql trace file on the database would be helpful since it
: will show each query ran.

Yes, the trace file is very useful in understanding what queries are being
run. In particular I have found it useful to check the ORDER BY which is
added by reports to make the break conditions work (when they don't work
as expected).
 | 
Pages: 1
Prev: Seeking PL/SQL tutorials in DVD or CD format
Next: orcle details