Kashif Masood Hashmi Blog

Here are findings regarding Oracle 11g, here are with complete examples. 

1)      Seudo Column in Tables - apply any complex formula here

in this example: update Sale_category column based on value of sales_amt directly

without user interaction, its like a DEFAULT value on column

---------------------------------------------------------------------------------

create table hr.sales

(

   sales_id      number,

   cust_id       number,

   sales_amt     number,

   sale_category varchar2(6)

   generated always as

   (

      case

         when sales_amt <= 10000

            then 'LOW'

         when sales_amt > 10000

            and sales_amt <= 100000

            then case

               when cust_id < 101 then 'LOW'

               when cust_id between 101 and 200 then 'MEDIUM'

               else 'MEDIUM'

            end

         when sales_amt > 100000

            and sales_amt <= 1000000

            then case

               when cust_id < 101 then 'MEDIUM'

               when cust_id between 101 and 200 then 'HIGH'

               else 'ULTRA'

            end

         else 'ULTRA'

      end

    ) virtual

);

insert into hr.sales (sales_id, cust_id, sales_amt) values (1,1,100);

insert into hr.sales (sales_id, cust_id, sales_amt) values (2,101,100001);

insert into hr.sales (sales_id, cust_id, sales_amt) values (3,102,1500);

insert into hr.sales (sales_id, cust_id, sales_amt) values (4,102,10000);

insert into hr.sales (sales_id, cust_id, sales_amt) values (5,101,100000);

insert into hr.sales (sales_id, cust_id, sales_amt) values (5,101,1000000);

insert into hr.sales (sales_id, cust_id, sales_amt) values (6,102,10000000);

select * from hr.sales;

  SALES_ID    CUST_ID  SALES_AMT SALE_C

---------- ---------- ---------- ------

         1          1        100 LOW

         2        101     100001 HIGH

         3        102       1500 LOW

         4        102      10000 LOW

         5        101     100000 MEDIUM

         5        101    1000000 HIGH

         6        102   10000000 ULTRA

7 rows selected.

drop table hr.sales;

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

2)      Using a Constraint to Enforce a Phone Number Format with Regular Expression

Regular expressions are useful for enforcing constraints—for example, to ensure that phone numbers are entered into the

database in a standard format. Example 3-1 creates a contacts table and adds a CHECK constraint to the p_number column to

enforce the below format model:

(XXX) XXX-XXXXXXX

(092) 333-1234567

CREATE TABLE hr.contacts (

  cust_name    VARCHAR2(30),

  cust_mobile  VARCHAR2(30)

  CONSTRAINT c_contacts_pnf

  CHECK (REGEXP_LIKE (cust_mobile, '^\(\d{3}\) \d{3}-\d{7}$'))

);

test entering wrong format in cust_mobile column

------------------------------------------------

HR.@.xe > insert into hr.contacts values ('My Customer 1','123456789');

insert into hr.contacts values ('My Customer 1','123456789')

*

ERROR at line 1:

ORA-02290: check constraint (HR.C_CONTACTS_PNF) violated

HR.@.xe > insert into hr.contacts values ('My Customer 1','(092) 333-123456');

insert into hr.contacts values ('My Customer 1','(092) 333-123456')

*

ERROR at line 1:

ORA-02290: check constraint (HR.C_CONTACTS_PNF) violated

now make a correct entry ...

----------------------------

  1* insert into hr.contacts values ('My Customer 1','(092) 333-1234567')

HR.@.xe > /

1 row created.

commit;