Home » SQL & PL/SQL » SQL & PL/SQL » To increment column value before insert (Oracle 9.2.0.3)
To increment column value before insert [message #429146] Mon, 02 November 2009 05:51 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

SQL> CREATE TABLE APPLICATION1(
  2   APPLICATION_ID VARCHAR2(10) PRIMARY KEY,
  3   APPLICATION_NAME VARCHAR2(20)
  4  )
  5  /

Table created.

SQL> CREATE TABLE TRAINING
  2  (  TRAINING_ID NUMBER(5) PRIMARY KEY,
  3   TRAINING_NAME VARCHAR2(20) NOT NULL,
  4   APPLICATION_ID VARCHAR2(10),
  5      FLAG CHAR(1),
  6   FOREIGN KEY (APPLICATION_ID)
  7      REFERENCES APPLICATION1(APPLICATION_ID))
  8  /

Table created.

SQL> CREATE SEQUENCE SEQ_TRAINING_ID
  2      START WITH 1
  3      INCREMENT BY 1
  4      NOMINVALUE
  5      NOMAXVALUE
  6      NOCYCLE
  7      CACHE 20
  8      NOORDER
  9  /

Sequence created.

SQL> CREATE OR REPLACE TRIGGER TR_TRAINING
  2  BEFORE INSERT
  3  ON TRAINING
  4  FOR EACH ROW
  5  BEGIN
  6    SELECT SEQ_TRAINING_ID.nextval
  7    INTO :new.TRAINING_ID
  8    FROM dual;
  9  END;
 10  /

Trigger created.

SQL> INSERT INTO TRAINING(TRAINING_NAME,FLAG) VALUES('ORACLE PL/SQL','01','N');
INSERT INTO TRAINING(TRAINING_NAME,FLAG) VALUES('ORACLE PL/SQL','01','N')
            *
ERROR at line 1:
ORA-00913: too many values


SQL> INSERT INTO TRAINING(TRAINING_NAME,application_id,flag) VALUES('ORACLE PL/SQL','01','N');
INSERT INTO TRAINING(TRAINING_NAME,application_id,flag) VALUES('ORACLE PL/SQL','01','N')
*
ERROR at line 1:
ORA-02291: integrity constraint (XXXXXX1.SYS_C0030204) violated - parent key not found


SQL> insert into application1 VALUES('01','WINDOWS');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO TRAINING(TRAINING_NAME,application_id,flag) VALUES('ORACLE PL/SQL','01','N');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TRAINING;

TRAINING_ID TRAINING_NAME        APPLICATIO F
----------- -------------------- ---------- -
          2 ORACLE PL/SQL        01         N

SQL> SPOOL OFF




In the table training the inserted value for training id is 2.
How to avoid this? I mean I do not want sequence to be incremented if the error is occured.

Thanks

[Updated on: Mon, 02 November 2009 05:54]

Report message to a moderator

Re: To increment column value before insert [message #429148 is a reply to message #429146] Mon, 02 November 2009 05:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Short answer - you can't. Oracle sequences are guaranteed to be unique and increasing, they are not gap free.

The only ways to get a gap free sequence involve locking the entire table, to ensure that no other process is inserting or amending the records - this can cause massive performance problems in tables that are frequently accessed.
Re: To increment column value before insert [message #429152 is a reply to message #429148] Mon, 02 November 2009 06:03 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

you can't. Oracle sequences are guaranteed to be unique and increasing, they are not gap free.



Sir, then what is the best way to increment the column TRAINING_ID for each insert.

I do not want that the table should be locked.
Thanks
Re: To increment column value before insert [message #429156 is a reply to message #429152] Mon, 02 November 2009 06:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can have the table locked, or you can have gaps in your sequence of ids, or you can not have Training_Id be a primary key - your choice.

If you're happy with gaps in the Ids,then your current solution is fine.

If you don't make Training_Id a primary key, then you can just insert a null value into Training_Id and have a DBMS_JOB or DBMS_SCHEDULER process running that will look for null values and populate them from a sequence.
Re: To increment column value before insert [message #429164 is a reply to message #429156] Mon, 02 November 2009 06:30 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, In an application how this issue is being handled ? What should be the approach need to follow
Many thanks for your suggesions
Re: To increment column value before insert [message #429166 is a reply to message #429164] Mon, 02 November 2009 06:33 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
prachij593 wrote on Mon, 02 November 2009 13:30
Sir, In an application how this issue is being handled ? What should be the approach need to follow
Many thanks for your suggesions

In a normal application, having gaps is NOT an issue.
Sure, Business Analysts may cry that it is, but watch them when you ask them to explain why exactly it is a problem.
Previous Topic: Tbale with more than 2 foreign keys referencing to tables
Next Topic: view to function
Goto Forum:
  


Current Time: Sun Dec 04 12:38:06 CST 2016

Total time taken to generate the page: 0.08639 seconds