?
Solved

Sequence with date like 011110 with number

Posted on 2010-01-11
10
Medium Priority
?
461 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 1000 total points
ID: 26286660
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26286673
(but note that when nextval will be > 9999 then it will not fit in char(10)...)
0
 
LVL 6

Author Comment

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

Still gives me ORA-06502 error
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26286889
silly question but is :qc_daily.control_no a number?
0
 
LVL 6

Author Comment

by:anumoses
ID: 26286897
Its char
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26287003
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
 
LVL 6

Author Comment

by:anumoses
ID: 26287029
     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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 26287056
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26287076
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26287142
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question