Re: Pro*C : Stored procedure in cursor

Chris <Chris_at_nospam.com> writes:

> Hello,
>
> I'm trying to call a stored procedure in a cursor in Pro*C :
>
> EXEC SQL DECLARE cur CURSOR FOR
> SELECT id
> FROM suivi_rejet
> WHERE lib = 'MyLib';
> EXEC SQL OPEN cur;
> EXEC SQL FETCH cur INTO :id;
>
> while (sqlca.sqlcode != NO_DATA_FOUND)
> {
> EXEC SQL FETCH cur INTO :id;
> EXEC SQL EXECUTE
> BEGIN
> MY_PACKAGE.my_procedure(:id);
> END;
> END-EXEC;
> }
> EXEC SQL CLOSE cur;
>
> by I get : ORA-01002 "fetch out of sequence".
> I can't see where is my error. :-(

I assume that you got an NO_DATA_FOUND but although you call the procedure. Another problem is that your first fetch is not passed to the procedure. I would try it as follows. Seems to me more logical:

Harald

EXEC SQL DECLARE cur CURSOR FOR

                        SELECT  id
                        FROM    suivi_rejet
                        WHERE   lib = 'MyLib';
                EXEC SQL OPEN cur;
                
                while (1) 
                {
                        EXEC SQL FETCH cur INTO :id;
                        if( sqlca.sqlcode != NO_DATA_FOUND)
                          break;

                        EXEC SQL EXECUTE
                        BEGIN
                                MY_PACKAGE.my_procedure(:id);
                        END;
                        END-EXEC;
                }
                EXEC SQL CLOSE cur;