1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to Generate unique number in Virtual column

Discussion in 'SQL PL/SQL' started by sadheesh, Jul 18, 2014.

  1. sadheesh

    sadheesh Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    The below mentioned way i have achieved but every query or insert or update data virtual column data updation happen how to avoid whole table refresh data

    Code (SQL):
    CREATE TABLE EMP_VIRTUAL
    (
      EMP_NO  VARCHAR2(50),
      EMP_NAME VARCHAR2(30),
      EMP_UNIQUE_NO VARCHAR2(50) GENERATED ALWAYS AS  ("F_GETSEQ"()) virtual
    )

    CREATE OR REPLACE FUNCTION F_getseq RETURN varchar2

     AS
    a varchar2(30);
    BEGIN
    SELECT 'FT'||to_char(sysdate, 'DDMMRR')||TEST_SEQ.NEXTVAL INTO a FROM DUAL;
    RETURN a;
    END F_getseq;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    It is necessary to check if the NULL field, only then to generate for value.
    For example to transfer function invocation of generation of value to the trigger.

    Code (SQL):

    CREATE SEQUENCE test_seq1;
    CREATE TABLE emp_virtual1
    (
      emp_no  varchar2(50),
      emp_name varchar2(30),
      emp_unique_no varchar2(50)
    )
    ;
    CREATE OR REPLACE FUNCTION f_getseq1 RETURN varchar2
     AS
    a varchar2(30);
    BEGIN
        RETURN 'FT'||to_char(sysdate, 'ddmmrr')||test_seq1.NEXTVAL;
    END f_getseq1;
    /
    CREATE OR REPLACE TRIGGER emp_virtual_tbui
    BEFORE INSERT OR UPDATE
    ON emp_virtual1
    FOR each ROW
    BEGIN
        IF :NEW.emp_unique_no IS NULL THEN
           :NEW.emp_unique_no  := f_getseq1;
                     
        END IF;    
    END;        
    /

    INSERT  INTO emp_virtual1(emp_no,emp_name) VALUES (1,'test1');
    commit;
    SELECT * FROM emp_virtual1 WHERE emp_no = 1;
    UPDATE emp_virtual1 SET emp_name = 'new' WHERE emp_no =1;
    commit;
    SELECT * FROM emp_virtual1 WHERE emp_no = 1;
       
     
     
  3. sadheesh

    sadheesh Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for support, its possible to generate unique number in virtual column without using trigger?
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    That value didn't change in case of UPDATE - it is necessary to use the trigger.

    Note : F_GETSEQ - shall be deterministic if it is used in the column description in "create table".
    as there doesn't correspond the description of the varchar2 (50) column and result of the varchar2 function.