0

Recently I have to make my current schema expose to other schema and allow it to insert new records, lets say table RECORD.

The RECORD table is managed by OpenJPA and it's id is auto generated

@GeneratedValue(strategy = GenerationType.AUTO)

and in openjpa persistence context

<property name="openjpa.Sequence" value="table(Table=SEQUENCE_TABLE, Increment=10)" />

and the sequence table is

CREATE TABLE "MYSCHEMA"."SEQUENCE_TABLE"
( 
    "ID" NUMBER(22,0) NOT NULL ENABLE,
    "SEQUENCE_VALUE" NUMBER(22,0),
     CONSTRAINT "SYS_C0012743" PRIMARY KEY ("ID")
);

my question is what is the safest way to let external schema retrieve the sequence for the insertions?

Dreamer
  • 7,333
  • 24
  • 99
  • 179
  • 1
    The "best" way? Use a *real* sequence. –  Jan 23 '15 at 23:17
  • @a_horse_with_no_name How about a "safest "way can live with current design? :) – Dreamer Jan 23 '15 at 23:24
  • @a_horse_with_no_name Can I ask what is the risk to switch to real sequence if database already have data(meaning many records has 'id' assigned with original sequence table)? – Dreamer Jan 25 '15 at 20:22
  • No problem to switch to a real sequence. Just start from a save margin –  Jan 26 '15 at 02:25
  • @hragheb Thanks, do you think this thread is the solution as you suggest for the save margin? http://stackoverflow.com/questions/798766/how-to-create-an-oracle-sequence-starting-with-max-value-from-a-table – Dreamer Jan 26 '15 at 02:28
  • 1
    The mentioned thread starts exactly by difference 1 form your last created sequence number. If you want things to be safe, then you can choose to start by 200 or more from your last sequence number. It depends on the width of your PK column. If it's big enough you can start with much broader margin to be more confident and also to identify where you are since your migration –  Jan 26 '15 at 11:12

0 Answers0