2

I need a little stored procedure to do the following logic?

procedure_name(seq_name IN varchar2(50), block_count IN int, return_ids OUT)

loop from 1 to block_count
return_ids := select 'seq_name'||.nextVal from dual;
end loop
return return_ids

Basically what I want to do is have a stored procedure that lets me pass in a sequence name, how many IDs I need and return to me the generated listed of IDs that I can use in JAVA. The reason to do this for me is to return a list of IDs that I can use in JAVA and no one else is using those sequence IDs. Where they will be used in some other bulk inserts later down the line. In essence, reserve a block of sequence IDs.

Improve this question

5 Answers 5

Reset to default
3

Here is one way to return an array from PL/SQL procedure.

Create a collection type of numbers, initialize it in your procedure and populate it with numbers to return. For example:

create or replace type narray as table of number;

create or replace procedure get_seq_ids(seq_name in varchar2, 
    block_count in number, return_ids out narray)
as
begin
    return_ids := narray();
    return_ids.extend(block_count);
    for i in 1 .. block_count
    loop
        execute immediate 'select ' || seq_name || '.nextval from dual' 
            into return_ids(i);
    end loop;
end;
/
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks vls, that's just what I needed!
2

I'd be concerned over the logic that requires IDs to be generated before records are inserted into the database.

Alternatively you may want to consider inserting rows first, selecting the ids from the rows, and then using an update statement to do your bulk operation. This however is still not as preferable as having the Java code not depend on ids until after the actual information is ready to be inserted.

You can push your information into XML (or any other data format your database can understand) and then call a stored procedure to do the bulk inserts.

Improve this answer

6 Comments

Well, if I had DBA support then I would much rather just pass in arrays to a procedure and let them handle all the insert on the DB side. But since I don't have the support...
I'm trying to insert a large numbers of records in to related hierarchical tables. Since I need IDs from the top level table so I can insert it in to the second level table and so on to the third and fourth level table and so on. I also don't want to use JDBC to call the DB to get the ID each time while I'm inserting. I just want to pre fetch a block of SEQ_IDs that I can populate various arrays with so I can then use the arrays in batch inserts.
I'm confused by your comment about not having DBA support. You can write a stored procedure to reserve IDs, but cannot write a stored procedure to do the inserts into each table with the correct IDs?
It's not an unusual problem. Java provides a batch command that issues a list of DML operations in one call. The only way to take advantage of that is to get the IDs ahead of time so all foreign key references can be specified before any rows are inserted
What's the benefit of the Java batch command over sending the data via XML to a stored procedure that can handle keys natively? I can 'batch' commands up in XML instead of an array to make one call.
|
1

Another option may be to use the RETURNING clause to return the sequence values automatically after the insert.

Improve this answer

Comments

0

IMHO, the best thing you can do is just referencing sequence_name.nextval right in your INSERT INTO, in the VALUES clause.

You said you want to avoid others using the same IDs. Referencing this site:

The sequence (or Oracle, for that matter) ensures that no other session or other call to nextval within the same session gets the same number from the sequence.

So, the uniqueness of a sequence' numbers are guaranteed in Oracle.

Improve this answer

1 Comment

The problem is for an application that wants to batch up DML operations for performance reasons, leaving the nextval call in the INSERT means a subsequent query to get that ID before inserting any child records. That's a lot of back-and-forth talk with the DB for an app that has to persist a lot of rows.
0

Here's what I do for the Java application I support (which also uses bulk inserts into deeply hierarchical tables)

PROCEDURE get_nextvals
( 
    p_values OUT SYS_REFCURSOR,
    p_count  IN  PLS_INTEGER
)
IS
    -- return the next p_count values from the PK sequence
BEGIN
    OPEN p_values FOR
        SELECT
            <schema>.<sequence>.nextval
        FROM
            dual
        CONNECT BY
            LEVEL <= p_count
    ;
END;

It was easier to just pass a cursor out to java than having the app use a table type defined in the DB.

Improve this answer

1 Comment

Also helpful, I got the table type way working, but I'll try this way to see if it makes a difference. Thanks guys!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.