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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem in using triggers and sequences
 
Similar Threads
Oracle JDBC Thin Client behaviour concerning date data types and it's format.
Trigger performance
Auto Increment ID Field Table in the Oracle Database
inserting a sequence value to the table
ORA-00001: unique constraint exception