Skip to Content
0
Former Member
Nov 08, 2012 at 03:18 PM

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

133 Views

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