Re: Crystal Reports and Oracle Stored Procedure Insert statments

On 15 Aug 2006 09:00:13 -0700, Aaron.Kay@xxxxxxxx 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
.

Relevant Pages
Loading