Tom,

I'm looking for a way to return back a block of sequences numbers through PL/SQL. Currently I have have written a stored procedure that takes two arguments (table name and # of sequence numbers to be reserved) and writes the sequence numbers out to a temp table and I pass the result of the temp table back out to the calling app via ref cursor.

I'm looking for a better approach to this solution (code below) and any suggestions would be appreciated. Thanks for taking my question.

CREATE OR REPLACE PROCEDURE test_get_numbers_proc
(
vv_array_size IN NUMBER,
vv_table_name IN VARCHAR2,
vv_row_cursor IN OUT SYS_REFCURSOR
)
AS


-- Declare local variables
v_sql VARCHAR2(1000);
v_seq_name VARCHAR2(30);
v_tab_name VARCHAR2(30);
v_current_number NUMBER;
v_test_number NUMBER;
v_array_size NUMBER;
--
BEGIN

v_tab_name := vv_table_name;

-- dbms_output.put_line ('Table name is '||v_tab_name);
-- All sequence names are <table_name>_seq, so assign it

v_seq_name := v_tab_name||'_SEQ';

-- dbms_output.put_line ('Sequence name is '||v_seq_name);
v_array_size := vv_array_size;

-- dbms_output.put_line ('Array size is '||v_array_size);


FOR v_current_number in 1 .. v_array_size
LOOP
v_sql := 'INSERT INTO TEMP_SEQUENCE_TBL SELECT '||v_seq_name||'.nextval from dual';
EXECUTE IMMEDIATE v_sql;
-- DBMS_OUTPUT.PUT_LINE ('SQL is '||v_sql);
END LOOP;

-- SELECT count(*) INTO v_test_number
-- FROM temp_sequence_tbl;

-- DBMS_OUTPUT.PUT_LINE ('Number of sequence records is '||v_test_number);

OPEN vv_row_cursor
FOR SELECT seq_number
FROM temp_sequence_tbl;

END test_get_numbers_proc;
/

New Info..
I asked the developer the question you posed and response was the following:

The idea to get the block of sequence numbers is to reduce server roud trips. Need to reserve and retrieve a preset number of primary keys so that it can be used to insert an array of records, all at once. To use nextval from the program for every records would make us hit the databse everytime....

Updated info..

Sorry for the delay, I went back to our developr and this gets into OCI application code (using an array interface?) which I have very little insight on. While I know this has gotten away from the original question, I would like your opinion if what they want to do is feasible via OCI as currently we are using a combination of OCI with PL/SQL stored procedures for DML activity in the database.

Here's what the developer(s) originally wanted to do (with an explanation.):

We would like to be able to use the array interface to send volumes of data to the database. We currently have one or more processes which take on the order of hours to handle volumes of data.

The primary key for the data is generated by the database from a sequence. When the process comes up, 'just enough of' the data is read (into a 'key object') to let us know which elements we're truly interested in. When the details of the object become of interest, the object is retrieved by the sequence-generated key retained in the 'key object'.

We would like to be able to assign the key to the object before we actually send the data to the database (currently using a stored procedure to insert the row and return the key value). This will enable us to 'complete' the data without waiting for the data to be sent to the database proper.

This, in turn, allows us to simplify the program logic - we don't have to keep the linkage between the source object and the broken-down row representation present at all times, nor do we have to find the original object except when an exception (preventing storage) occurrs.

What will typically happen is that there will exist one 'complete' object, only during receipt of the data, with a collection of 'key' objects that enable us to rettrieve those which we become interested in.