Crystal Report for VS2013, data connection for Oracle

[Former Member]

Hello,

The default data connection for the reports are Microsoft OLE DB Provider for Oracle, which is discontinued by MS, and no 64bit verison.

May i know how to enable oracle driver for <Crystal Report for VS 2013>, it seems that there is no such option for selection,

even, I have set PATH to %ORACLE_HOME%\instant client, pointing to ORAOCI*.dll or have install ODAC, it is no use.

Since i has the following error,

Logon failed.
Details:
ADO Error Code: 0x80004005
Source: Microsoft OLE DB Provider for Oracle
Description: Oracle client and networking components were not found.
These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation

Platform used:
Windows 2012R2 64 bit
Oracle client 64 bit 12c Release 1
Visual studio 2013
Crystal report for VS 2013(x64) version 13.0.2

so, i has to change to Oracle Driver, but there is no such option provided. thank you for your help.

[former_member11696 · Accepted solution]

Hi Andrew,

Moved to .NET SDK forum.

I'm amazed you had that MS Oracle driver working for all these years. It actually only supports up to Oracle 7

CR does not install Client drivers so what you simply need to do is install the Oracle Client which I believe also installs the OLE DB Provider and use that as the data source.

Be aware there are issues with Oracles OLE DB Provider, it seems not to support Stored Procedures.

So if you can I suggest converting your reports to use Oracles ODBC driver.

Search for "replaceConnection" and you'll find links to KBA's on how to use that API and a test app that will generate the code to use for you.

Also remember to set the Subreport log on info also.

Don

[Former Member · Accepted solution]

Hello Don,

Thanks for your replies.

I install OracleClient12cR1 (64bits), and register the OraOLEDB12 (Oracle Provider for OLEDB), of which the Oracle Provider do not show in the Crystal Report.

I do a sample on Windows 7 64bits client machine, and register Oracle11gR2 (32bits), It can be located by Crystal Report.

I am curious if the Crystal report supports 32bits only.

Andrew

[DellSC · Accepted solution]

The Crystal Reports design tool and BusinessObjects 4.x only support 32-bit connections for reports.  If you have written a .NET application using Visual Studio, then there is a 64-bit runtime available.

-Dell

[former_member11696 · Accepted solution]

Oh and Oracle 12c is not supported until SP 14 so be sure to use the latest Patch for CR for VS.

And the Assembly version of CR for VS will always show as 13.0.2000.x. Need the actual version of the dll, you can get that from Programs and feature.

Don

[Former Member · Accepted solution]

Hello,

I install SP14 (CR for VS) with 64bits runtime, and It doesn't show the option "Oracle Provider for OLEDB" in Database Expert of "Crystal Report for Visual Studio".

I also try to connect with ODBC 64bits driver, It prompt the following error.

So, Is there no way to use 64bits CR? Does Crytal Report 2013 solve this problem? Thank you.

[DellSC · Accepted solution]

Visual Studio is 32-bit and requires a 32-bit client.  You'll be able to use the 64-bit client when you deploy your application.  Also, I would use the Native Oracle connection rather than OLE DB or ODBC - it's more reliable.

-Dell

[Former Member · Accepted solution]

Oh, thanks,

May i know how to design the report, as the data connection object is not yet added in CR.

[DellSC · Accepted solution]

You need to install the 32-bit Oracle client on your workstation (you can install the 32-bit and 64-bit clients side-by-side, just make sure they use the same TNSNames.ora.)  If that doesn't make the Oracle Client appear in Crystal, then I would uninstall and reinstall Crystal for VS, which would then pick up that the client software is available.

-Dell

Message was edited by: Dell Stinnett-Christy

[former_member11696 · Accepted solution]

Also note that Oracles OLE DB Provider has problems with Stored Procedures so as Dell suggested use the Native Oracle or ODBC only.

Don

[Former Member · Accepted solution]

Hi Dell,

Notes with thanks.

In conclusion, Visual studio, CR for VS and Crystal Reports Design Tools for 2013 is 32bits application, so the connection required is 32bit ODBC or Native Oracle Connection.

Thus, i develop the CR in 32bits platform, after that, i move the report files to the platform with 64bits runtime. However, the data connection inside the report files are refer to 32bits ODBC DSN, Is it fine when i switching to 64bits ODBC DSN?

Also, Do the Native Oracle means ODP.NET connecting to OracleClient?

Thank you.

[DellSC · Accepted solution]

If you make sure the name of the ODBC connection is exactly the same as the name of the 32-bit connection, you shouldn't have any problems.

-Dell

[Former Member · Accepted solution]

Thank you very much. It works.

May i know how to identify if it is using native client? I just saw the data connection type as "Oracle Server" in CR' Database Expert.

I do some research on ODP.NET, some post states, it fit the dataset to CR to display the content.

May i know if native client is cross bit platform and Can CR directly connect to server?

[former_member11696 · Accepted solution]

Hi Andrew,

Same for Cr's Oracle native client. All that is required is the Oracle 64 bit client be available also.

You can check which one your report is using by going into the Set Location menu option and expanding the connection info:

Oracle Server is CR's Native Oracle driver. In code you can get the dll used and it will be crdb_oracle.dll

And all you have to do once you manually update the reports to make the connection in code is change the Server name from your DSN to the Oracle Server name/instance.

Don

[Former Member · Accepted solution]

Thank you Don,

Does the CR create in 32bit native client environment which can be used in 64bits native client environment? Does it "auto recognize" in 64bits oracle server environment?

[DellSC · Accepted solution]

Either the 32-bit client or the 64-bit client can connect to a 64-bit server.  The "bit-ness" of the client that you need is based on whether you application is 32-bit or 64-bit - it has nothing to do with the whether the server it's running on or the database is 32-bit or 64-bit.

-Dell

[Former Member · Accepted solution]

Oh yes, thank you for your reminders.

I wanna know if the application and cr is designed in 32bit enviroment, and then recompile at 64bit environment, will the "oracle server" / native client reference be updated to 64bits also?

By the way, i tested on native client to connect to DB by executing the SP, it return ORA-00900 invalid statement, may it be sp calling error?

I tried two syntax:

{CALL upkg.sample(...) } / CALL upkg.sample(...)
execute upkg.sample(...)

exec

[former_member11696 · Accepted solution]

The 64 bit CR database runtime will be used when the app is running in 64 bit mode. You don't need to do anything other than compile the app in 64 bit mode.

As for the Oracle SP execution line you cannot alter that in CR Designer so all you simply need to do is update the connection properties as I noted previously and CR will update the SQL it sends to the Server.

[Former Member · Accepted solution]

Hello Don,

Thank you for your help.

I tried:

     - set data location from "ODBC" to "Oracle Server": it returns ORA-00911: invalid character

     - set data location from "MS OLEDB" to "Oracle Server": it returns ORA-00900 invalid sql statement.

     - expand Database Expert > My Connection > Oracle Server > Select the schema > Qualifiers > move the SP to Right handside, it might cause error.

However, a simple SQL statement works well with "Oracle Server":

"select count(*) from sample"

and do the following test:

execute upkg_sample.sp_sample( {?in_id}, {?in_sys_id},

{?in_abbr}, {?in_name},

{?in_hic}, {?in_brc},

{?in_addr}, {?in_active},

{?in_order_by_field}, {?in_display_order}, '0','1' )

OR

exec user.upkg_sample.sp_sample( '{?in_id}', '{?in_sys_id}',

'{?in_abbr}', '{?in_name}',

'{?in_hic}', '{?in_brc}',

'{?in_addr}', '{?in_active}',

'{?in_order_by_field}', '{?in_display_order}', '0','1' )

According to this articule: http://www.sdn.sap.com/irj/boc/go/portal/prtroot/docs/library/uuid/1084c536-711e-2b10-e48a-924a60745...

CR with Oracle Native Server is possible, but i don't know why the SP list is falling into Qualifiers list.

[former_member11696 · Accepted solution]

Hi Andrew,

Are you using a Command Object and pasting in the SQL above?

If so it explains why you may be getting a SQL error.

See this article on how to create/use Oracle Stored Procedures in CR:

http://scn.sap.com/docs/DOC-21853

Then try this, create a new report and log onto your DB. From the Database wizard expand the Oracle connection and browse the list for your Stored Procedure. Select it and then expand and drop a few fields onto the report and preview.

Do you see data now?

Don

[Former Member · Accepted solution]

Hello Don,

I finally solve the problem, as native client fit null value on SP in design, which triggers program bugs in SP of Qualifiers.

[former_member11696 · Accepted solution]

CR does not create environment info, it saves the connection info in the report, so if you move to 64 bit client CR still looks for that environment, it should be the same as the 32 bit environment.

Only requirement is CR can find and access either clients.

Don