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

[Former Member]

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:-)

[former_member11696]

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.

cr_oracle_stored_procedures.txt.zip

[Former Member]

Good Morning Don,

You are welcome for the details!

However, Our issue is not that You cannot set (change) location from an Oracle table to an Oracle stored procedure. In fact, We have developed all of Our Crystal Reports using Oracle Stored Procedures within Packages and they work wonderfully.

The issue is that if We need to make a change to the underlying Oracle Stored Procedure within the Oracle Package an attempt to update the Crystal Report by performing an Update in the Set Location function or verify database, it DOES NOT WORK We keep receiving the following erros and then We have to rewrite the report from scratch.

Data Objects cannot be opened/accessed

then

Unknown Database Connector Error

This used to work! If the Stored Procedure (within the package) or table changed, then the excellent unmapped/mapped fields window in Crystal Reports would be pop up and could be used to correct any changes. This used to work and still does with SQL Server stored procedures/tables.

This is very frustrating. It works when connected to SQL Server, switch from table to SP and unmapped fields are identifed perfectly! But not with Oracle Stored Procedures in or out of an Oracle Package.

I have been working with Crystal Reports since version 1.0 when it came bundled with VB 1.0.

Shows My age...

This is definitely a bug that We think should have time allocated by the SAP developers to correct, unless someone can provide a legitimate workaround.

We are to the point where We are going to stop using Crystal and just export to Excel or something else. We would really hate to see this happen because We really like how Crystal works!

Please let Us know if You have any additional questions or concerns or if You would like to discuss over the phone, that would be great.

We cannot say in good conscious that Your response has solved or even helped with this problem. So, We will not be able to mark this is as the "Correct Answer". Seems to Me that the answer is only correct when the problem is solved!

Have A Great Day...

Mike

C:-)

[former_member11696]

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