Problem in using triggers and sequences

[Pranav Pal]

I have an employee table, whose primary key , empId, is auto-generated.
I am using following sequence:

 CREATE SEQUENCE  EMPID_SEQ 
 MINVALUE 1 MAXVALUE 9999 
INCREMENT BY 1 START WITH 1 CACHE 3 CYCLE ;
 

And I amusing this sequence to generate auto-id in a trigger which is fired when an insert in the employee table is made:

create or replace trigger tri_emp_bef_ins
before insert on employee
for each row
declare
  mon varchar2(2);
  yr varchar2(2);
begin
  select to_char(sysdate,'MM') into mon from dual;
  select to_char(sysdate,'YY') into yr from dual;
  select mon || yr || to_char(empid_seq.nextval,'0009') into :new.empId from dual;
end;
 

But I am getting auto-id like 0308 0001 instead of 03080001

Can I know what is the reason behind that and how to remove that space?
Thanks in advance

[Agador Paloi]

I played with this some and noticed Oracle always put a leading space on the sequence mask . Should be able to use LTRIM to get rid of it:

select mon || yr || LTRIM(to_char(empid_seq.nextval,'0009'),' ') into :new.empId from dual;

[Pranav Pal]

Originally posted by Agador Paloi:
I played with this some and noticed Oracle always put a leading space on the sequence mask

I also played like you and got the same conclusion and solution

Thanks...

[Craig Collins]

I also find the leading space after converting the sequence to a character!?

I thought that if the empID in the table is a NUMBER datatype it may be better to explicitly cast the generated key value into a NUMBER. Any error generated when trying to convert to number could be handled in an exception and then calculate and return a valid number - also perhaps it is uneccessary to declare the local variables and we could then make only one call to the SQL engine;

begin
  select to_number(to_char(sysdate,'MM')||
                   to_char(sysdate,'YY')||
                   trim(to_char(my_seq.nextval,'0009'))
         ) into :new.empId 
    from dual;
 
exception
  when invalid_number then
    select my_seq.nextval into :new.empId from dual;
end;
/

[ March 31, 2008: Message edited by: Craig Collins ]

[Craig Collins]

From the oracle docs,

Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language.

I'm guessing to accomodate the (possible) minus sign oracle pads the 4 digit format model to 5 characters!

So a solution using TO_CHAR is to use a Format Model Modifier to control this blank padding.

select to_number(to_char(sysdate,'MM')||
                 to_char(sysdate,'YY')||
                 to_char(my_seq.nextval,'[B]FM[/B]0009')
       ) into :new.empId from dual;

Could also use LPAD - LPAD definition in Oracle SQL Language Reference

select to_number(to_char(sysdate,'MM')||
                 to_char(sysdate,'YY')||
                 lpad(my_seq.nextval,4,'0')
       ) into :new.empId from dual;

lpad(string, length of string returned, character to use to pad).

lpad return a string of either varchar2, nvarchar2, or LOB depending on the datatype of your string.