Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sequence with date like 011110 with number

Posted on 2010-01-11
10
Medium Priority
?
464 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 78

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 78

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 78

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 78

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

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.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

578 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