more than one number from sequence

From: Volker Apelt <gq437x_at_yahoo.de>
Date: 23 Oct 2002 16:57:00 +0200
Message-ID: <lgsmyx2og3.fsf@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 - 09:57:00 CDT