Help w/ SQL / Oracle - Building Tables?

[MsReece]

Hi,

I am new to SQL, taking a course at a State University. I am building a database and would like to have my Owner_ID field come from automatically taking the first 2 letters of that records Last_Name field and 5 random numbers (generated automatically from the system) (example: db43679).

Name Null? Type
OWNER_ID NOT NULL VARCHAR2(7)
FIRST_NAME NOT NULL CHAR(20)
LAST_NAME NOT NULL CHAR(20)
SALUTATION CHAR(4)
STREET VARCHAR2(20)

Is this possible? If so, could someone please assist?

To reemphasize...I am very new to SQL, so keeping it as simpl, yet specific, as possible would be VERY helpful.

Thank you,
MsReece

[ronverdonk]

If you want help with Oracle (see thread subject) you are in the wrong forum. I'll transfer you.

Ronald :cool:

[MsReece]

OK...thank you!
Ms Reece

If you want help with Oracle (see thread subject) you are in the wrong forum. I'll transfer you.

Ronald :cool:

[milonov]

Hello,
the main scenario:

1) create sequence to generate 5 numbers:

create sequence s_seq
minvalue 1
maxvalue 999999999999999999999999999
start with 10001
increment by 1
cache 20
 

2)create trigger before insert each row to fill OWNER_ID:

create or replace trigger TBI_YOURTABLE
  before insert on YOURTABLE
  for each row
declare
var_owner_id varchar2(50);
seq_number number;
begin
 
  select s_seq.nextval into seq_number from dual;
  var_owner_id := substr(:new.LAST_NAME, 1, 2) || to_char(seq_number);
  select var_owner_id into :new.OWNER_ID from dual;
 
end TBI_YOURTABLE;
 
 

Best Regards,
Michael Milonov
http://www.snotratech.com

Hi,

I am new to SQL, taking a course at a State University. I am building a database and would like to have my Owner_ID field come from automatically taking the first 2 letters of that records Last_Name field and 5 random numbers (generated automatically from the system) (example: db43679).

Name Null? Type
OWNER_ID NOT NULL VARCHAR2(7)
FIRST_NAME NOT NULL CHAR(20)
LAST_NAME NOT NULL CHAR(20)
SALUTATION CHAR(4)
STREET VARCHAR2(20)

Is this possible? If so, could someone please assist?

To reemphasize...I am very new to SQL, so keeping it as simpl, yet specific, as possible would be VERY helpful.

Thank you,
MsReece

[MsReece]

Thank you, Michael! I will try this.

Ms Reece

Hello,
the main scenario:

1) create sequence to generate 5 numbers:

create sequence s_seq
minvalue 1
maxvalue 999999999999999999999999999
start with 10001
increment by 1
cache 20
 

2)create trigger before insert each row to fill OWNER_ID:

create or replace trigger TBI_YOURTABLE
  before insert on YOURTABLE
  for each row
declare
var_owner_id varchar2(50);
seq_number number;
begin
 
  select s_seq.nextval into seq_number from dual;
  var_owner_id := substr(:new.LAST_NAME, 1, 2) || to_char(seq_number);
  select var_owner_id into :new.OWNER_ID from dual;
 
end TBI_YOURTABLE;
 
 

Best Regards,
Michael Milonov
http://www.snotratech.com