Connecting to Oracle DB

[cboothroyd]

I am trying to connect Crystal to an Oracle Data source. Everything works at run time (and preview) but if I try to use the Database Expert or Update Data Source it does not.

I have tried on multiple machines (including a clean build of Windows) – all without success. I have also tried using Visual Studio 2013, 2017 and 2019 - as well as Crystal 2020.

The symptoms are pretty much the same on all machines and with both the dedicated report editor and the various versions of Visual Studio.

My primary development Oracle server is version 19.x – when I try to connect to this server, the Oracle TNS name is recognized – I can tell this because if I enter the wrong password, it is rejected. However, if I enter the correct password, the application just goes into busy mode indefinitely (I have tried leaving it for over 30 minutes – but no connection is ever made).

If I try to connect to another (Oracle 12.x) server, I am able to see all of the available entities – I can even select a database view as a data source. Unfortunately, our project standard is that all reports should use stored procedures. If I try to select a stored procedure, the procedure parameters are correctly displayed BUT when I select OK, I just get an error message:

SAP Crystal Reports Active X Designer Failed to retrieve data from the database. Details: ORA-06550: lin 1, colum 16: PLS-00302: component 'SPRPTAUDITTRAIL' must be declared ORA-006650: line 1, column 8: PL/SQL: Statement ignored [Database Vendor Code: 6550]

NOTE: The stored procedure DOES exist – and it IS valid – and has been in use for several years. It runs fine in both runtime AND preview mode -- AND verify data source works fine. The user account used is also valid and has been in use for several years.

I've done extensive research on this and people commonly say that you need the 32 bit version of the Oracle runtime - which I have. On the clean build VS2013 version, I have the following:

• Visual Studio 2013 with Update 5
• SAP Crystal Reports, version for Microsoft Visual Studio - 13.0.19.2312
• SAP Crystal Reports, runtime engine for .NET Framework (32 bit) - 13.0.19.2312
• SAP Crystal Reports, runtime engine for .NET Framework (32 bit) - 13.0.19.2312
• ODTforVS2013_121025
• Oracle win32_12201_client
• TNS_ADMIN and Path variables both point to the correct folder.

SQL Developer connects to either the Oracle 19 or 12g servers without any issues

Many Thanks!

(I realize that VS2013 is a bit long in the tooth but that was the last version that I had working - and the same results happen for VS2017 and VS2019 - obviously with different versions of ODAC/CR etc).

[former_member11696 · Accepted solution]

Hi Chris,

Crystal Reports 2016 and lower and CR for VS are both 32 bit so they require the 32 bit Oracle client.

CR 2020 is 64 bit and as Dell mentioned you will need to use the 64 bit Oracle Client.

CR will not connect if the client is missing or wrong platform, it finds the Oracle Client but due to platform issues it's basically saying I don't know what to do....

Depending on the order the Oracle Client is installed depends if CR can find it and use the correct version.

I suggest uninstalling the 64 Oracle client and only install the 32 bit client.

And upgrade CR for VS to SP 30, it's full release only because we don't patch previous versions.

And since Oracle can have a huge amount of Tables once connected right click on the Connection and select Options and add filtering, either by user etc. and uncheck tables, if you are using SP's only etc. You may be running out of resources because CR will copying all of the tables into memory...

Don

[DellSC]

Unlike earlier versions of Crystal, Crystal 2020 is 64-bit. So, you need to have the 64-bit version of the Oracle client software installed in order for it to connect to Oracle. You don't mention whether you're using Oracle native connections (preferred) or ODBC, but if it's ODBC, you'll also have to create a 64-bit ODBC connection.

Also, you're using an older version (SP19) of the Crystal for VS SDK. The version you have is not compatible with VS 2017 or 2019 or with Oracle 19. The current version is SP30. You'll want to go to the Crystal for VS wiki to download the latest version. Also, you'll need to follow the info in the wiki for the SP21 release which outlines changes you'll need to make to your code for it to be compatible with the newer versions of VS.

-Dell

[cboothroyd]

Hi Dell,

Thank you for your quick response.

I am using the native connection (Oracle Server). I was pretty sure that I tried the Oracle 64 bit client with Crystal 2020 but just to be sure, I tried it again on a clean build of Windows 10. I just installed the Oracle client (from WINDOWS.X64_193000_client_home.zip), Sql Developer and Crystal 2020. The results were identical.

On the Visual Studio setups, I did match the version of CR for VS with the VS version (I have been stuck on this for over a week now - soooo many installations 😞 )

If there is anything else you can think of, please let me know. As I say - it all works except that I cannot add a new data source to a report or us the Update Data Source to change an existing report. Of course, if you want to create a new report - that pretty much leaves you stuck in the water).

Chris

[DellSC]

Are the folders to the Oracle path at the beginning of the PATH environment variable or elsewhere in it? You could try moving them around so that the 32-bit folders are at the beginning (left) of the environment variable and the 64-bit folders are next. I know this used to make a difference, but I'm not sure whether that's still the case.

I would also make sure that the tnsnames.ora and the sqlnet.ora files are the same as the ones on the machine where this was working in the past.

-Dell

[cboothroyd]

Hi Dell,

Yes, the oracle client bin directory (C:\app\bin) is in the path but I only have the 64 bit client on this machine - so nothing to swap. (I did have both on another machine but swapping didn't help on that machine).

Both of the Oracle networking files are identical to the original l working version.

Many Thanks

Chris