Re: Crystal Reports and Oracle Stored Procedure Insert statments

On 15 Aug 2006 09:00:13 -0700, 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

So far I don't see any Oracle error mentioned, so my stab in the dark is, this a Crystal Reports issue and not an Oracle issue. You could of course avoid the globally temporary table, by putting the results in a pl/sql table, return a REF CURSOR with select table(cast(plsqltable) from dual;

Ugly, but it works

--
Sybrand Bakker, Senior Oracle DBA