Sequence with date like 011110 with number

v_control_no  varchar2(10);

select CONTROL_NO_SEQ.nextval into v_control_no from dual;

data is 1004
I want to concatenate this with sysdate mmddyy

:qc_daily.control_no := to_char(sysdate,'mmddyy')|| ' '|| v_control_no;

gives me ora-06502. Control No in the datablock in char(10)

How to resolve this
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Franck PachotCommented:
Hi,
You must use to_char(v_control_no,'FM0999') if you want to transform a number into a 4 digit character string.
Regards,
Franck.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Franck PachotCommented:
(but note that when nextval will be > 9999 then it will not fit in char(10)...)
0
anumosesAuthor Commented:
:qc_daily.control_no := to_char(sysdate,'mmddyy')|| ' '|| to_char(v_control_no,'FM0999');

Still gives me ORA-06502 error
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
silly question but is :qc_daily.control_no a number?
0
anumosesAuthor Commented:
Its char
0
slightwv (䄆 Netminder) Commented:
duh...  I missed that part in the original post.

I think franckpachot is probably right in post #26286673.  What is the value of CONTROL_NO_SEQ.nextval?  With the suggested format, you can only put numbers in the field.

Try:
... to_char(v_control_no,'FM099');
0
anumosesAuthor Commented:
     v_control_no  varchar2(10);

select CONTROL_NO_SEQ.nextval into v_control_no from dual;

:qc_daily.control_no := to_char(sysdate,'mmddyy')|| ' '|| to_char(v_control_no,'FM0999');

Control_no in the form is character
I get ora-06502 error
0
slightwv (䄆 Netminder) Commented:
you didn't change the format.

Even a varchar2(10) can only hold 10 characters. 6 for the date mask, 1 for the space character. That leaves 3 for the sequence. 'FM0999' returns 4 characters. You need to change it to 'FM099'.
0
slightwv (䄆 Netminder) Commented:
Oh I see the confusion. Sorry. I had a typo in my post. I forgot the '3'.

I meant to say:
...you can only put 3 numbers...
0
Franck PachotCommented:
Yes I haven't seen the space.
If you have a doubt, just check length( to_char(sysdate,'mmddyy')|| ' '|| to_char(v_control_no,'FM0999')
But why not have varchar2(20) ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.