Re: Retrieve a sequence number using a cursor

In article <3694BBD9.389ACF46_at_dircon.co.uk>,   reza <reza_at_dircon.co.uk> wrote:
> Dear All,
>
> I am trying to get SEQUENCE value using a stored procedure.
> Unfortunately I can't get this small procedure to compile. Can anyone
> help?
>
> HEADER:
> CREATE OR REPLACE PACKAGE pkg_unique
> AS
> TYPE t_sysno is TABLE OF NUMBER(8)
> INDEX BY BINARY_INTEGER;
>
> PROCEDURE allunique
> (SYS_DEF_SEQ OUT t_sysno );
>
> END pkg_unique;
>
> BODY:
> CREATE OR REPLACE PACKAGE BODY pkg_unique
> AS
> PROCEDURE allunique
> (SYS_DEF_SEQ OUT t_sysno
> )
> IS
> CURSOR cur_key IS
> SELECT SYS_DEF_SEQ.NEXTVAL FROM dual;
>
> sccount NUMBER DEFAULT 1;
>
> BEGIN
> FOR thing IN cur_key
> LOOP
> SYS_DEF_SEQ(sccount):=thing.SYS_DEF_SEQ;
> sccount:=sccount+1;
> END LOOP;
> END;
> END;
>
>

Two issues:
1. In the package body, the variable SYS_DEF_SEQ is BOTH PL/SQL table and sequence. That's not allowed. Could you just change the PL/SQL table name. 2. An error is in the following line:
> LOOP
> SYS_DEF_SEQ(sccount):=thing.SYS_DEF_SEQ;
right side should be "thing.SYS_DEF_SEQ.NEXTVAL", or just use an alias for "SYS_DEF_SEQ.NEXTVAL" in Cursor definition.
-Cathy

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 07 1999 - 00:00:00 CET