AaronK wrote:
> DA Morgan wrote:
>> Aaron.Kay_at_USAA.com wrote:
>>> I'm assigned to improve performance on a stored procedure that is
>>> called by Crystal Reports. The existing PL/SQL block is large and
>>> contains many instances of two sub-queries. My plan was to run these
>>> repeated queries just one time, inserting the rows into two Global
>>> Temporary Tables. My code works until I put an insert statement in the
>>> stored procedure. The Crystal Report returns 'Failed to open a rowset.
>>> File <filename.rpt>. [On Cache/Page Server:
>>> <myServerName>.pageserver]'. When I comment out the insert statements
>>> and use the inline statements in the final select into cursor, the
>>> report is generated.
>>>
>>> The report uses the same schema for logon as the procedure was created
>>> on. I'm using Oracle 9i, Crystal Reports 10, ODBC connection. Are
>>> there any settings that need to be changed to allow the insert into
>>> global_temp_table statement to work?
>>>
>>> Thanks,
>>> Aaron
>> You can do inserts in stored procedures called by Oracle but the insert,
>> itself, is not a good idea. Look at writing queries with the WITH clause.
>>
>> www.psoug.org
>> click on Morgan's Library
>> click on WITH Clause
>>
>> But stop using the ODBC driver to connect: Use Crystal's native Oracle
>> driver.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
>
> Daniel,
>
> Thanks for your suggestions. The WITH Clause worked really well...
> until I ran the procedure through crystal reports. Here's my basic
> syntax:
>
> OPEN Cursor1 FOR
> select * from (
> WITH sub1 as (select ...),
> sub2 as (select ...)
> select ..........<very long>....
> );
>
> Without the 'select * from ()' wrapper, Crystal Reports wouldn't run.
> My sub1 returns less rows than sub2, but is a more complicated query.
> I used each of the subs one time a piece at first to test. It runs.
> Then I included another sub1, and it blew up. Crystal wouldn't grab
> the data. So I take out my 2nd instance of Sub1, put in another
> instance of Sub2 and it works. I replaced all my instances of
> Sub2-like code, and the report ran, and it ran faster than without the
> WITH clause. So... sub2 is good. I tried taking sub2 completely out,
> but as soon as sub1 was referenced a 2nd time, it blew up.
>
> Have you any ideas why my sub1 (which only contains 12 rows) would only
> be allowed to appear in the larger select once?
>
> Thank for the help,
> Aaron
There is something I think Sybrand mentioned (perhaps someone else) that
I should have mentioned. Crystal can only work with Oracle via REF
CURSORS. If you decide to use the WITH ... use it in opening a ref cursor.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Aug 16 2006 - 01:18:03 CDT
