I'm loading data from a flat file into Oracle9i using SQL*Loader. It works perfectly but I have one question.
When I insert a number like '00001234' into a column with an INT datatype Oracle cuts of the zero's. When I select the rows after inserting the number Oracle shows me that the number '1234' is stored into the table. So my question is: is it posible to store a number into a table that has a zero in front of it?
I think I have to use another datatype but I don't know which one? I have a book here about SQL that say's that there are some datatypes to store a number in (INTEGER, SMALLINT, TINYINT, FLOAT and DOUBLE) but it seams that none of them are aible to store a number with a zero in front of it.
If you really need the leading zeroes (i.e. if this is something like a reference number rather than a numeric value), then you must store it in a VARCHAR2(9). There is no numeric datatype (INTEGER, SMALLINT, TINYINT, FLOAT and DOUBLE or whatever) that preserves the DISPLAY FORMAT of a number, they only store the VALUE. The VALUE of the number 000001234 is 1234.
Of course, you can still DISPLAY the value with leading zeroes when you select it, e.g.
SQL> select object_id from user_objects
2 where rownum <= 5