Experiencing issues with upgrade to Oracle 11g

[Former Member]

We are in the process of upgrading to Oracle 11g from 10g. Now that we have our tns names pointing to the 11g environment, the reports all run fine, but when we open up Crystal and go into the Database Expert to add/delete a table, we cannot see any of the tables with the existing connection to the oracle database. I've tried using both oracle 10g and oracle 11g ODBC drivers and I'm getting the same results.

It's not that the connection is giving any error, but the only "tables" available to select from using the connection are functions and stored procedures. It's not allowing us to view any of the tables.

We checked the permissions and using any other tool to connect, it works fine, which leads me to believe that it has something to do on the Crystal side. If I create a new connection using native oracle with the same TNS string as I was using with the ODBC, it works just fine as well. I'm wondering if the RDO type ODBC is not compatible with 11g.

Has anyone ran into this before? Any suggestions? Thanks!

[Former Member · Accepted solution]

Odd is right.... After receiving your message, I found that by going into the options on the crystal connection and unchecking everything and rechecking tables, etc... If I close out of Crystal and open it back up, the connection works just fine. I'm not sure why it works but it's a good enough work around for me! I've tried it on a couple other coworkers PCs and it seems to do the trick!

Thank you!!!

[former_member11696 · Accepted solution]

Hi Jessica,

Great that worked for you, strange though you had to close the designer.... Hitting the F5 key to refresh the connection info may have done it for you also....

The reason why it works is when you make your connection to the database CR queries the DB for all tables, Views, Stored Procedures etc. and it loads the info into memory. There is a limited amount of memory CR can use and once it's full it stops adding the Table info. They are there, you just can't see them....

Because Oracle can have a huge number of Tables, SP's, Views, etc. that memory space can and quite often does fill up. It's always been this way. Other DB's it doesn't seem to be affected as much or fill up the data area as quickly.

Once your table is selected CR keeps a copy of the info in the RTP file for quick reference, which is why we try to load everything when creating the reports.

Thanks again

Don

[Former Member]

Thanks for the explanation! That makes sence... I'm not sure why we haven't come across the issue before, but now we know. Thanks again!

[Former Member]

I have uninstalled 10g thinking that was part of the issue.

A native connection works fine, but with our deployment process, we need to use an ODBC connection.

[former_member11696]

Odd.... who's ODBC driver are you using?

On your connection right click on it and select Options. Check off everything except Tables and then add a user wild card or table name wild card in the options.

Do you see them now?

Don

Edited by: Don Williams on Jun 28, 2011 1:52 PM

[Former Member]

Crystal xi r2, service pack 4.

[former_member11696]

Hi Jessica,

Try using the native Oracle server driver in CR to see it shows the fields. If it does with a new report then try set Location to the same driver.

Do you have both Oracle Clients installed on that PC? I've of problems with both, CR or Oracle mixes dependencies. You may have to remove both and install Just 11.

Also change your PATH statement so it loads the Oracle 11 \bin first.

Don

[Former Member]

Thank you for moving my thread to the correct forum. The link you attached seems to be the same issue that I'm experienceing, however, I am already using an Oracle in OraClient 11g driver in my ODBC connections. Which, again works fine with other tools, but just not with Crystal. And then when I make the connection within Crystal I'm using ODBC (RDO).

[former_member11696]

What version of Crystal Reports are you using and any patches installed?

Don

[former_member183750]

Moving thread to SAP Crystal Reports Database connectivity forum.

See if the KB [1432328 - Schema tables do not appear in the Database Expert of Crystal Reports 2008 when connecting to Oracle 11g through ODBC driver|http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_bi/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes%7B6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333433333332333333323338%7D.do] helps.

- Ludek