Re: more than one number from sequence

Pick any large (enough) table you have in your schema and then

SELECT MySequence.NextVal
FROM MyLargeTable
Where ROWNUM < TheNumberIWant;

"Volker Apelt" <gq437x_at_yahoo.de> wrote in message news:lgsmyx2og3.fsf_at_mu.biosolveit.local...
>
> I'm looking for a way to retrieve more than one id
> per SQL statement/OCI aall from a sequence.
>
> * Is there some smart and inexpensive SQL statement
> wich will return a variable number of rows from
> 'select seq.nextval into ID from dual'?
>
> I defined a sequence, a package header and a package body
> for retrival of many sequence numbers with on sql statement
> by OCI. But the
>
> Compiling the package body in sqlplus gives these errors.
> PLS-00306: wrong number or types of arguments in call to 'EXTEND'
> and
> PLS-00437: FORALL bulk index cannot be used in RETURNING clause
>
> How should I rewrite draw_N_ids() ?
>
> Thank you,
>
>
> Volker
>
> --------- PL/SQL
> create or replace package test_on_sequences as
> TYPE sequence_id_list IS TABLE OF NUMBER index BY binary_integer;
> FUNCTION draw_N_ids( i_count IN NUMBER ) RETURN
> sequence_id_list;
> procedure P_draw_N_ids(i_count IN NUMBER, ret sequence_id_list);
> END test_on_sequences;
> /
> --
> create sequence seq_employee_id;
> --
> create or replace
> package BODY test_on_sequences as
> FUNCTION draw_N_ids( i_count IN NUMBER ) RETURN sequence_id_list
> as
> ret sequence_id_list ;
> i binary_integer := 0;
> new_key number := 0;
> begin
> ret.extend(i_count,0); -- line 8
> FORALL i IN 0 .. (i_count - 1)
> SELECT seq_employee_id.NEXTVAL INTO ret(i) FROM dual;
> return ret;
> end;
> END test_on_sequences;
> /
> show errors
> SQL>
>
> Errors for PACKAGE BODY TEST_ON_SEQUENCES:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 8/9 PLS-00306: wrong number or types of arguments in call to 'EXTEND'
> 8/9 PL/SQL: Statement ignored
> 10/12 PL/SQL: SQL Statement ignored
> 10/48 PLS-00437: FORALL bulk index cannot be used in RETURNING clause
> 10/55 PL/SQL: ORA-00904: invalid column name
> SQL>
>
> --------- PL/SQL
> --
> Volker Apelt
>
>
Received on Wed Oct 23 2002 - 10:15:26 CDT