Add column to table with a default value from function

Question

I am pretty new to this, so please be nice ;)

I have created a function in PL/SQL where I feed the function a UserNo and it gives me an email-address.

What I would like to be able to do, is to add a column 'EMAIL' to 'My_table' that inserts the email based on the UserNo_column.

Something like this:

Alter table My_Table add EMAIL value {USERNO2EMAIL-function(USERNO_column)} not null;

This is my function:

create or replace function userno2email 
  (userno in number)
return
  varchar2 is email varchar2(64);

begin

  select O_EMAIL into email from USERS where O_USERNO = USERNO;
  return email;
end;

Thank you..

Answer 1

Shortly: looks like you'll have to use a trigger for that purpose.

Here's a demo.

users table and initial row:

SQL> create table users
  2  (o_userno number,
  3   o_email varchar2(64)
  4  );

Table created.

SQL> insert into users (o_userno, o_email) values (100, '[email protected]');

1 row created.

Function; I modified it a little bit so that it is kind of safer to use - compare it to your version (you'd rather inherit datatypes involved than hardcode them; what to do if nothing has being found? Include other exception handlers, if you think you need them; use prefix for parameter name):

SQL> create or replace function userno2email
  2    (par_userno in users.o_userno%type)
  3    return users.o_email%type
  4  is
  5    l_email users.o_email%type;
  6  begin
  7    select O_EMAIL
  8      into l_email
  9      from USERS
 10      where O_USERNO = par_USERNO;
 11    return l_email;
 12  exception
 13    when no_data_Found then
 14      return null;
 15  end;
 16  /

Function created.

This is the target table:

SQL> create table my_table
  2    (id number);

Table created.

This is what you tried to do. Syntax is now correct, but outcome is, sadly, wrong:

SQL> alter table my_table add email varchar2(64)
  2    default userno2email(id);
  default userno2email(id)
          *
ERROR at line 2:
ORA-04044: procedure, function, package, or type is not allowed here

You could have used default value with alter table statement - no problem with that, but - it has to be valid. For example:

SQL> alter table my_table add insert_date date default sysdate;

Table altered.

OK, let's add email column in allowed manner:

SQL> alter table my_table add email varchar2(64);

Table altered.

This is the trigger I mentioned at the beginning - upon insert into my_table, it'll fetch someone's e-mail address and put it into appropriate column:

SQL> create or replace trigger trg_bi_myt
  2    before insert on my_table
  3    for each row
  4  begin
  5    :new.email := userno2email(:new.id);
  6  end;
  7  /

Trigger created.

OK, let's test it:

SQL> insert into my_table (id) values (100);

1 row created.

SQL> select * from my_table;

        ID EMAIL                                    INSERT_DATE
---------- ---------------------------------------- -------------------
       100 [email protected]                              19.10.2023 18:54:01

SQL>

As you can see, although I inserted ID value only, trigger fetched e-mail address, while default clause on insert_date took care about that.

Answer 2

if you want the email id to be latest you can use a view instead of creating a additional column and trigger like below

   create or replace view my_table_view as 
   select id,userno2email(id) as email from my_table;

You can get the required data using the below query

    select * from my_table_view;