CE10 & Oracle Stored Procedures 6

[pandpp]

Hi there.

I have started at a new site that was on CE9 & just upgraded to CE10, on Oracle 8.1.7.

I have a need for a Stored Procedure, which is written & works.

Previously (up to CR8.5) I have created an CR Oracle ODBC connection, told it that 'Procedure Returns Results' & away I would run.

I've just been told by the person who did the install that there aren't any such CR ODBC drivers with our install.

What am I or he missing? Surely BO haven't removed the ability to use SP's on Oracle.

Are they part of the install & were missed?
Do they have to be downloaded and installed from some BO site?

All help appreciated to finish what should have already been completed.

Peter.

 

[paulmarr]

Hi pandpp!

Have a read:

http://support.businessobjects.com/library/kbase/articles/c2012126.asp

I hope this helps!

Cheers,

paulmarr

 

[pandpp]

Paul,

I found that exact KB article yesterday.

Unfortunately, if I could get to the point of setting up a CR ODBC driver, I can solve it from there. The issue is that, when you go in to setup the ODBC DSN, the CR ODBC drivers are NOT listed at all.

What I'm looking to find is where these should have been installed from, or how the person who installed them could have missed it.
There is of course the small possibility that BO have removed them, but believe that has a 0.0001% chance of being plausible.

Peter.

 

[paulmarr]

Peter,

I am using CR10 - and the ORacle ODBC driver is not on the list.

So I am assuming BO removed it and you would have to get it from your db vendor.

Cheers,

paul

 

[cmmrfrds]

The Oracle ODBC and OLEDB should be part of the Oracle client install. Check with your DBA about having them installed. In my company they only installed the OLEDB driver since they decided that OLEDB was more secure. Do you have an Oracle OLEDB provider installed. Microsoft has one for Oracle also, but I noticed it did not work when we upgraded to Crystal 10. Is there a reason you prefer the ODBC driver?

 

[pandpp]

I prefer the CR ODBC driver because Oracle by nature will NEVER return a record set from a Stored Procedure.

CR has realised this & circumvented the issue by releasing their own ODBC driver that allows CR to talk to an Oracle sP via a bi-directional CURSOR.

So, as you see it's not that I have something against the standard OLEDB driver, it's just that Oracle SP's & Crystal will NOT talk to each other without using the CR ODBC driver.

The ODBC drivers I am speaking of have NOTHING to do the Oracle Client install, since they come from the Crystal Reports install.

 

[Turkbear]

Hi,
Oracle SPs, when written to return a REF CURSOR, work in Crystal ( so Oracle will return the what amounts to a Record Set)
I always use the Oracle 'Native' driver, but it should work with Oracle's ODBC one as well.

 

[cmmrfrds]

I am giving you both a star, since my background is Sybase and SQL Server, and recently new to Oracle. I learned something from both posts. I had assumed there was no problem on receiving a recordset from an Oracle stored procedure.

 

[lyanch]

Peter,

The Crystal oracle stuff will *not* install automatically unless the oracle client is installed first. I am not sure on the newer version if it will auto install if the client is there.. anyway

reinstall (update) crystal and select the datacomponent for oracle.

As a general rule, I usually install *all* the data components because sooner or later I run into most of them and they don't take a lot of disk space. Hate having to fumble around with an install again for a new type of connection.

Lisa

 

[pandpp]

Turkbear,

Thank you.
Problem resolved by using a 'Native' driver.

Peter.

 

[Turkbear]

Hi,
Glad to help..Thanks for the star...

 

[robnmo]

In CE10 The ODBC drivers are not part of the install, they are on BO website.

http://www.businessobjects.com/products/downloadcenter/.

Here is where you will also find the new ADHOC, if you have the right license for CE.

 

[nice4]

More than curious. Is there a way that CR9 calling Oracle SP without having CR9 to prompts for parameters values ?

I have searched around and few people suggested to using sub-report and then link the static formula(defined in the main report) directly to the SP parameters (in the sub-report), but its kind of weird that I see no parameters are listed from the SP when I try to 'link' them. Does this related to ODBC drive problem or something else ? Thanks