Solved

Stored Procedure in Crystal Reports

Posted on 2004-09-05
7
603 Views
Last Modified: 2008-02-01
Hi,

I'm trying to use stored procedure in Crystal Reports.
The stored procedure is:

create or replace procedure test (param in out varchar) is
cc varchar2(30);
begin
select name  into cc from my_content where id = 'c_101' and locale='en';
param := cc;
end;

after selecting it in CR data explorer I get error: "Error opening file. There are no fields in file: "ROZANA.Proc(Test)"."

Thanks!
0
Comment
Question by:RozanaZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 13

Accepted Solution

by:
vidru earned 500 total points
ID: 11984694
Looks like Oracle (which I don't use).  However, this document has all the information you need on how to construct your Oracle stored procedure to work with Crystal:
http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

-dave
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 11985163
Hey vidru:)

If you're only pulling in a single value, you can just use a SQL Expression such as:

(select name from my_content where id = 'c_101' and locale='en').

If this proc is intended to return a list of values, then you'd probably be better off just using a view or pulling straight from the table.  I don't think we have the whole picture.  What are you trying to accomplish and does this proc interact with anything else?
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11985765
Have you tested your SP outside of Crystal?  If this is Oracle, then you'll get an exception if there isn't exactly one record matcing your selection criteria so that could be your problem.  

frodoman
0
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 
LVL 3

Expert Comment

by:DarrenZai
ID: 11986855
You seem to be trying to specify the return variable rather than just using the result of the query.
I don't think CR handles this.
What the error is saying is that as far as CR is concerned there is no fields returned, no result set.
You shouldn't need to declare the result variables at the beginning, so you shouldn't need to use 'select into' in this way.
You should just be able to go

select name from my_content ...

and then the result set will contain a field called 'name'

If you want the result field to be called param then you can try

select name as param from my_content

and then the result set will contain a field called 'param'

Darren
0
 
LVL 8

Author Comment

by:RozanaZ
ID: 11987256
Hi,

Thank you for answers,
all I want to do is to check how does CR work with stored procedures,
so I need a simple stored procedure to interact with CR.

I do not understand the meaning of error message: "There are no fields in file: "ROZANA.Proc(Test)"."
What fields should I define?
0
 
LVL 3

Expert Comment

by:DarrenZai
ID: 11987279
You shouldn't need to specifically define any fields, they are usually the fields specified in your select statement.

eg

select name1, name2,name3 from my_details where id = 'c_101'

would return a record with 3 fields to CR and that should work fine.

Your SQL is selecting into another variable and I don't think CR is recognising the result from this stored proc.

Is there any particular reason why you need to select in this fashion?

Darren

0
 
LVL 8

Author Comment

by:RozanaZ
ID: 11987349
Thank you all!

Vidru, link you provided solves everything! Thank you!

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

688 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