Skip to Content
author's profile photo Former Member
Former Member

Refresh report using "Set Database Location" OLEDB Oracle Connection to Oracle Stored Procedure within an Oracle Package DOES NOT WORK

Good Morning,

I decided to change this in Hopes of SAP doing something
about a “BUG” that I have wrestled
with on and off with the various (later) versions Crystal reports. The issue is
refreshing a Crystal Report that is bound to an Oracle Stored Procedure within
an Oracle Package. When You do this (even
if Your Stored Procedure has not changed
) You will receive the following errors
from Crystal Reports:

Data Object cannot be
opened/accessed.

After click Ok to this message box, You will receive the following
error:

Unknown Database Connector Error..

After click Ok to this message box, You are returned to the
Crystal Report will receive the following error again:

Data Object cannot be
opened/accessed.

To make this as simple as possible for anyOne to recreate these
errors, I am going to provide step by step instructions and code.

First the Oracle SQL for a Table, Standalone Oracle Stored
Procedure and Oracle Stored Procedure within a package. We need all three so We
can demonstrate that You can successfully “Set Database Location” from or to an
Oracle Table or Standalone Oracle Stored Procedure and Crystal will even
identify if there are any field changes and let You map the field changes.
However, this will not work if You try to “Set Database Location” to an Oracle
Stored Procedure within an Oracle Package. The only time that an Oracle Stored
Procedure within an Oracle Package bound to a Crystal Report works is when You
first create the Crystal Report and bind it to the Oracle Stored Procedure
within the Oracle Package.

But that is it! You cannot make any changes to the Oracle
Stored Procedure within the Oracle Package or even do a “Set Database Location”
to a table with the EXACT field names in the stored procedure. Hence BUG. I
believe that it has something to do with the way that Crystal is handling the
name of the Oracle stored procedure within the package.

TEST Table

CREATE TABLE TEST

(COLUMN1 VARCHAR(255),

COLUMN2 VARCHAR(255)

)

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

TEST Oracle
Package with Test Stored Procedure

CREATE OR REPLACE PACKAGE pkgTest IS

TYPE curCrystalTest IS REF CURSOR;

PROCEDURE ssp_Test ( cCompanyName IN CHAR

,nCallingAppId IN NUMBER

,nEntryUserId IN NUMBER

,curInOutTest IN OUT curCrystalTest );

END pkgTest;

/

CREATE OR REPLACE PACKAGE BODY pkgTest AS

PROCEDURE ssp_Test ( cCompanyName IN CHAR

,nCallingAppId IN NUMBER

,nEntryUserId IN NUMBER

,curInOutTest IN OUT curCrystalTest ) IS

BEGIN

OPEN curInOutTest FOR

SELECT

COLUMN1

,COLUMN2

FROM

TEST TEST;

END ssp_Test;

END pkgTest;

/

TEST Standalone Oracle Stored Procedure

CREATE OR REPLACE PROCEDURE ssp_Test ( cCompanyName IN CHAR

,nCallingAppId IN NUMBER

,nEntryUserId IN NUMBER

,curTest IN OUT pkgTest.curCrystalTest )

AS

BEGIN

OPEN curTest

FOR SELECT

COLUMN1

,COLUMN2

FROM TEST;

END ssp_Test;

/

Now go ahead and try creating a Crystal Report (I don’t care what version) X, XI, 2011 whatever…)
bound to the Oracle stored procedure within the Oracle package and try to change the location to

either the Test table or the standalone ssp_Test Oracle stored procedure. IT WILL NOT WORK!

Now try creating a new Crystal Report report bound to either the TEST table or the standalone ssp_Test stored
procedure and then switch to either the Test table (if You bound to the standalone stored procedure) or the ssp_Test standalone stored procedure if You initially bound the report to the Test table. Either way will work. Now try and set the location from either the Test table or standalone Oracle stored procedure to the ssp_Test Stored procedure within the Package and it FAILS!!!

Can someOne from SAP Support please Help with this.

Or at least get it on the list to be fixed in the future!!!

Please

I am willing to speak with anyOne from SAP Support if They would like more information, just let Me know.

Have A Great Day…

C:-)

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Nov 19, 2012 at 03:53 PM

    Hi Michael,

    Thank you for all of the details, nice to see...

    CR has always required an Oracle SP to be in a package. I'm attaching an older doc describing this but nothing has changed in recent versions.

    So even though you had it working when doing a set location without a package it was never supported. CR has never been able to convert a report from a Table to an SP. And more recent versions that now have Data Command Objects the same for a Table or SP to a Command.

    The problem is the fully qualified names going from a table to a SP doesn't exist within the report because everything is in one data collection in the SP, there is no unique table qualifier in an SP, CR uses the last SELECT statement in the SP which is always pointing to one fully qualified package.

    All I can suggest is to search Idea Place and then if found vote for it an if not then create one. This is where the Program Managers look for enhancement requests.

    FYI - I have been working with the PM's and various SDK Managers on this exact enhancement and at thsi time there is too much Development work to do to make the Set Location from Table to SP or Command. There is a lot of Field mapping that has to go on so that CR can fully qualify old to new field references. On first look it seems easy to do but when you have the same field name in multiple tables and now you have them aliased in the SP or Command SQL which one is CR supposed to map to?.... thus the issues around this. Easy for People to do it but not that easy when coding.

    It would be nice if it "just worked" using a "Conversion Wizard" but that is the issue. We may be able to do this where the Field Mapping UI pop's up and assume the Report Developer can do the mapping for us, but that is what they are discussing. We want to do this in both the Report Designer and at run time using our SDK's. If this happens is still on the table.... A lot of Development work is required to make this happen so it's debatable if we do it or the work around is you do it by simply recreating the report using a Command Object...

    Thank you

    Don

    PS - rename the file attached to *.zip, SCN will not allow zip files to be attached.


    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Mike,

      I've supported CR since version 4.6, not quite as long as you have... so not quite as old as you... maybe...

      I don't see noted what version of Oracle and Oracle Client or how you are connecting, CR Oracle Native, ODBC or OLE DB and who's driver you are using?

      Have you tried using a different driver to see if that works, could be a client issue?

      Open the report and go into the Report Options and check on the Verify Stored Procedure on First refresh.

      There is a known issue if the SP has a date parameter even thoguh your sample does not, the order in the Parameter collection is wrong and causes an exception in the designer, may be this is a result of that bug.

      Also, I noticed there are slight differences in the PDF I attached on creating and using SP's and how CR supports them and how they are created. Not sure if "IS" is the same as "AS", they are different words so I assume they may have slightly different case use.

      As for supporting and calling that feature is no longer available. If you would like to work with a support engineer then you need to either log a case in Service Market Place if you have a support contract or purchase a single case. If this is a bug then you will get a refund. Unfortunately paid cases must take priority over this forum. If I or one of the other reps is working with Oracle SP's I'll see if they can do a quick test to validate the issue.

      We don't have Oracle Servers running locally and the steps to get access to one for testing is long. With a case we could use your database through SAP Router...

      Thanks again

      Don

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.