CODEWITHSUNDEEP
oracle-databasesequence
user1207017
user1207017

Reputation: 9

oracle sequence for returning number as a string

I have a sequence which returns a number like this

CREATE SEQUENCE test.SEC_FORM 
START WITH 9000
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

And I created a trigger to insert this value into the table FORM.

CREATE OR REPLACE TRIGGER test.TRG_SEC_FORM
BEFORE INSERT
ON test.FORM
FOR EACH ROW
BEGIN
  IF :NEW.FORM_ID IS NULL THEN
      SELECT test.SEC_FORM.NEXTVAL INTO :NEW.FORM_ID FROM DUAL;
   END IF;
END ;

But the sequence is returning integer while the form_id in the form table is string. I need a string like '9000' etc.

How can I do this?

Upvotes: 1

Views: 2225

Answers (2)

sulica
sulica

Reputation: 111

Maybe you should consider to change FORM_ID data type from VARCHAR or STRING to NUMBER.

Upvotes: 0

penartur
penartur

Reputation: 9912

  1. Why is FORM_ID a string, why won't you make it an integer?

2.

CREATE OR REPLACE TRIGGER test.TRG_SEC_FORM
BEFORE INSERT
ON test.FORM
FOR EACH ROW
BEGIN
  IF :NEW.FORM_ID IS NULL THEN
      SELECT TO_CHAR(test.SEC_FORM.NEXTVAL) INTO :NEW.FORM_ID FROM DUAL;
   END IF;
END ;

Upvotes: 2

Related Questions