Re: PL/SQL do processing on output cursor ?

"Ka" <jackhui_at_hotmail.com> wrote in message news:<asvkef$i1k1_at_rain.i-cable.com>...
> I create the following Stored Procedure, but it can't compile. I want to
> return a cursor
> from a "select", but after select, I want to update the fields too. Can I do
> it ??
>
> PROCEDURE GetTokenControlList(p_cursor OUT refcur) IS
> new_counter NUMBER;
> BEGIN
> OPEN p_cursor FOR
> SELECT TOK_KEY, TOK_COUNTER, TOK_BLOCKSIZE FROM TIF_TOKENCONTROL;
>

Wrong syntax. You need to declare cursor before BEGIN statement. You can't open a cursor if you want to use it in FOR cursor loop. FOR cursor loop opens cursor (and closes it) automatically. Another trick is to use SELECT statment, in parentheses, instead of cursor name in FOR loop.

And, of course, you could use SHOW ERROR after CREATE PROCEDURE statement in sqlplus.It would show you syntax errors.

Last, but not least. It's a very bad idea to update the table inside of a cursor loop if cursor is based on the same table. It's a very good way to cause ORA-1555 error. If you need to do an update, use FOR UPDATE clause in the cursor.

> FOR tok_rec in p_cursor LOOP
> new_counter := tok_rec.TOK_COUNTER + tok_rec.TOK_BLOCKSIZE;
> UPDATE TIF_TOKENCONTROL SET TOK_COUNTER = new_counter
> WHERE TOK_KEY = tok_rec.TOK_KEY;
> END LOOP;
>
> END GetTokenControlList;
Received on Mon Dec 09 2002 - 17:50:35 CST