Solved

set location for oracle stored procedure using crystal 8.0

Posted on 2003-10-27
19
533 Views
Last Modified: 2009-07-29
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.
0
Comment
Question by:rambosh
  • 9
  • 7
  • 2
19 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 9630952
0
 

Author Comment

by:rambosh
ID: 9633525
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.
0
 
LVL 3

Accepted Solution

by:
AnnetteHarper earned 200 total points
ID: 9633610
Synonyms won't do you any good unless you're calling the stored procedure from a SQL Command. The synonyms list in the Database Explorer just doesn't work, though there may be a hot fix for that by now.

Is the stored procedure defined in a schema other than that of the logged in user? If so and you're using ODBC, then you will not be able to see it unless you uncheck "Use current schema for SQL procedures." In my experience, if I wait 30-40 minutes, the Database Explorer will finally return and list the procedures! The alternative is to use the native driver. It will display the procedures in other schemas as expected. If you have to use ODBC and don't care to wait half an hour, another alternative is to call the procedure from a SQL Command.

The privileges required for the stored procedure, assuming that it is a definer rights procedure, are just execute on the procedure name.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9637036
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
0
 

Author Comment

by:rambosh
ID: 9641444
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.
0
 

Author Comment

by:rambosh
ID: 9717449
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?
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9717702
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.
0
 

Author Comment

by:rambosh
ID: 9723510
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.
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9723636
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).
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:rambosh
ID: 9739409
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.
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9739881
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?
0
 

Author Comment

by:rambosh
ID: 9740293
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?
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9740447
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.
0
 

Author Comment

by:rambosh
ID: 9740669
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.
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9740786
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.
0
 

Author Comment

by:rambosh
ID: 9741110
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.)
0
 

Author Comment

by:rambosh
ID: 9741113
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?
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9741218
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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now