Hi,
On a 10g database server I have a table with the following definition:
SQL> desc exp
Name Null? Type
----------------------------------------- -------- ----------------------------
TDATE DATE
CONF VARCHAR2(10)
There are only two rows in the table:
SQL> select * from exp;
TDATE CONF
--------- ----------
16-JUN-09 0906160001
16-JUN-09 0906160002
When inserting a new row into the table, TDATE is always the current date, which is obtained from sysdate. The CONF column is a unique "confirmation number" in the format of YYMMDD plus a 4-character serial number, i.e., transactions on the same have the first 6 characters identical and only the last four padded serial number different. I tried the following SQL:
insert into exp values (
sysdate,
(select to_char(sysdate, 'yymmdd') from dual)||(select to_char(count(tdate)+1,'0999') from exp where trunc(tdate) = to_char(sysdate))
)
and got this error:
ORA-12899: value too large for column "STUDENT"."EXP"."CONF" (actual: 11,
maximum: 10)
Wondering why the number becomes 11 characters, I changed the mask from '0999' to '099' and the statement got executed:
insert into exp values (
sysdate,
(select to_char(sysdate, 'yymmdd') from dual)||(select to_char(count(tdate)+1, '099') from exp where trunc(tdate) = to_char(sysdate))
)
SQL>
But checking to see what was inserted, I got:
SQL> select * from exp;
TDATE CONF
--------- ----------
16-JUN-09 0906160001
16-JUN-09 0906160002
16-JUN-09 090616 003
What does the extra space character come from and how not to get it in the confirmation number?
Thanks for helping!
Newman