Stored Procedures

[mjmiller24]

I am trying to use stored procedures crystal reports 8.5, but I keep getting an error about my cursor. It says it is invalid. After closing out, and trying to just preview the normal report, I get a message that the report is trying to use a closed cursor. Then it says there is an error in the DLL. I have to restart the server then.

I really want to have the psID passed into the stored procedure, and then have it return something via a field. I have this so far, which may not be correct. Can someone help me with this?


CREATE OR REPLACE PROCEDURE test(psID IN Number) AS
v_cursorID INTEGER;
v_selectStr VARCHAR2(1000);
v_exec INTEGER;
v_id INTEGER;
CURSOR statemnt is SELECT main.ps_id from str.main where main.ps_id = psID;
BEGIN
v_id := 0;
OPEN statemnt;
LOOP
FETCH statemnt INTO v_id;
EXIT WHEN statemnt%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_id);
END test;
/

 

[balves]

You might want to post in an Oracle forum.

 

[mjmiller24]

I would except it runs fine in an Oracle environment. I just don't know how to get it to work in a Crystal Reports environment.

 

[balves]

Try looking at this document:

scr_oracle_stored_procedures.pdf

Type the document name to Ask Jeeves at:

http://support.crystaldecisions.net/fix/ask.asp

 

[Pejo]

When you use a stored procedure with Crystal Reports the stored procedure has to return a ref cursor and yours is not returning anything!
Also you shouldn't open and loop through the cursor but instead do like this:

"OPEN out_cursor FOR
SELECT main.ps_id from str.main where main.ps_id = psID;"

where out_cursor is declared as OUT parameter to the procedure. It's declared seperatley in a package.

What I can remember it's not possible to use dbms_output-package in stored procedures used by Crystal Reports.

/PeJo