aspose file tools*
The moose likes Oracle/OAS and the fly likes Problem in using triggers and sequences Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Problem in using triggers and sequences" Watch "Problem in using triggers and sequences" New topic
Author

Problem in using triggers and sequences

Pranav Pal
Ranch Hand

Joined: Nov 04, 2007
Posts: 74
I have an employee table, whose primary key , empId, is auto-generated.
I am using following sequence:


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


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


Hakuna Matata!
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
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
Ranch Hand

Joined: Nov 04, 2007
Posts: 74
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
Greenhorn

Joined: Mar 30, 2008
Posts: 8
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;


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

Joined: Mar 30, 2008
Posts: 8
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.




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



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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problem in using triggers and sequences