Usage of Oracle virtual column to increment value

Question

This is an attempt to the test the usage of the VIRTUAL column with a function to increment the value in a column.

I am using a function which would return the last two digits of the current year, concatenated with a hyphen followed by the next max value from the table column which is defined as a virtual column.

When I insert records into the table, it does insert successfully. However, when I am querying records, I am getting the below error

ORA-00036: maximum number of recursive SQL levels (50) exceeded

My question is, does it possible to achieve to increment the values (custom increment with the using a VIRTUAL column or this attempt is trivial?

The below function is compiled first by uncommenting the commented part and upon the creation of the table, the first SQL block is commented and I use the second SQL block

Function

CREATE OR REPLACE FUNCTION test_func (
   p_empl_id    NUMBER,
   empl_nm      VARCHAR2)
   RETURN VARCHAR2
   DETERMINISTIC
IS
   return_value  VARCHAR2(32);
BEGIN
   return_value := NULL;

--    SELECT    TO_CHAR (SYSDATE, 'YY')
--          || '-'
--          || LPAD (
--                TO_CHAR (NVL (MAX (TO_NUMBER (SUBSTR (001, 5))), 0) + 1),
--                5,
--                '0') into return_value
--     FROM dual;    

   SELECT    TO_CHAR (SYSDATE, 'YY')
          || '-'
          || LPAD (
                TO_CHAR (NVL (MAX (TO_NUMBER (SUBSTR (test_col, 5))), 0) + 1),
                5,
                '0')
     INTO return_value
     FROM test_table
    WHERE SUBSTR (test_col, 1, 2) = TO_CHAR (SYSDATE, 'YY');

   RETURN return_value;
END;
/

Table Structure

CREATE TABLE test_table
(
   empl_id       NUMBER,
   empl_nm       VARCHAR2 (50),
   monthly_sal   NUMBER (10, 2),
   bonus         NUMBER (10, 2),
   test_col      AS (test_func (empl_id, empl_nm)) VIRTUAL
);

Insert Statement

INSERT INTO test_table (empl_id,
                        empl_nm,
                        monthly_sal,
                        bonus)
   WITH data
        AS (SELECT 100 empl_id,
                   'AAA' empl_nm,
                   20000 monthly_sal,
                   3000 bonus
              FROM DUAL)
   SELECT *
     FROM data;

I have tried using the below SQL using a sequence, however, the sequence value is getting inserted every time I execute a SQL statement from the table

SELECT    TO_CHAR (SYSDATE, 'YY')
       || '-'
       || '000'
       || test_virtual_sequence.NEXTVAL
  FROM DUAL;

Answer 1

"ORA-00036: maximum number of recursive SQL levels (50) exceeded"

Part of the reason you get that error is because your function is not deterministic. Deterministic means that the same inputs will produce the same output. But this is not true of your function: it makes no use of the input parameters whatsoever. Instead the output is governed my how many records have already been inserted.

But worse than that, your function is manipulating the virtual column. This is similar to the mutating table error on Triggers which query their owning Table.

"this is a certainly a test case to know the usages of virtual column"

Virtual columns are a way to implement a certain amount of denormalization without risking different views of the same data. For instance, on a ORDER_LINE Table we might have columns for ITEM_COST and LINE_QTY. But we need a column for LINE_TOTAL (say to support a business rules on approvals). Before 11g we would have had to add a real column, and had the burden of maintaining it (probably in triggers or other procedural code). But now we can define it thus:

, line_qty as (item_cost * line_qty) virtual.

Another example is the sort of key in your case. It is a smart key, which users love, but data modellers hate: there are multiple components, in this case the year the record was created and a serial number. These should properly be modelled as separate columns, so the components can be manipulated cleanly in SQL without the need for substr() etc. Also we need to enforce the smart key's format with check constraints.

But, our users love smart keys, because they've been using these identifiers for years. So how can we give them the familiar key yet have proper data integrity? With a virtual column:

SQL> create table t23 (
  2     created date not null
  3     , serial_no number not null
  4     , ref_no as (to_char(created, 'YYYY')||'-'||lpad(serial_no, 5, '0')) virtual
  5 );

Table created.

SQL> insert into t23 (created, serial_no) values (sysdate, s23.nextval);

1 row created.

SQL> insert into t23 (created, serial_no) values (sysdate, s23.nextval);

1 row created.

SQL> select * from t23
  2  /

CREATED    SERIAL_NO REF_NO
--------- ---------- ----------
04-JUN-17          3 2017-00003
04-JUN-17          4 2017-00004

SQL> 

One benefit of the virtual column is that it synchronizes automatically if we change a value it depends on:

SQL> update t23
  2  set created = add_months(created, -12)
  3  where serial_no = 3
  4  /

1 row updated.

SQL> select * from t23
  2  /

CREATED    SERIAL_NO REF_NO
--------- ---------- ----------
04-JUN-16          3 2016-00003
04-JUN-17          4 2017-00004

SQL> 

This much we could implement with a view over the table. But virtual columns have the benefit that we can build indexes and constraints on them:

SQL> alter table t23
  2      add constraint t23_ref_no unique (ref_no)
  3  /

Table altered.

SQL> insert into t23 (created, serial_no) values (sysdate, s23.nextval)
  2  /

1 row created.

SQL> insert into t23 (created, serial_no) values (sysdate, s23.currval);
insert into t23 (created, serial_no) values (sysdate, s23.currval)
*
ERROR at line 1:
ORA-00001: unique constraint (C.T23_REF_NO) violated


SQL>

---

The other part of your question relates to incrementing a serial number with a fixed grouping (such as year). If you need to do this you can implement a code control table, such as I show in my answer to this other SO question.