| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Crystal Reports and Oracle Stored Procedure Insert statments
DA Morgan wrote:
> AaronK wrote:
> > 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
>
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 Received on Wed Sep 06 2006 - 09:54:00 CDT
 |
 |