RozanaZ
asked on
Stored Procedure in Crystal Reports
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
frodoman
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
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
ASKER
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?
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?
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
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
ASKER
Thank you all!
Vidru, link you provided solves everything! Thank you!
Vidru, link you provided solves everything! Thank you!
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?