I have made a virtual column in Oracle, using the following code.
CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 (256); email varchar2 (256); BEGIN select substr( p.name, instr( p.name, ' ', -1 ) + 1 ) into lname from person p where p.id = id_; SELECT regexp_replace(p.service_no, '[^0-9]*', '') into snumber FROM person p where p.ID = id_; email:= firstname.lastname@example.org'; return email; end email_address;
The virtual column works fine and it does populate what i want to acheive in the virtual column. But the problem arises when i make a view using virtual column's table; population of view takes hell lot of time (max 5 mins). Here, i would like to mention that the view works perfectly fine if i don't use the function (i.e empty column of email). The code for view is as under
select distinct person.SERVICE_NO as Service_No, person.CNIC_NO as CNIC, person.NAME as NAME , card.CPLC_SERIAL_NO as Card_Number, child_dc.NAME as Child_DC, root_dc.NAME as Root_DC, person.OU as OU, person.EMAIL as Email from person_card inner join person on person_card.PERSON_ID = person.ID inner join card on person_card.CARD_ID = card.ID left outer join child_dc on person.CHILD_DC_ID = child_dc.ID left outer join root_dc on child_dc.ID = root_dc.ID;
What i presume is that when i make a virtual column, oracle forcibly asks me to keep the datatype length to 4000, therby, making it too large or heavy to populate. What should i do to make the view populate. I need to have a virtual column as email is not being entered by the application. Help required.