Solved

Oracle To_Char: Error > Invalid Number .. Different Oracle Terminals

Posted on 2003-12-09
10
1,143 Views
Last Modified: 2009-12-16
Hi Experts,
I have 2 Oracle Servers viz..
Hostname: OraLin .. Running on Linux. Version:8.1.6.1.0 Enterprise Edition
Hostname:Ora8 .. Running on Windows 2K Version:8.1.7.0.0 Enterprise Edition

I have a sample Table
CREATE TABLE TEST (TEST_COL VARCHAR2(10) NULL)
INSERT INTO TEST VALUES ('TEST VALUE')
in both the Databases

Whenever I query
SELECT TO_CHAR(TEST_COL) TEST_COL FROM TEST

The First Database throws an error ...
ORA-01722: invalid number
While the second database returns me the row ...
Any Answers ??
Thanks in Advance for ur time ..
Regds,
Karthick.
0
Comment
Question by:karthick79
10 Comments
 
LVL 24

Expert Comment

by:shivsa
ID: 9910192
0
 
LVL 24

Expert Comment

by:shivsa
ID: 9910202
if u running this on linux and copying the test from windows, windows editor sometime may put extra character in the files. so please be aware when running on linux that u have to remove those characters.

----------------------------
How to fix it
The fix depends upon the exact expression which caused the problem. The following guide lists the possible SQL expressions which can give this error, with their most likely cause. When addressing this error, keep in mind that it can indicate a simple keystroke problem with the query, or a deeper problem with the query logic, or even the presence of bad data in the database itself.

You are doing an INSERT INTO ... VALUES (...)
One of the data items you are trying to insert is an invalid number. Locate and correct it.

If all of the numbers appear to be valid, then you probably have your columns out of order, and an item in the VALUES clause is being inserted into a NUMBER column instead of the expected VARCHAR2 column. This can happen when a table has columns added or removed
0
 
LVL 7

Expert Comment

by:jocave
ID: 9910218
I believe the problem is that to_char is unnecessary in this query.  Your column is already a string, there's no need to cast it to a string again.  In this case, the to_char function is, at best, a no op.

I don't have access to an 8.1.6 database, but I'd wager that on 8.1.6, there was no version of the to_char function that accepted a varchar2 parameter.  Thus, the 8.1.6 database has to convert the string input parameter to a number in order to call the to_char function that accepts a number.  Since this conversion isn't valid-- your data isn't a number-- you get an error.

You can simplify your life by just using the query

SELECT testCol
  FROM test;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9911995
karthick79:

oracle TO_CHAR(column or expression, FORMAT) function requires the first parameter (column or expression) SHOULD be either NUMBER or DATE date type , and convert them to String. Therefore, you should Never use To_CHAR function againg VARCHAR2 OR CHAR data types.

Hope this helps
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Accepted Solution

by:
SDutta earned 250 total points
ID: 9914337
In Oracle 8.1.7 a change was made to fix bug 914145 which caused a change in the TO_CHAR functionality.

The change in behaviour in the TO_CHAR function from 8.1.7 onwards is that no numeric to string conversion is done if the parameter is of type char (i.e., NCHAR, NVARCHAR2, CLOB and NCLOB) and return as it is in the database
characterset format.

The above is not documented in the 8i documentation but is present in the 9i docs.

0
 
LVL 1

Author Comment

by:karthick79
ID: 9918573
Thanx SDutta:
You seem to have taken me to the nearest problem resolution .. but I still need to get that problem solved in 8.1.6 Enterprise Edition ..
Any Answers ??
And Where Can i get a ECopy of Oracle 9i Docs, wherein they have mentioned about this bug ..

And anywayz .. u answer this or not .. the points are your for the taking mate ..

Regds,
Karthick.
0
 
LVL 15

Expert Comment

by:andrewst
ID: 9919958
> Any Answers ??

Presumably the resolution is not to apply the redundant TO_CHAR to a VARCHAR2 column?

Or upgrade to 8.1.7...

Just pointing this out - others gave you the answer already.
0
 
LVL 10

Expert Comment

by:SDutta
ID: 9921658
Hi Karthick,

As everyone mentions the best solution for you is not to use the to_char function for NCHAR types in 8.1.6. What is the reason you want to do a TO_CHAR on the VARCHAR2 field ?

If the field may be either number or varchar2, even in 8.0 there will be an implicit conversion of datatype if you use a string function on a numeric date field, so you don't really need to do the to_char.

FYI the 9.2 documentation I mentioned that allows varchar parameters is here :
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions133a.htm#SQLRF06128

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now