AaronK wrote:
> 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
>> 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_at_x.washington.edu
>> (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
Best practice with Crystal and Oracle is to never use ODBC. Crystal
has a native driver and it is far superior.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Wed Sep 06 2006 - 13:17:59 CDT
