To_char Or To_number Best?

[31006558]

I'm running a SAP app on Oracle. I want to change a data type in one of the tables, because the end users tend not to understand the value of "number". These columbs are very important for calculations, so if there is any other character present other than numerical, the calculations doesnt work.

My Q:Should I use TO_CHAR OR TO_NUMBER to modify the data type in this scenario?

My pro-forma script looks something like this:

ALTER TABLE SAP_NOTIF
MODIFY (KMFROM TO_CHAR(NUMBER,'9999'),
(KMTO TO_CHAR(NUMBER,'9999'),
(MFROM TO_CHAR(NUMBER,'999999'),
(MTO TO_CHAR(NUMBER,'999999'),
(FROM_STATION TO_CHAR(NUMBER,'999999,99'),
(TO_STATION TO_CHAR(NUMBER,'999999,99')
;

Any suggestions would be wellcome!!
Thank you

[slimdave]

Here are my suggestions.

i) Don't modify an SAP table.

ii) Read the documentation, because ...

ALTER TABLE SAP_NOTIF
MODIFY (KMFROM TO_CHAR(NUMBER,'9999'),
(KMTO TO_CHAR(NUMBER,'9999'),
(MFROM TO_CHAR(NUMBER,'999999'),
(MTO TO_CHAR(NUMBER,'999999'),
(FROM_STATION TO_CHAR(NUMBER,'999999,99'),
(TO_STATION TO_CHAR(NUMBER,'999999,99')
;

... doesn't make any sense, and certainly won't run. What you probably want is something like...

ALTER TABLE SAP_NOTIF
MODIFY (KMFROM CHAR(4),
KMTO CHAR(4),
MFROM CHAR(6),
MTO CHAR(6),
FROM_STATION CHAR(9),
TO_STATION CHAR(9))
;

... however that will probably not run either.

iii) Create a view to change what the user see

iv) It scares me that you are considering even thinking about doing this -- you will break the application, you don't know SQL very well, and you are going to be fired from your job if you do this.