How and when would I use Database Expert Connection to Oracle Server

[Former Member]

We have a core application that has been setup to use Oracle Provider for OLEDB.  I can't get Ref Cursor (strong named or weak named) to return data with this connection.

I tried the Oracle Server connection and I'm able to return data on one of my databases using a Ref Cursor not the other.  One database display the data in the viewer, the other gets failed to retrieve data from the database vendor code 6550.

I have access to Crystal Reports XI, Visual Studio 2010 or Visual Studio 2013.  We have Runtime for 32 bit and 64 bit installed.

The core application won't allow us to use Microsoft OLEDB for Oracle.

Looking for options:

• How to return data from complex stored procedure in Oracle, using the Oracle Provider for OLEDB.  Does the stored procedure have to be included in the package or can it be stand alone?
• How and when would I use Oracle Server in the Database Expert, and how would it be deployed when a Crystal Report has been created using it for the connection?
• any other option that would allow me to retrieve data with either connection?

Thank you,

Bruce Edgar

bruce.edgar@fisglobal.com

[DellSC · Accepted solution]

Crystal XI is old and went out of support over 5 years ago.  Also, it is not certified to work with any version of Visual Studio other than 2005 or 2008.

So, the first thing you need to do if you're working in VS, is make sure that you have the latest version (SP 16) of the SAP Crystal for Visual Studio SDK.  You can download it here:  .  Make sure that you uninstall the Crystal XI SDK (you can leave the report designer stuff, just do a modify install and take out the SDK stuff) and then follow the instructions at the link above to get the correct file for integration into VS.

To get a result from a stored procedure from Oracle, I think you need to use a "Native Oracle" connection (which is going to be much faster and more reliable than OLEDB...)  Also, the MS OLEDB driver for Oracle is no longer supported in the newer versions of Crystal and its SDK.  For Oracle, the stored proc must have an "In Out" parameter that returns the refcursor - I usually name it "result" and have no issues getting Crystal to "see" it.  However, the refcursor should be strongly typed - Crystal cannot read data from a stored proc where the result set may have different sets of fields depending on the parameters that are used - I know this because I've tried to do it!

-Dell

[Former Member · Accepted solution]

Thanks Dell

We're using Crystal Reports XI or Visual Studio to create the .rpt file.  I'm currently trying to create this with CR XI. I am making the assumption (Assumption the Mother of ALL Screw Ups) that Oracle Server is Native Oracle.

Here's a screen shot from CR XI of the connection.

Using this connection, I get results from one of the databases; the other database gets failed to retrieve data vendor code 6550.

I haven't been able to determine what is different about the 2 databases and/or the servers the Web App is running from.  The TNSNames are the same in both servers.

Here's the simple stored procedure I put together that works on one database but not the other:

The main issue is Oracle Provider for OLEDB doesn't allow us to return data from the Ref Cursor, strange and I'm not sure why, you'd think any of the Oracle connections would work with Ref Cursors.

Thank you Kindly for your reply

[DellSC · Accepted solution]

One of the things you need to be careful about when working with both and old (XI) and a new (CR for VS) version of the software is that the versions of the database clients that they will work with are different.  So, if you're working with, for example, Oracle 11i, CR for VS will be completely compatible with it but Crystal XI may not.  This particularly holds true for the OLEDB type connections, and I know for sure that the newer version of Crystal will NOT work with the Microsoft OLE DB provider for Oracle, but I'm not sure that Crystal XI will work with Oracle's version of the provider.

Something else to remember is that OLEDB is a "standard" type of database connection that may be applicable to many types of databases.  The standard may not have any way of defining what a RefCursor is and that's why you can't get it to return data.

-Dell

[Former Member · Accepted solution]

Thank you again Dell

Maybe I'll try editing the report with Visual Studio 2013 and see what happens, surely wouldn't hurt.