Crystal 8.5; Crysta Enterprise; Oracle stored procedures

[guptaj]

I am having trouble with one of my Crystal report which is getting data from a stored procedure thru ref cursor.

When a odbc driver(pdsodbc.dll) is used, the report worked fine on my desktp. When I put it on Crysyal enterprise it gives me dll error.

Some one suggested me instead of odbc driver, connect directly to the oracle server. When I tried that one, I can't see the stored procedures in the drop down.

I checked with dba, the procedure has execute permission, still I can't see them.

Can somebody tell me, how I can get to see the stored procedure when connected thru direct link(/pdsora7.dll)?

 

[synapsevampire]

When the ODBC is set up on the server, make sure that they use the Crystal supplied ODBC for Oracle driver, not Oracles, and make sure that the "procedure returns results" checkbox is enabled on the driver.

-k

 

[Turkbear]

Hi,
( Version helps insur ebeter answers)
You should see the SP with a Native Oracle connection, if:
1: The username/password used to connect has permision to execute the SP and
2:If CR10, You click on the Schema..Stored Procedures heading in the Database Expert or
If 8.5, that the stored procedures' option is checked in the options shown

When publishing the report to CE, be sure to use the same username/password ( or another one with the needed rights) and be sure entries in the tnsnames.ora file on the CE server are the same as the one on your desktop..





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

 

[guptaj]

Thanks guys,

I guess I found what it is. What I found on the web was use an IN/OUT parameter which associates to a ref cursor. When it is a IN/OUT parameter, Crystal prmpts for something to input for the ref cursor. I got annoyed with it and made it only an OUT parameter, and it worked like charm on the desktop (with odbc dll) and blew me when it was put on the enterprise.
And as long as it was an OUT parameter, the darn stored procedure doesn't display it when a native Oracle driver is used.
Well, reluctantly I made it back to IN/OUT parameter and boom, it started appearing in the Crystal thru Oracle native driver. It worked fine on both desktop version and enterprise. Though it asks for the ref cursor aprameter needs to be entered, it really doesn't matter what u enter.. Though u look stupid to the users. Well, Crystal itself is a riduculus product for designing(I like its export features, developing is not fun though).
The reason why elaborated is, when I was looking on the web for the Crystal problem, which I got, I see the same type question unanswered on all the usergroups.