StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle Partitioned Sequence
text
Body
copied!<p>I'm trying to see if exists something to create a sequence with partition logic. I need a sequence number that depend on other primary key ex:</p> <pre><code>id_person sequence id 1 | 1 1 | 2 2 | 1 3 | 1 1 | 3 </code></pre> <p>so the sequence must depend on the <code>id_person</code> partition. Is there something like this on oracle or i must implement it by myself on the application level?</p> <p>thank you.</p> <hr> <p>Hi have create this PLSQL package one function and procedure:</p> <pre><code>PROCEDURE INIT_SEQUENCE(NAME varchar2, pkColumnNameList PARTITIONED_SEQUENCE_PK_COLUMN); FUNCTION GET_NEXT_SEQUENCE_VALUE(NAME varchar2, pkPartitionColValue PARTITIONED_SEQUENCE_COL_VALUE) RETURN NUMBER; </code></pre> <p>INIT_SEQUENCE - get in input the name to associate at the sequence and a list of column name that are the fixed primary key part that vincolate the sequence Ex:'ID_PERSON'</p> <p>the work of this procedure is to create the table that will manage the increment of sequence according to pkColumnNameList column.</p> <p>GET_NEXT_SEQUENCE_VALUE- get the name of sequence to increment and the value of pkColumnNameList primary key and make the next step: 1) Create dynamically the sql to work 2) dbms_lock.allocate_unique(); to lock the table 3) check if is present a record in the table for pk value in input 4) if a record is present update the record with max + 1 in the sequence column 5) if a record is not present insert the new record with the 1 in the sequence column 6) return new id;</p> <p>i would like to receive comment about this thanks in advance...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload