Oracle: laufende Nummer mit Sequenzen

[Studdi20]

Hallo zusammen,

ich versuche in einer Tabelle Datensätze einzufügen deren ID automatisch mit dem Befehl sequenzname.NEXTVAL erhöht wird. Beim Erstellen der Sequenz habe ich Start With 1 und als Inkrement auch 1 angegeben. Trotzdem wird beim Anlegen eines Datensatzes immer eine Nummer übersprüngen (2,4,6,8 usw.) Es ist aber für mich wichtig das die Nummer laufend ist ohne Lücken dazwischen, also 1,2,3,4 usw. Ein weiteres Problem besteht darin, dass wenn eine oder mehrere Einfügeoperationen fehlschlagen, beim nächsten erfolgreichen Einfügen sogar mehrere Nummern übersprüngen werden (z.b. 1,2,5,9 usw.)

Ich habe herausgefunden, dass wenn ich den Befehl .CURRVAL einsetze, also z.B. INSERT INTO TABLE VALUES (TAB_SEQ.CURRVAL, 'Test')

die Inkrementierung um 1 funktioniert jedoch nur wenn ich vorher NEXTVAL ausgeführt habe. Ansonsten erhalte ich folgende Fehlermeldung:

"ORA-08002: Sequenz TAB_SEQ.CURRVAL ist in dieser Session noch nicht definiert"

Ich möchte die SQL-Anweisung in einem Java-Programm verwenden und es ist wie schon gesagt wichtig, dass der erste Datensatz bei 1 anfängt und alle Anderen an die zuletzt eingetragene ID anknüpfen. Wie stelle ich das genau an? Gibt es vielleicht auch eine Möglichkeit nachträglich die Sequenz für eine Primärschlüsselspalte zu ändern bzw. neu zu erstellen, so dass alle ID's der Spalte den gewünschten Kriterien entsprechen?

Bearbeitet 2. November 2009 von Studdi20

[grueni]

Wann und wo wird der Wert aus der Sequenz in die Tabelle eingefügt, in einem BEFORE INSERT Trigger?

Siehe Beispiel Nr.1 von:

Oracle Sequence for Auto-Increment

[Studdi20]

servus,

ja es handelt sich um ein BEFORE INSERT Trigger.

CREATE OR REPLACE TRIGGER  "BI_FAQS" 

  before insert on "FAQS"               

  for each row  

begin   

    select "FAQS_SEQ".nextval into :NEW.ID from dual; 

end; 


/

ALTER TRIGGER  "BI_FAQS" ENABLE

/[/PHP]

Meinem Java-Programm übergebe ich folgende Zeichenkette um die eingegebenen Daten in der DB zu speichern:

String put = "INSERT INTO FAQS VALUES (faqs_seq.NEXTVAL, ?, ?, ?)";

PreparedStatement putStmt = connection.prepareStatement(put);

Aber wie gesagt, der Sequenz-Wert wird nicht wie erwartet beim Einfügen eines Datensatzes um 1 hochgezählt, sondern jeweils um 2. Die erste Zeile beginnt schon mit dem Wert 2. Woran liegt das und was genau muss ich ändern?

[grueni]

Dir ist aber schon klar, was ein Trigger macht?

Zum Nachlesen: Triggers

Triggers are implicitly fired by Oracle when a triggering event occurs...

In deinem Fall ist das triggering event ein INSERT. Du benutzt einen BEFORE INSERT Trigger, also wird der Trigger natürlich vorher ausgeführt.

Du darfst in deinem INSERT die ID nicht nochmal mit dem Sequenzwert setzen, deshalb wird die Sequenz nämlich zweimal hochgesetzt. Einmal vom Trigger und einmal in deinem INSERT.

[Studdi20]

Achsooo :upps. Mir war nicht ganz klar wie ein Trigger arbeitet, sorry. Hab ihn jetzt deaktiviert und siehe da...es funzt Vielen Dank für deine Hilfe. Eine letzte Frage noch, ist es möglich eine Sequenz zurückzusetzen, so dass alle Datensätze einer Tabelle mit 1 beginnend eine laufende Nummerierung erhalten? Oder kann ich die Sequenz löschen und die Primärschlüsselspalte mit neuen Werten auffüllen?

EDIT: Hat sich erledigt, habs mit ALTER SEQUENCE hinbekommen.

Bearbeitet 2. November 2009 von Studdi20

[dbwizard]

....Es ist aber für mich wichtig das die Nummer laufend ist ohne Lücken dazwischen, also 1,2,3,4 usw.....

EDIT: Hat sich erledigt, habs mit ALTER SEQUENCE hinbekommen.

Hallo,

Nur eine Bemerkung zu deiner Anforderung : "Lückenlose" Nummerierungen sind eine Illusion und können mit Sequences *nicht* garantiert werden.

Gruss

[Studdi20]

Hallo,

das ist mir mittlerweile klar geworden. Mein nächstes Ziel ist es einen Trigger zu schreiben der vor jedem insert max(id) sowie nextval ausliesst und die Sequenz anschließend um die Differenz inkrementiert. also z.b. sowas:

alter sequence faq_seq increment by maxid-value;

Nur kenn ich mich leider nicht mit PL/SQL aus und werde mich zuvor reinlesen müssen. So wie ich das in einem Tutorial verstanden habe muß ich erstmal in einem Package die Variablen definieren, in einem weiteren Trigger die Werte in den Variablen speichern und schließlich den Sequenzwert mit obigem Befehl anpassen.

Kann mir hierzu einer von euch Hilfestellung leisten? Wäre sehr dankbar.

Bearbeitet 3. November 2009 von Studdi20

[dbwizard]

Hallo,

das ist mir mittlerweile klar geworden. Mein nächstes Ziel ist es einen Trigger zu schreiben der vor jedem insert max(id) sowie nextval ausliesst und die Sequenz anschließend um die Differenz inkrementiert. also z.b. sowas:

- Das wird auch nicht funktionieren. Stichwort : Multiuser. Ich würde mir auf jeden Fall mal die Bedienung "Gap-Free" nochmals durch den Kopf gehen lassen, dies ist praktisch ohne Serialisierung nicht umzusetzten

Ein interesssanter Post zu diesem Thema findest du hier :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:530735152441

Gruss

Bearbeitet 3. November 2009 von dbwizard

[Studdi20]

Verstehe. Danke für den Hinweis.