Re: Crystal Reports and Oracle Stored Procedure Insert statments

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