Re: Crystal Reports and Oracle Stored Procedure Insert statments

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 Received on Wed Aug 16 2006 - 00:24:03 CDT