Error.Calling oracle stored procedure in crystal report

[ram2002]

Hi,

I created oracle stored procedure and it runs successfully in sql plus.
Then i add procedure name in crystal report it gives error
Error name is:
ORA-01001: cursor invalid

stored procedure is

CREATE OR REPLACE PROCEDURE NEW_WORKER
(Person_Name IN VARCHAR2
,Person_age IN NUMBER
,Per_Lodging IN VARCHAR2 )
AS
BEGIN
INSERT INTO WORKER(Name,Age,Lodging)
values
(Person_name,person_age,per_lodging);
End;

Please help me out.
Thanks.

[ducky]

You have to return the recordset via a ref cursor. Also looking at your code you don't have a select procedure. See the crystal support doc I found usefull for oracle stored procedures.

http://support.crystaldecisions.com/...edures.pdf.asp

[ram2002]

Hi Ducky,
As per your guidence i created stored procedure and i tried to run the program in crystal report.But i could not make it.I dont know why.Please help me out.

my code is: I created package:It works fine
**********************************
CREATE OR REPLACE PACKAGE New_Worker_package
AS TYPE New_Worker_Type IS REF CURSOR RETURN WORKER%ROWTYPE ;
END New_Worker_package;
***********************************
Here is stored procedure:this also works fine
CREATE OR REPLACE PROCEDURE NEW_WORKER_SELECT(
NEW_WORKER_CURSOR IN OUT New_Worker_package.New_Worker_Type,
New_Worker_Parameter IN WORKER.Name%TYPE)
AS
BEGIN
OPEN NEW_WORKER_CURSOR FOR
SELECT *
FROM WORKER
WHERE WORKER.Name=New_Worker_Parameter;
END NEW_WORKER_SELECT;
*********************************
This displays the datas exactly

SET SERVEROUTPUT ON
declare
NEW_WORKER_CURSOR New_Worker_package.New_Worker_Type;
resultset NEW_WORKER_CURSOR%rowtype;
begin
NEW_WORKER_SELECT(NEW_WORKER_CURSOR,'DICK JONES'(i passed hardcode parameter value));
if not NEW_WORKER_CURSOR%isopen then
dbms_output.put_line('the cursor is not open');
else
dbms_output.put_line('the cursor is open');
end if;
fetch NEW_WORKER_CURSOR into resultset;
while NEW_WORKER_CURSOR%found Loop
dbms_output.putline(resultset.Name);
dbms_output.putline(resultset.Age);
dbms_output.put_line(resultset.Lodging);
fetch NEW_WORKER_CURSOR into resultset;
End loop;
end;
*****************************
When i added it in crystal report.
I selected NEW_WORKER_SELECT procedure and i press the ADD button it asks ENTER the Parameter value.
It has NEW_WORKER_CURSOR and New_Worker_parameter.
if i select the New-worker_parameter and passed the value in descrete value box.
Then it added and i selected crystal report designer.But in preview it doesnot display anything.
So please help me out.
I exactly tried with which you sent link know.I used native oracle odbc driver.

Please help me.
Thanks.

[ducky]

Hmm off hand it looks good.

I selected NEW_WORKER_SELECT procedure and i press the ADD button it asks ENTER the Parameter value.
It has NEW_WORKER_CURSOR and New_Worker_parameter.
if i select the New-worker_parameter and passed the value in descrete value box.

Yes, this is to enable it to get the fields from the procedure to fill out the field explorer box in the designer. You did this correct, leave the new_worker_cursor null and key a value for the new_worker_parameter.

Then it added and i selected crystal report designer.But in preview it doesnot display anything.

Did you add fields to the detail of your report, then run the report and re-key the new_worker_parameter parameter when prompted? Also, the actual setups of the parameters it automatically creates when you add the procedure to your report should not be modified at all. You should only key values when you run the report and it asks for the parameters.

So please help me out.
I exactly tried with which you sent link know.I used native oracle odbc driver.

Your deffinately close. Hope you get it straightened out.

[NewbieVB2003]

Can CR not accept OUT Parameters?

I have this peice of code.Could you please suggest whether it will work with CR.
Thanks

declare @retrn smallint
	exec procedure_name	
			@project_id 	= 1,
			@from_date 	= '01/01/2004',
			@to_date		= '01/01/2005',
			@return_cd = @retrn output	
	select 'ret code = ', @retrn

When I allow it to add the OUT parameter as well while designing report;then at runtime,it asks me to enter the retrn parameter as well.

I need to instead display the project_name,from_date and to_date in the report which correspond to project_id,from_date and to_date.