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:= snumber||lname||'@met.af';
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.