Question - Stored Procedures and Cursors

From: James J. Yorton <yorton_at_polarbear.cig.mot.com>
Date: 6 Dec 1993 23:34:22 GMT
Message-ID: <2e0fhu$doc_at_delphinium.rtsg.mot.com>

I'm running Oracle 7 and I was wondering if it is possible to declare a CURSOR in a Pro*C program's DECLARE block, and then pass that cursor as a parameter to a stored procedure. Once inside that stored procedure, OPEN the CURSOR, and return to the C program. Once back in the C program, perform repeated FETCHes and CLOSE the cursor.

Basically I want to use CURSORs but I want them to act/execute as stored procedures when I open them and execute my query.

Something like this in the Pro*C program:

EXEC SQL BEGIN DECLARE SECTION;

	cursor c1 is
		select * from tablename;

EXEC SQL END DECLARE SECTION;
/* called stored procedure */

EXEC SQL EXECUTE
BEGIN
        stored_procedure(c1);
END;
END-EXEC
/* do FETCH here */

And declare the stored procedure itself something like:

CREATE OR REPLACE PROCEDURE stored_procedure(c1 CURSOR) AS
BEGIN
        OPEN c1;
END stored_procedure;

I searched high and low in my Oracle books and there was nothing on it. My stored procedure won't compile. I get:

        PLS-00201: identifier 'CURSOR' must be declared

so it doesn't appear to like my stored procedure's parameter declaration. Maybe it can't be done, or maybe my syntax is just wrong.

Is this a possibility, or should I dream on? Please email your responses. Thanks.

------------------------------+------------------------------------
Jim Yorton                    | Telephone:  +1 708-632-6695
Motorola Inc.                 |
General Systems Sector        | Internet:   yorton_at_rtsg.mot.com
Cellular Infrastructure Group | UUCP:       ...!uunet!motcid!yorton

Arlington Heights, IL USA | Received on Tue Dec 07 1993 - 00:34:22 CET