cancel
Showing results for 
Search instead for 
Did you mean: 

How to connect to Oracle databases

Former Member
0 Kudos

Let me apologize up front for this total newbie question. I am just getting started with Crystal Reports and I would like to know the process for connecting to Oracle databases.

I can connect to the example database Xtreme.mdb just by selecting the database files and without having the Microsoft Access database software installed.

Would I be able to do the same with an Oracle database? Would I be able to remotely develop some reports for a client where the client has the Oracle application but I would have only the database files?

Thanks,

(newbie) Loran Kary

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi there,

You need to establish an ODBC connection to get connected to your database.

Go to your Control Panel and open Administrative Tools -> Click on Data Source(ODBC). On the User DSN tab, select a driver for which you want to set up a data source by clicking on the Add button. Once done, the ODBC setup dialog box appears. Provide your database login details and clik ok..Your ODBC connection is done.

Next open your Crystal Report application(a blank report)not sure which version you are using, in my case it is Crystal Report XI Release 2. On the Database Expert, expand Create New Connection. Expand ODBC(RDO) , it will take you to the Data Source Selection. Select the datasoure name and click Next. Here, you need to give the login credential for accessing the Database. Once done, you will find the data available on the Crystal Report.

Regards

Cauvery

0 Kudos

Also note, you must have the Oracle Client installed on your local PC which has access to a Oracle Server to be able to create reports off or Oracle. In a round about way you could continue to use Access or any other DB and then your customers could map the reports to Oracle, most of the time it will work but sometimes due to field type differences between databases mapping field doesn't always work properly. Your customers would have to "fix" up your reports which is kind of messy.

Or rather than using ODBC you can Install the Oracle Client and it will allow you to connect to their DB once a VPN tunel or Remote Desktop connection is made. It's better to use the Oracle Native Client or OLE DB rather than ODBC, ODBC adds another layer on top of the client. But at the same time there are differences between the way ODBC, OLE DB and Native work, it's more of what you are used to.

CR cannot connect directly to an Oracle file, must go through a Client connection.

Thank you

Don

Former Member
0 Kudos

Don, do you have a link for the Oracle client ?

i am upgrading to a new pc. windows 7 64 bit.

there is file "win64_11gR2_client.zip" on the oracle downloads. is this what i need ?

thanks

0 Kudos

Hi John,

It should be on your Oracle CD and CR requires that you use the 32 bit client for CR Designer and if using CR for VS 2010 64 bit applications then the 64 bit client is required on the app server. If you are using CR 2011 and publishing the reports to BOE 4.0 then the 64 bit client needs to be installed on the BOE Server. BOE 4.0 is 64 bit only and 32 bit on the local PC for CR 2011 to connect.

I don't believe the Oracle Client is free to install so not sure what it is and if you'll be able to find it, legally anyways. Check Oracles Site and use what is available from them.

If you use the Data Direct CR 5.3 install then you can use the Wired Driver, it has the client built in.

Thanks

Don

Former Member
0 Kudos

Don, can you point me at the Data direct drivers ? So these will allow me to connect to ODBC ? specifically Oracle 10g db ?

thanks John

Former Member
0 Kudos

Plus if i do use the Data direct drivers, where do i place the tnsnames.ora and sqlnet.ora files ?

0 Kudos

Don't know what version you have so go to:

CR 2008 or XR R2 I believe should have them listed. You do need the one per version. If you are using older Versions of Cr the drivers were included so do a repair install and then custom and you can select the DD drivers.

Don

Former Member
0 Kudos

Don, i seem to have a problem i cannot resolve.I first loaded the 32 bit oracle client, and it installed correctly.

But when i open the windows ADMIN TOOLS / ODBC Administrator, i do not see any drivers installed, when i select "system dsn" other than SQL server.

So i loaded the data direct odbc 5.3 drivers for windows. They also installed ok, but again i do not see any drivers in the ODBC ADMIN tool ???

i am using windows 7 and have crystal 2008 version 12.3.0.601

Any ideas ? thanks for your help

0 Kudos

If you are on a Windows 64 Bit OS then go into the 32 bit Control Panel or into

\windows\syswow64\odbcadm32.exe

Don

Former Member
0 Kudos

Well Don, do i get points for answering my own question ???

In win7, if you open the ODBC ADMIN it only shows 64 bit drivers.

Of course i have downloaded 32 bit drivers for Oracle and the DataDirect 5.3

If i run c:\windows\sysWOW64\odbcad32.exe this shows a 32 bit ODBC admin windows, and all the 32 bit drivers are there !!!

very frustrating, so i just created a shortcut to c:\windows\sysWOW64\odbcad32.exe and called it "32 bit ODBC CONFIG"

However, where do i place the tnsnames/sqlnet ora files for DATADIRECT ?

Thanks John

0 Kudos

Hi John,

If the 32 bit Oracle Client is installed then in your PATH statement it should it in there and CR and the client will find your ORA files.

Are you getting a 126 error in ODBC when trying to create or test your DSN? If so then it's because the Oracle Client can't be found by ODBC.

Not sure about answering your own question, this is a Microsoft platforms issue. You should have a 32 bit Control Panel where the short cut is located.

On Windows 7 from Start and the search line simply type in ODBC and both short cuts should be listed.

I also recall someone else had the problem where the 64 bit client was installed first, to fix it they had to uninstall both 32 and 64 and then install 32 bit first....

Thanks

Don

Former Member
0 Kudos

Don, i am now ok. It was the odbc admin tool causing my problem.

I am fine using the Oracle client.

In the 32 bit odbc admin window, how do the datadirect drivers appear ,

i.e is CR nnnnnn ODBC DRIVER 5.3 a datadirect driver ?

if i use the cr oracle odbc driver 5.3 i get a dialog box which i complete, but then get a ORA-12541 TNS:NO Listerner

Thanks

0 Kudos

Hi John,

Correct, they are listed as CR.... in the ODBC Admin when adding a driver.

You still need to configure the Oracle Client. Try a re-boot, sometimes the client can't be found until a reboot is done.

Do you see OCI*.exe running in the Task Manager? Does SQLPlus work?

Try the Wired Driver, it prompt for all of the Server info.

Don

Former Member
0 Kudos

Thanks Don.

I do not see OCI.exe running in the Task Manager, but Sqlplus works fine. What is OCI.EXE ? how do i install it ?

Thanks John

0 Kudos

OCI is the Oracle Client engine. Try Modules or some other tool that can show 64 bit Processes also like Process Monitor and filter on SQLPlus, must be running of course. It may tell you what client dll it's using and if it's 32 or 64 bit.

Don

Answers (0)