[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

I Want 0 to be displayed in front of digits in number column

I have a number column in a table, It has more than 20 million records in it.

But now the users want some new records in this format(0123, 01455, ..).

I cannot change the datatype to varchar2, as the table is not empty.

Adding a new column , updating the new column with old, dropping the old and renaming the new is not possible as in many places inside the DB, there are inserts without column list.

Also adding a new column, updating the new from old, nullify the old and changing the datatype is not possible, because so many million records need to be updated 3 times. DBA is not accepting this solution.

Is there any other way?
0
sakthikumar
Asked:
sakthikumar
  • 3
  • 3
  • 2
  • +4
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Oracle does not store insignificant digits in a number column so even if you insert the 0, it will not keep it.

To force a format on a number on display use TO_CHAR and the proper format mask.

Something like:  select to_char(numbercolumn,'000009') from dual;
0
 
johnsoneSenior Oracle DBACommented:
Alternately, you could create a view on the table that has the value formatted the way the user wants.  No updating, or creating of columns needed.
0
 
sdstuberCommented:
if it's 11g  create a virtual column on the table with the appropriate data type and content.  It's like having a view except without the extra object
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
DavidSenior Oracle Database AdministratorCommented:
The gpal is to display one or more leading zeros?  Then it's perhaps best addressed by setting the display mask:
COLUMN someval FORMAT 099.999
SELECT someval FROM sometab;
0
 
johnsoneSenior Oracle DBACommented:
The column format using:

COLUMN someval FORMAT 099.999

Is a SQL*Plus command.  It probably is not valid anywhere outside SQL*Plus.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
I cannot change the datatype to varchar2, as the table is not empty.  ---> Not just right, it would impact all the other code which refer to this column as a number but you are trying to change it to varchar2 now. So the best option is probably to go for a view and show the data in the format which is requried for your users instead of touching the table/columns.

Infact these type of things should be considered/done in the data design state and it would not be a direct / easy solution if that needs to happen after the current system has gone live is beign used already.
0
 
DavidSenior Oracle Database AdministratorCommented:
So far this discussion has been to accommodate the new user request, and our comments presume there's a technical solution.  Perhaps it's time to include why the users want this changed, what are the costs and benefits to the change, and to confirm senior management have signed off on this.  I'm simply curious, as I have never seen this approach in my home country.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
me too - had never come up this type of thing in the real sense. I had seen situations where there is a compelling need for this type of change and in those situations it would be dealt as a project/impact study etc.... because we are trying do deal with this after going far beyond and hence should be done carefully. The last thing anyone wants is an outage in your application because of some of your queries breaking or not working as desired/not producing results as desired.
0
 
sakthikumarAuthor Commented:
Already we have analyzed all the impacts, and the impacts are huge.

So, trying other solutions.

How I can use a view here, I am already having a table_name by one name,
how  I  can create an another object by the same name.
Please explain me clearly regarding the usage of view here.
0
 
johnsoneSenior Oracle DBACommented:
This would be for a new requirement correct?  So, a new name wouldn't be out of the question, right?

If you need to be able to access both the table and the view with the same name, that isn't going to happen.  If just the view is sufficient, then rename the table and create the view with the same name as the table.  Now, you are going to have to thoroughly test insert/update/deletes to make sure they are going to work as you expect, as doing those against views isn't always what you hope it will do.  You may need to create instead of triggers on the views.
0
 
paquicubaCommented:
Can you use DBMS_REDEFINITION to do an online table redefinition?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Good that i see thoughts are going on other options for your requirement. But anyway, in my view this is not a single question or there is no single answer for this, you need to treat it like a project and take it forward with all the diligence otherwise there are high chances that the ball may get dropped because you requirement is like plugging in into the new system which needs in & out knowledge of the existing systems and its tricks/traps if any.
0
 
slightwv (䄆 Netminder) Commented:
>>Please explain me clearly regarding the usage of view here.

You cannot create a new object with the same name.  You would need to rename the original table and create a view with the same name as the original table.

I like the suggetion by sdstuber above and use a virtual column if you are on 11g.  This way they can select the column as a number or the formatted column as a varchar2.

Any way you go something needs to change in the app.  Typically you store it as a number and use TO_CHAR with the correct formatting at query time or format it from the app itself at display time.
0

Featured Post

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.

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now