Re: more than one number from sequence

Volker Apelt wrote:

> 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

Not from dual.

But dual returns one value because there is one row in the table (and don't get any ideas about changing it or you will make a mess of everything).

But if you have a table with three rows in it you can do the following:

SELECT sequence_name.NEXTVAL
FROM tablewiththreerows;

and get three numbers.

But you are looking for a variable number of rows so you could do this.

1. Create a table with 1,000,000 rows.
2. Use a random number generator to generate a random number. 3.

SELECT sequence_name.NEXTVAL
FROM tablewiththreerows
WHERE rownum < randomnum+1;

And you will get what you want. Though why you would want it is a mystery to me. Because I can't imagine how you are going to SELECT INTO a random number of variables.

Daniel Morgan Received on Wed Oct 23 2002 - 10:36:40 CDT