Link to home
Start Free TrialLog in
Avatar of rambosh
rambosh

asked on

set location for oracle stored procedure using crystal 8.0

I'm trying to change the location of an oracle stored procedure using Crystal Reports 8.0. I cannot see the stored procedure when I connect to the new server. The dba has set up the package and procedures as public synonyms. He has granted execute rights to the procedure(s) also, but I still cannot see them. Any ideas what else has to be set in order to attach to the new server? On the original test server, I can attach to the procedure as both a schema owner and a user, but I can't find the differences in the user on the original test server and the new server.
Avatar of Mike McCracken
Mike McCracken

Avatar of rambosh

ASKER

I already have that white paper. That's how I created the procedure to begin with. The program works great on the 1st test server. Now I've moved the package and procedure to the next test server, but I can't see the stored procedure in the list of available items when I try to change the location. I've tried to connect to the synonym and I get an error something like "No fields available". I found a paper that stated to uncheck the 'Use current schema for SQL procedures' in the ODBC administrator. All that does is lock up my session of Crystal. I'm really stuck unless I can figure out how to connect to the stored procedure. What kind of rights does the dba have to give to the user id? It currently only has execute rights. The package and procedure are set up as public synonyms.
ASKER CERTIFIED SOLUTION
Avatar of AnnetteHarper
AnnetteHarper

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Have you checked the option in Crystal to show stored procedures?

Open Crystal
Click FILE --> OPTIONS
In CR8 it is on the DATABASE tab

mlmcc
Avatar of rambosh

ASKER

Yes, I have stored procedures checked. The original report is based on a stored procedure. I'm using Cr driver for Oracle. When I unchecked the "Use current schema for stored procedures" in the odbc connection, it looked for over 2 hours, so I cancelled out. I'm going to try something different today.
Avatar of rambosh

ASKER

Sorry for the delay in response. I got caught up on something else and this got put on the back burner. I tried to uncheck the "Use current schema for stored procedures" in the odbc. When I tried to connect the report to it, it processed for about 3 hours and I couldn't tell if it was really doing something or not. I also tried to change it in the code in my VB app. That didn't work either. Could the time it's taking to process depend on how many schemas are on the server? I don't know how many are on the server I am trying to attach to, but could that be a factor?
Can you try switching to the native driver? With the native driver, stored procedures in other schemas should be displayed with no problem. If they are, and you can find and use the one you want, then we'll know that it is just the bug with the ODBC driver and "Use current schema for stored procedures."

If you can't try the native driver, then try putting the procedure call into a SQL Command. In the SQL Command use the following syntax. Create any necessary parameters first by choosing the Create button in the SQL Command dialog box.

{Call ProcSchema.ProcPackage.ProcName(Param1, Param2, ...)}

Do not list the REF CURSOR parameter.
Avatar of rambosh

ASKER

If I run the stored proc from SQL command (I'm assuming this is the oracle prompt?), it's not going to do me any good. The data doesn't write to a table, it returns to a crystal report. I can run/debug the stored produre from PLSQL Developer fine. It's not an issue with the stored procedure. It's an issue with Crystal reports. I guess the underlying issue is if I can attach to a stored procedure if I am not the schema owner. The problem I have is that on my original server, I have the procedure under schema owner xxx. I can still access and attach to it being user yyy. I don't know what needs to be set on the oracle server in order for me to do it. Do I need direct access rights to the procedure? My middle tier server is in a different location and we don't have access to the server as schema owners.
I'm sorry, I didn't realize that you said Crystal Reports 8. SQL Commands are a new feature in Crystal Reports 9.

The only thing that needs to be set on the oracle server is granting the execute privilege to the report user's id. This is a problem with Crystal Reports and the ODBC driver, where you cannot see procedures in other schemas without unchecking the "Use current schema for stored procedures," but unchecking it results in incredibly poor performance.

Try the native driver. I don't have CR 8 installed, but in 9, you have to go to the "Oracle Server" folder rather than the ODBC folder to get to it. (Assuming that you installed it when you installed Crystal.) Using the native driver, all stored procedures that you have privileges for will be displayed and usable (unless there's an issue withe CR 8 that I don't remember).
Avatar of rambosh

ASKER

I finally got them connected. You were right. I just had to wait a long time. I tried to connect it on a company holiday when there weren't a lot of people here and it connected. When I connected the next server, I did it over night and when I came in the morning, it was ready to attach. I must just have a large server and it's scanning through all of the schema owners to see what rights the particular user has. Thanks again for your help.
I'm glad you finally got the ODBC driver to list your procedures. However, I really think that you should try the native driver since it does not make you wait at all, but lists them right away. Is there some reason that you MUST use ODBC?
Avatar of rambosh

ASKER

Yes. I cannot connect directly to the database. I think when I tried to use the oracle driver, I got errors when trying to attach. There is something quirky with Crystal and the db drivers. When you read the white paper on stored procedures, it recommends that you use the crystal driver or you may get inaccurate results. If I use the native oracle driver, what am I supposed to enter for the result set parameter?
You don't need any more access to the database to use the native driver than you do for ODBC. Everything that is required for it, is also required for the ODBC driver, namely the installation of the Oracle client. It will also save you one layer of translation, the ODBC layer, and of course, this long wait to see procedures in other schemas.

To use the native driver, you must have the Oracle client properly installed and you must install the Crystal database option called "Oracle Server." You do not need to make any changes to your stored procedure or setup a DSN. If you have the Oracle Server option installed, try it and let me know what errors you're getting. You just need the same information that you used to setup the ODBC DSN.

As for the Crystal whitepaper, it may be talking about other ODBC drivers. For instance, the Oracle supplied ODBC driver and the Microsoft ODBC driver for Oracle both have significant problems. I've had no problems with the native driver. If you use stored procedures a lot or want to see a comparison of connectivity options, see my book "Crystal Reports 9 on Oracle" from Osborne/McGraw-Hill.
Avatar of rambosh

ASKER

I have the oracle client installed on my machine. Where would I get the crystal database option "Oracle Server"? Is this for crystal 8 or crystal 9. I'm using crystal 8. When I try to attach the report using an oracle native odbc, it seem to be attaching. I see the stored procedure, click on it, enter the parameter values and then it just hangs. It's not even an option in the task manager. I don't think it's any better than attaching using cr oracle driver.
The Oracle Server option exists in both Crystal 8 and Crystal 9. If you don't have it showing up in your Database Expert, then you need to modify the Crystal installation to add it. This is different from choosing the ODBC folder and then connecting to an Oracle DSN. If you need to add it, go to the Control Panel, Add/Remove Programs, and choose to modify the Crystal Reports installation.

There is no Oracle native ODBC connection, so I'm not sure what you're talking about there. There is either native, which is the "Oracle Server" option, or there is ODBC which can encompass lots of different ODBC drivers for Oracle, with the recommended one being the CR ODBC driver for Oracle, which you would pick by choosing a particular DSN.

My experience has been that if your stored procedure works using the CR ODBC driver for Oracle, it will work using the native driver, AND you will not have the added wait time. It will probably NOT work using the Oracle ODBC driver for Oracle or the Microsoft ODBC driver for Oracle, depending on the Oracle version and driver version. If you are correctly using the native driver and still having a problem, it could be that you need to install the most recent service pack or hot fix for CR 8, if they're still keeping them updated.
Avatar of rambosh

ASKER

I don't have it in the database expert, so I'm trying to add it. I'm in the add/remove programs and have seagate up. I imagine I have to go to data access, but then what option/selection is it? Oracle? Also, what I meant by the native driver is the native oracle driver. (Psdor7.dll or whatever it's called.)
Avatar of rambosh

ASKER

One more thing. What version of crystal 8 is it in? I have the developer edition opened right now. Is it in here? or somewhere else?
Yes it should be the Oracle option under data access, but not under ODBC. I don't have CR8 still, but I assume it hasn't changed much. The current version of the native driver is crdb_oracle.dll, version 9.2.1.116. I looked at Crystal Decisions website and it looks like you could use the most recent database drivers hot fix even for CR8. You might want to look at that.

The native driver should be available with the developer edition, just not with standard.