Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Oracle
hi all,
The following is my select query,
select substr(trim(to_char(AMOUNT, '00000000000.00')),1,11) from mytable where status_code='V';

I got the right result while I run this query in Oracle 10g (windows Server), but while i run this same query in Oracle 11g (Linux Server) i got the result as like this below
###########
###########
###########
I don't know why this '#' symbol occurs instead of the values. Kindly give your suggestion.
 
Regards,
Bluesathish.
Posted 27-Dec-12 21:18pm
Edited 27-Dec-12 23:06pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Your format mask format & length should be match with the value you retrieved otherwise you will get this.
 
In your query execution order will be like below,
 
1. to_char
2. trim
3. substr
 
Try the following query, you get the problem,
 
<pre>
select to_char(trim(substr(&AMOUNT,1,11)),'00000000000.00') from mytable where status_code='V';
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
Your format mask format & length should be match with the value you retrieved otherwise you will get this.
 
In your query execution order will be like below,
 
1. to_char
2. trim
3. substr
 
Try the following query, you get the problem,
 
select to_char(trim(substr(&AMOUNT,1,11)),'00000000000.00') from mytable where status_code='V';
  Permalink  
Comments
bluesathish at 28-Dec-12 6:15am
   
thanks for your reply sampath, but your code returns ORA-01722: Invalid number error. any idea about this?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

My Oracle data Provider was the problem, previously i've using devart dotConnect v5.0, when i upgrade this to v7.5 its working fine. ie., while the time of data insertion i've to do this. No problem with the query which i posted.
 
Note: This upgradation is due to the migration of oracle 10g to 11g. The problem occurs for the Oracle 11g server connected via devart dotConnect v5.0. So we've to upgrade devart dotConnect version 7.5 inorder to connect Oracle 11g.
 
Thanks for all who replied my question.
 
Regards,
Bluesathish
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,105
1 DamithSL 5,079
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,617
4 Kornfeld Eliyahu Peter 4,384


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 10 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100