Skip to Content
0

Crystal Report using Ref Cursor OLE DB failed to retrieve

Aug 30, 2017 at 11:02 AM

142

avatar image
Former Member

Dear Experts,

Im using Visual Studio 2010 CR version 13, while trying connect Ref cusror Proc getting below attached error. Please suggest to resolve this issues.

before one year its working. now im trying to modify the script, but getting error. Please advise.

1.below Image, saved data at the time of designing its working.

2.Now im trying to modify the report once i refreshed, gettting below error.

3.Trying with new connection, can u help me to select correct provider for Ref Cursor.

below provider selection, which one i need to be select for Ref Cursor Procedure working Perfectly.

4.Is this correct provider for Ref Cursor ?

before it's only i was connected

5. Once i select the Ref Cursor Proc, check that Report preview while getting below error.

Please help me to resolve this issues.

Thanks in advance,

Mani.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Don Williams
Sep 06, 2017 at 04:24 PM
0

Hi Mani,

CR Designer needs a REF Cursor to hold the result set in so you may need to update your SP.

See this PDF in text format...

Crystal Reports
Oracle Stored Procedures
Overview
This document contains information about which database drivers support
Oracle stored procedures in Crystal Reports 9 and 10. Also, this document
contains information on how to report off an Oracle stored procedure, including
a list of minimum requirements.
NOTE A native or ODBC connection is recommended for reporting off Oracle stored procedures
for full functionality to be available.
Contents
INTRODUCTION............................................................................................2
DATABASE DRIVERS THAT SUPPORT ORACLE STORED PROCEDURES...........2
Crystal Reports 9 .........................................................................................2
Crystal Reports 10 .......................................................................................3
REQUIREMENTS OF ORACLE STORED PROCEDURES.....................................3
Strongly Bound REF CURSORs versus Weakly Bound REF CURSORs .....4
CREATING AN ORACLE STORED PROCEDURE...............................................5
Sample A: Use with ODBC and native connections in Crystal Reports ......5
Sample B: Weakly Bound REF CURSOR ....................................................6
Sample C: Stored procedure created within a package...............................7
EXECUTING AN ORACLE STORED PROCEDURE IN SQL*PLUS .......................8
CREATING A CRYSTAL REPORT OFF AN ORACLE STORED PROCEDURE.........9
FINDING MORE INFORMATION ......................................................................9
Crystal Reports Oracle Stored Procedures
2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 2
cr_oracle_stored_procedures.pdf
Introduction
This article provides information for Oracle stored procedures and Crystal
Reports (CR). You will find detailed information on the following:
• Crystal Reports 9 and 10 database drivers that support Oracle stored
procedures
• Requirements when reporting from Oracle stored procedures
• Creating Oracle stored procedures
• Executing Oracle stored procedures in SQL*Plus to troubleshoot
Database Drivers that Support Oracle Stored Procedures
Crystal Reports 9
For Crystal Reports 9, refer to Table A: Database drivers and Oracle, to
determine which database drivers can report off stored procedures in the
specified versions of Oracle.
Table A: Database drivers and Oracle
Database Drivers
(32-bit only)
Oracle
7.x
Oracle
8.0.5
Oracle
8.0.6
Oracle
8.1.x
Oracle
9.x
Native Oracle
Driver
(Crdb_oracle.dll)
No No Yes Yes Yes
CR Oracle 4.10
ODBC Driver
No No Yes Yes Yes
Oracle ODBC
Driver
No No No Yes Yes
NOTE Only the most recent version of the Oracle 8i ODBC driver are able to work with stored
procedures.
Crystal Reports Oracle Stored Procedures
2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 3
cr_oracle_stored_procedures.pdf
Crystal Reports 10
For Crystal Reports 10, refer to Table B: Database drivers and Oracle, to
determine which database drivers can report off stored procedures in the
specified versions of Oracle.
Table B: Database drivers and Oracle
Database Drivers
(32-bit only)
Oracle
7.x
Oracle
8.0.5
Oracle
8.0.6
Oracle
8.1.7
Oracle
9.x
Native Oracle
Driver
(Crdb_oracle.dll)
No No No Yes Yes
CR Oracle 4.20
ODBC Driver
No No No Yes Yes
Oracle ODBC
Driver
No No No Yes Yes
NOTE Only the most recent version of the Oracle 8i ODBC driver will be able to work with stored
procedures.
Requirements of Oracle Stored Procedures
In order for Crystal Reports to report off Oracle stored procedures, all of the
following requirements must exist.
• To use an ODBC connection to access an Oracle stored procedure, you
must create a package that defines the REF CURSOR. This REF CURSOR
must be strongly bound to a static pre-defined structure (see Strongly Bound
REF CURSORs vs Weakly Bound REF CURSORs). This package must be
created separately and before the creation of the stored procedure.
• A native connection to Oracle in Crystal Reports 9 and Crystal Reports 10
can be used to access an Oracle stored procedure that was created within a
package and also an Oracle stored procedure that references a weakly bound
REF CURSOR
• The stored procedure must have a parameter that is a REF CURSOR type.
Crystal Reports uses this parameter to access and define the result set that
the stored procedure returns.
• The REF CURSOR parameter must be defined as IN OUT (read/write
mode). After the stored procedure has opened and assigned a query to the
REF CURSOR, Crystal Reports will perform a FETCH call for every row
from the query’s result. This is why the parameter must be defined as IN
OUT.
• The parameters can only be defined as IN (input) parameters. Crystal
Reports is not designed to work with OUT parameters.
• The REF CURSOR variable must be opened and assigned its query within
the procedure.
Crystal Reports Oracle Stored Procedures
2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 4
cr_oracle_stored_procedures.pdf
• The stored procedure can only return one record set. The structure of this
record set must not changed, based on parameters.
• The stored procedure cannot call another stored procedure.
• If using an ODBC driver, it must be the CR Oracle ODBC driver installed
by Crystal Reports.
• If you are using the CR ODBC driver, verify that the option ‘Procedure
Return Results’ is selected as ‘On’ in the ODBC Driver Configuration setup
under the ‘Advanced’ tab.
• If you are using the native Oracle driver and using hard-coded date selection
within the stored procedure, the date selection must use either a string
representation format of YYYY-DD-MM (where the date field = 2004-01-
01) or the To_Date formula function with the same format specified (where
date field = To_Date(2004-01-01’,’YYYY-MM-DD’).
• Most importantly, the stored procedure must be able to execute successfully
in Oracle’s SQL *Plus utility.
If all of these requirements have been met, verify that the database driver that
you are using works with that version of Oracle.
Strongly Bound REF CURSORs versus Weakly
Bound REF CURSORs
When declaring the REF CURSOR in an Oracle package, the REF CURSOR
may be strongly bound or it may be weakly bound.
A strongly bound REF CURSOR is bound with the same structure that is used
by the outputted SELECT statement in the Oracle stored procedure.
CREATE OR REPLACE PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
END Test_Package;
/
The example code in green displays the REF CURSOR strongly bound. In this
example, the REF CURSOR is bound with the row structure of the Test_Table
table.
A weakly bound REF CURSOR is not assigned a structure in the Oracle
package.
CREATE OR REPLACE PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR;
END Test_Package;
/
Although the REF CURSOR is not assigned a structure in the Oracle package,
the Oracle stored procedure itself must return a record set with a structure that
does not change based on parameters.
Crystal Reports Oracle Stored Procedures
2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 5
cr_oracle_stored_procedures.pdf
Creating an Oracle Stored Procedure
Samples of stored procedures and their steps on how to create them are
provided. These are samples of Oracle stored procedures that will return a result
set in Crystal Reports. If these samples were executed in the Oracle connection
utility, SQL *Plus, they should run successfully.
NOTE These sample stored procedures are provided as a tip. Refer to your Oracle
documentation for more information on creating stored procedures.
Sample A: Use with ODBC and native
connections in Crystal Reports
1. Create a table.
CREATE TABLE Test_Table
(ID number(5),
Firstname varchar2(30),
Lastname varchar2(30),
Birthday date);
2. Insert values into the table.
INSERT INTO Test_Table VALUES
(1, 'Christopher', 'Jones', '01-Nov-70');
INSERT INTO Test_Table VALUES
(2, 'Maria', 'Marshall', '02-Jan-77');
INSERT INTO Test_Table VALUES
(3, 'Jonathan', 'Campbell', '09-Aug-75');
INSERT INTO Test_Table VALUES
(4, 'Julie', 'Gagnon', '23-Dec-72');
INSERT INTO Test_Table VALUES
(5, 'Daemon', 'Thompson', '11-Feb-69');
3. Create the Package.
CREATE OR REPLACE PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
END Test_Package;
/
4. Create the stored procedure.
CREATE OR REPLACE PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN Test_Table.ID%TYPE)
AS
BEGIN
Crystal Reports Oracle Stored Procedures
2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 6
cr_oracle_stored_procedures.pdf
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table
WHERE Test_Table.ID = Test_Parameter;
END Test_Procedure;
/
5. Once the stored procedure is successfully created on your Oracle database,
execute the stored procedure.
Sample B: Weakly Bound REF CURSOR
Only use this sample with a native connection to Crystal Reports 9 or later.
1. Create a table.
CREATE TABLE Test_Table
(ID number(5),
Firstname varchar2(30),
Lastname varchar2(30),
Birthday date);
2. Insert values into the table.
INSERT INTO Test_Table VALUES
(1, 'Christopher', 'Jones', '01-Nov-70');
INSERT INTO Test_Table VALUES
(2, 'Maria', 'Marshall', '02-Jan-77');
INSERT INTO Test_Table VALUES
(3, 'Jonathan', 'Campbell', '09-Aug-75');
INSERT INTO Test_Table VALUES
(4, 'Julie', 'Gagnon', '23-Dec-72');
INSERT INTO Test_Table VALUES
(5, 'Daemon', 'Thompson', '11-Feb-69');
3. Create the Package.
CREATE OR REPLACE PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR;
END Test_Package;
/
4. Create the stored procedure.
CREATE OR REPLACE PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN Test_Table.ID%TYPE)
AS
BEGIN
Crystal Reports Oracle Stored Procedures
2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 7
cr_oracle_stored_procedures.pdf
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table
WHERE Test_Table.ID = Test_Parameter;
END Test_Procedure;
/
5. Once the stored procedure is successfully created on your Oracle database,
execute the stored procedure.
Sample C: Stored procedure created within a
package
Only use this sample with a native connection to Crystal Reports 9 or later.
1. Create a table.
CREATE TABLE Test_Table
(ID number(5),
Firstname varchar2(30),
Lastname varchar2(30),
Birthday date);
2. Insert values into the table.
INSERT INTO Test_Table VALUES
(1, 'Christopher', 'Jones', '01-Nov-70');
INSERT INTO Test_Table VALUES
(2, 'Maria', 'Marshall', '02-Jan-77');
INSERT INTO Test_Table VALUES
(3, 'Jonathan', 'Campbell', '09-Aug-75');
INSERT INTO Test_Table VALUES
(4, 'Julie', 'Gagnon', '23-Dec-72');
INSERT INTO Test_Table VALUES
(5, 'Daemon', 'Thompson', '11-Feb-69');
3. Create the Package.
CREATE OR REPLACE PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Type,
Test_Parameter IN Test_Table.ID%TYPE
);
END Test_Package;
/
Crystal Reports Oracle Stored Procedures
2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 8
cr_oracle_stored_procedures.pdf
4. Create the Package Body (With The Code For The Stored Procedure).
CREATE OR REPLACE PACKAGE BODY Test_Package
AS
PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Type,
Test_Parameter IN Test_Table.ID%TYPE
) IS
BEGIN
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table
WHERE Test_Table.ID = Test_Parameter;
END Test_Procedure;
END Test_Package;
/
5. Once the stored procedure is successfully created on your Oracle database,
execute the stored procedure.
Executing an Oracle Stored Procedure in SQL*Plus
The following script executes the sample stored procedures created earlier in
this document. This script runs in Oracle's native connection utility, SQL*Plus.
Remember to change the <parameter value> to a valid parameter value (a
number between 1 and 5).
SET SERVEROUTPUT ON
declare
test_cursor test_package.test_type;
resultset test_cursor%rowtype;
begin
test_procedure(test_cursor, <parameter value>);
if not test_cursor%isopen then
dbms_output.put_line('the cursor is not open');
else
dbms_output.put_line('the cursor is open');
end if;
fetch test_cursor into resultset;
while test_cursor%found loop
dbms_output.put_line(resultset.ID);
dbms_output.put_line(resultset.Firstname);
dbms_output.put_line(resultset.Lastname);
dbms_output.put_line(resultset.Birthday);
Crystal Reports Oracle Stored Procedures
2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 9
cr_oracle_stored_procedures.pdf
www.businessobjects.com
The Business Objects product and technology are protected by US patent numbers 5,555,403; 6,247,008;
6,578,027; 6,490,593; and 6,289,352. The Business Objects logo, the Business Objects tagline, BusinessObjects,
BusinessObjects Broadcast Agent, BusinessQuery, Crystal Analysis, Crystal Analysis Holos, Crystal Applications,
Crystal Enterprise, Crystal Info, Crystal Reports, Rapid Mart, and WebIntelligence are trademarks or registered
trademarks of Business Objects SA in the United States and/or other countries. Various product and service
names referenced herein may be trademarks of Business Objects SA. All other company, product, or brand
names mentioned herein, may be the trademarks of their respective owners. Specifications subject to change
without notice. Not responsible for errors or omissions. Copyright © 2004 Business Objects SA. All rights reserved.
fetch test_cursor into resultset;
end loop;
end;
/
NOTE If the script is executing correctly but you do not see any results displayed on the screen,
enter SET SERVEROUTPUT ON at the SQL> prompt to display results to the screen.
Creating a Crystal Report off an Oracle Stored Procedure
These steps are based on Crystal Reports 9 and 10. However, they may be
applicable to other versions.
1. In Crystal Reports, click the File menu, and then click New.
2. Click As a Blank Report, and then click OK.
3. In the Database Expert dialog box, find your data source. Double-click on
the data source and you will see a list of the available Schemas, choose the
Schema that you want and then you will see the Stored Procedures node.
Double-click on that and you will see the list of stored procedures.
4. Move the procedure(s) you want to report off into the Selected Tables box.
If the stored procedure has a parameter you will be prompted to enter a
value at this point.
5. Click OK to close the Enter Parameter Values dialog box, and then click
OK to close the Database Expert.
You can now design a Crystal report off an Oracle Stored Procedure.
NOTE If you do not see the Stored Procedures node in the Database Expert do the following. On
the File menu, click Options. On the Database tab, under the Show section, select the
Stored Procedures check box.
Finding More Information
For more information regarding the creation of Oracle Stored procedures, refer
to www.oracle.com.

Or try searching for it using Google.

Don

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Aug 30, 2017 at 09:52 PM
0

Microsoft's Oracle Driver only supported Oracle 7, you need to update to a current Oracle OLE DB Provider.

TO be supported be sure to get the latest CR for Runtime:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

Don

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello Don,

thanks for your reply, Currently I'm using VS 2010 CR latest version (CRforVS_13_0_20) and 11g Client ODTwithODAC112021, Oracle Provider for OLE DB (11.2.0) getting the below error,

did i select any wrong provider? please let me know to clear this one...

How to update current Oracle OLE DB Provider ?

(Currently im using my local system Oracle 11g client ODTwithODAC112021)

Please suggest me to update which one below,

11g Client,

12c Client,

Thanks in advance,

Mani

msdaora.png (38.7 kB)
msprovider.png (123.2 kB)
0
Don Williams
Aug 31, 2017 at 08:30 PM
0

Yes, you are selecting MS' Oracle provider,look at the last one listed in the screen shot, that's the one you need to use.

Don

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Don,

As per your comment, i have selected the 'Oracle Provider for OLE DB' and select the listed procedure to design the report, but getting below error.i can't to Move selected tables space.

Suppose if i select the MS OLE DB provider for Oracle, i can select and use (before that i used to design & display the report working as expected but now its not working).

Thanks in advance

Regards,

Mani

provider.png (96.3 kB)
pro-err.png (106.1 kB)
0