ORA-12729: invalid character class in regular expression

Cause :- Invalid character set using in regular expression.

SQL> CREATE TABLE REGEXP_LIKE_TEST (ID NUMBER, NAME VARCHAR2(20), SALARY NUMBER);

Table created.

SQL>
SQL> ALTER TABLE REGEXP_LIKE_TEST
  2  ADD CONSTRAINT REGEXP_LIKE_TEST_CONST CHECK (REGEXP_LIKE(NAME,'[[:ALPHA:]]')) NOVALIDATE ;

Table altered.

SQL>
SQL> INSERT INTO REGEXP_LIKE_TEST VALUES(10, 'RAJIB',5000);
INSERT INTO REGEXP_LIKE_TEST VALUES(10, 'RAJIB',5000)
                 *
ERROR at line 1:
ORA-12729: invalid character class in regular expression

Solution :- Using valid character set within regular expression.


SQL> ALTER TABLE REGEXP_LIKE_TEST
  2  DROP CONSTRAINT REGEXP_LIKE_TEST_CONST;

Table altered.

SQL> ALTER TABLE REGEXP_LIKE_TEST
  2  ADD CONSTRAINT REGEXP_LIKE_TEST_CONST CHECK (REGEXP_LIKE(NAME,'[[:alpha:]]')) NOVALIDATE ;

Table altered.

SQL> INSERT INTO REGEXP_LIKE_TEST VALUES(10, 'RAJIB',5000);

1 row created.

SQL>

Multi-Table In oracle database.

Multi-table insert is a new feature of Oracle 9i Release 1 (9.0). An extension to INSERT..SELECT, this feature enables us to define multiple insert targets for a source data.

SQL> CONN RND/rnd
Connected.
SQL>
SQL>
SQL>
SQL> CREATE TABLE MULTI1 (ID NUMBER, NAME VARCHAR2(300), SALARY NUMBER);

Table created.

SQL>
SQL> CREATE TABLE MULTI2(ID NUMBER, NAME VARCHAR2(300), SALARY NUMBER);

Table created.

SQL>
SQL> INSERT ALL
  2  WHEN EMPLOYEE_ID >=140 THEN
  3  INTO MULTI1(ID, NAME, SALARY)
  4  VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
  5  ELSE
  6  INTO MULTI2(ID, NAME, SALARY)
  7  VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
  8  SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
  9  FROM EMP;

107 rows created.

SQL>
SQL> SELECT MAX(ID), MIN(ID)  FROM MULTI1;

   MAX(ID)    MIN(ID)
---------- ----------
       206        140

SQL>
SQL> SELECT MAX(ID), MIN(ID)  FROM MULTI2;

   MAX(ID)    MIN(ID)
---------- ----------
       139        100

SQL>

General comparison function uses in oracle database.

There are two types of general comparison function uses in oracle database.

1. GREATEST    Highest value from set of values.
2. LEAST    Lowest value from set of values.

EXAMPLE :-

SQL> select greatest(1,2,3,4) from dual;

GREATEST(1,2,3,4)
-----------------
                4

SQL>
SQL> select least(1,2,3,4) from dual;

LEAST(1,2,3,4)
--------------
             1

SQL> SELECT GREATEST(SALARY,COMMISSION_PCT), LEAST(SALARY,COMMISSION_PCT)
  2  FROM EMPLOYEES
  3  WHERE COMMISSION_PCT IS NOT NULL;

GREATEST(SALARY,COMMISSION_PCT) LEAST(SALARY,COMMISSION_PCT)
------------------------------- ----------------------------
                          14000                           .4
                          13500                           .3
                          12000                           .3
                          11000                           .3
                          10500                           .2
                          10000                           .3
                           9500                          .25
                           9000                          .25
                           8000                           .2
35 rows selected.


SQL> SELECT GREATEST('ABC','AAB','AAA') FROM DUAL;

GRE
---
ABC

SQL> SELECT GREATEST('ABC','ABD','AAA') FROM DUAL;

GRE
---
ABD

SQL> SELECT GREATEST('ABC','ABCD','AAA') FROM DUAL;

GREA
----
ABCD

SQL> SELECT LEAST('ABC','ABCD','AAA') FROM DUAL;

LEA
---
AAA