Re: Crystal Reports and Oracle Stored Procedure Insert statments

DA Morgan wrote:
AaronK wrote:
DA Morgan wrote:
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
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@xxxxxxxxxxxxxxxx
(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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

I found what caused my problem. I was using an ODBC driver to connect
from the Crystal Report to the Oracle 9i database. The reason is that
then when I moved the report over to the production server, it would
use the server's ODBC connection to the production database. However,
the ODBC driver for Crystal Reports 10 and Oracle 9i for some reason
will not allow insert statements (even into a temp table). Once I
changed Crystal Reports to use the 'Oracle Server' driver connecting
directly to the DB without a pass through ODBC, my statement with
inserts into temp tables worked fine.

Hope this helps anyone with the same issue.

Aaron

.

Relevant Pages
Loading