Oracle stored procedure and crystal reports 9.0

[MrHelpMe]

Hello experts,

I have sent an email to the Oracle 9i forum and received an answer saying that the issue might be related to crystal reports so I need all the experts advice. I have created a very simple Oracle Stored procedure(I am trying to learn oracle 9.2) and what I found was that crystal is prompting me for a value. I am not sure what value should be inputed. I just wish to return the results of the select. I tried all type IN/OUT/In Out and still nothing. Any ideas why I cannot return any info. Thanks everyone. I have applied all references made in the scr_oracle_stored_procedures.pdf and still did not obtain what I needed. Thanks again


CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE usp_SelectGroups (results_cursor IN OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY GroupsPackage
AS
PROCEDURE usp_SelectGroups (results_cursor IN OUT CURSOR_TYPE)
AS
BEGIN
OPEN results_cursor FOR
SELECT Entry_id
FROM shr_people
ORDER BY Entry_id;
END;
END;
/

Using crystal report 9.2.2.634.

 

[MrHelpMe]

Sorry guys I figured it out. I had suspicion it was related to the oracle driver so looking at the cr_oracle_stored_procedures.pdf they confirmed to use the crystal for oracle ODBC driver. So I setup another DSN using the Crystal Oracle ODBC Driver 4.10 instead of the Oracle in OraHome92 driver. Please correct me if I am worng or whether I should be using another driver or if their is an updated driver that will function more efficiently with oracle. Thanks again.

 

[synapsevampire]

You are correct to ONLY use the Crystal supplied ODBC driver when using Oracle, and make sure that you turn on the option for procedure returns results.

Better yet, go with the Crystal supplied native connectivity, it's much faster. You'll see it listed under Oracle Server.

-k

 

[MrHelpMe]

Thanks for the reply Synapsevampire,

When you say the crystal supplied native connectivity and that it's listed are you talking about the Microsoft ODBC for Oracle version 2.575.1117.00 because I do not see any others besides the one's I listed. Their is the SQL Native Client but I'm assuming that is for SQL Server. Sorry can you clarify because I would love to take advantage of performance gains and speed. Thanks Synapsevampire.

 

[synapsevampire]

Nooo, it ISN'T ODBC.

Do not look under ODBC, it's a seperate data source.

You initial post says Oracle 9, now you list something about a 2.X version???

Your issue may be that your Oracle doesn't work correctly for the database type. Which Oracle client do you have installed?

Also make sure that the Oracle native connectivity was installed with Crystal.

Check out the BO whitepaper:

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

-k

 

[MrHelpMe]

Ah yes,

Now I understand Synapsevapire. I'm not sure where my head is today. You are referring to the oracle server(create New Connection) part in crystal reports directly bypassing the ODBC connection. Now I understand. I had to install the drivers from the crystal CD inorder to get this functionality but all is good. Couple more questions though, does the native driver have any issues that I should be aware of? Would you recommend always using this driver as opposed to ODBC. Which one is safer? Thanks again Synapsevampire you've helped me out a lot.

 

[Turkbear]

Hi,
To add my 2 cents:

Always use the Oracle Native Driver unless some special circumstance prevents it ( I have not found any yet)..

ODBC ( except for Crystal's latest wire-protocol versions) has additional overhead that can slow things down and can cause hard to debug problems..

The Oracle SqlNet drivers are what Oracle expects to connect to it and 'knows' what to do with the request.
It also means that to add a new database instance, you only need to edit the tnsnames.ora files and not set up a new DSN.
In addition, anyone with an Oracle client and correct tnsnames.ora file can run the report without having to create the identical DSN to the one used in a report..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

I always use and suggest native connectivity.

There were some issues with native and odbc in the past (CR 8.5 and below), but I wouldn't really fear either these days on that level.

Glad that it helped, it's the first thing I correct at new contracts.

-k