Solved

Issue with an Oracle function works in .

Posted on 2004-10-19
12
511 Views
Last Modified: 2009-07-29
Hi!,
Follow-up to http://www.experts-exchange.com/Databases/Oracle/Q_21154434.html.

The function, whose text is provided below, is used to provide the number in text. It works in Oracle 8.0.5 but fails in 8.1.7.
It works upto three digits but fails beyond 999. Pl see the query results below:
SQLWKS> select newmsr(999) from dual
     2>
NEWMSR(999)                                                                    
--------------------------------------------------------------------------------
 NINE HUNDRED NINETY NINE                                                      
1 row selected.
SQLWKS> select newmsr(100) from dual
     2>
NEWMSR(100)                                                                    
--------------------------------------------------------------------------------
 ONE HUNDRED                                                                    
1 row selected.
SQLWKS> select newmsr(1000) from dual
     2>
NEWMSR(1000)                                                                    
--------------------------------------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 100
ORA-06512: at line 1


Btw, the same function works fine on Oracle 8.0.5, see the results in the other window.
SQLWKS> select newmsr(1000) from dual
     2>
NEWMSR(1000)                                                                    
--------------------------------------------------------------------------------
 ONE THOUSAND                                                                  
1 row selected.

Thanks in advance,
Srini




The function text is given below.:-

FUNCTION nsc_num_to_words (
temp_var   IN  CHAR )
RETURN VARCHAR2 IS
input_data       CHAR(30);
alpha_var        CHAR(30);
total_var        VARCHAR2(2000);
chk_number1      NUMBER(1);
chk_number2      NUMBER(1);
chk_number3      NUMBER(1);
last_two         NUMBER(2);
start_position   NUMBER;
array_index      NUMBER;
higher_position  NUMBER;
disp_position    NUMBER;
use_number       NUMBER;
TYPE data_text IS TABLE OF CHAR(40)
     INDEX BY binary_integer;
one_thru_nine    data_text;
ten_thru_ninety  data_text;
higher_group     data_text;
disp_var         data_text;
BEGIN
    one_thru_nine (001) := ' ONE';
    one_thru_nine (002) := ' TWO';
    one_thru_nine (003) := ' THREE';
    one_thru_nine (004) := ' FOUR';
    one_thru_nine (005) := ' FIVE';
    one_thru_nine (006) := ' SIX';
    one_thru_nine (007) := ' SEVEN';
    one_thru_nine (008) := ' EIGHT';
    one_thru_nine (009) := ' NINE';
    one_thru_nine (010) := ' TEN';
    one_thru_nine (011) := ' ELEVEN';
    one_thru_nine (012) := ' TWELVE';
    one_thru_nine (013) := ' THIRTEEN';
    one_thru_nine (014) := ' FOURTEEN';
    one_thru_nine (015) := ' FIFTEEN';
    one_thru_nine (016) := ' SIXTEEN';
    one_thru_nine (017) := ' SEVENTEEN';
    one_thru_nine (018) := ' EIGHTEEN';
    one_thru_nine (019) := ' NINETEEN';
    ten_thru_ninety (001) := ' TEN';
    ten_thru_ninety (002) := ' TWENTY';
    ten_thru_ninety (003) := ' THIRTY';
    ten_thru_ninety (004) := ' FORTY';
    ten_thru_ninety (005) := ' FIFTY';
    ten_thru_ninety (006) := ' SIXTY';
    ten_thru_ninety (007) := ' SEVENTY';
    ten_thru_ninety (008) := ' EIGHTY';
    ten_thru_ninety (009) := ' NINETY';
    higher_group (001) := ' HUNDRED';
    higher_group (002) := ' THOUSAND';
    higher_group (003) := ' MILLION';
    higher_group (004) := ' BILLION';
    higher_group (005) := ' TRILLION';
    higher_group (006) := ' QUADRILLION';
    higher_group (007) := ' QUINTILLION';
    higher_group (008) := ' SEXTILLION';
    higher_group (009) := ' SEPTILLION';
    higher_group (010) := ' OCTILLION';
    input_data   := temp_var;
    SELECT NVL(LENGTH(temp_var),0)
      INTO array_index
      FROM DUAL;
    higher_position := 1;
    disp_position   := 0;
    start_position  := 0;
    WHILE ( array_index > start_position )
       LOOP
           IF  ( higher_position > 1 ) THEN
               disp_position := disp_position + 1;
               disp_var (disp_position) := higher_group (higher_position);
           END IF;
           use_number       := array_index - start_position ;
           start_position   := start_position + 1;
           IF  ( use_number != ' ') THEN
               chk_number1    := SUBSTR(input_data,use_number,1) ;
           ELSE
               chk_number1    := 0;
           END IF;
           use_number       := array_index - start_position ;
           start_position   := start_position + 1;
           IF  ( use_number != ' ') THEN
               chk_number2    := SUBSTR(input_data,use_number,1) ;
           ELSE
               chk_number2    := 0;
           END IF;
           last_two       := SUBSTR(input_data,use_number,2) ;
           use_number       := array_index - start_position ;
           start_position   := start_position + 1;
           IF  ( use_number != ' ') THEN
               chk_number3    := SUBSTR(input_data,use_number,1) ;
           ELSE
               chk_number3    := 0;
           END IF;
           IF  ( higher_position > 1 AND
                 chk_number1 = 0     AND
                 chk_number2 = 0     AND
                 chk_number3 = 0     ) THEN
               disp_position := disp_position - 1;
               disp_var (disp_position) := ' ';
           END IF;
           IF  ( chk_number1  <> 0     AND
                 chk_number1  <> ' '   AND
                 chk_number2  <> '1' ) THEN
               disp_position := disp_position + 1;
               disp_var (disp_position) := one_thru_nine (chk_number1);
           END IF;
           IF  ( chk_number2  <> 0     AND
                 chk_number2  <> ' ' ) THEN
               IF  ( chk_number1 <> '0' AND
                     chk_number1 <> ' ' AND
                     chk_number2 =  '1' ) THEN
                   disp_position := disp_position + 1;
                   disp_var (disp_position) := one_thru_nine (last_two);
               ELSE
                   disp_position := disp_position + 1;
                   disp_var (disp_position) := ten_thru_ninety (chk_number2);
               END IF;
           END IF;
           IF  ( chk_number3  <> 0     AND
                 chk_number3  <> ' ' ) THEN
               disp_position := disp_position + 1;
               disp_var (disp_position) := higher_group (1) ;
               disp_position := disp_position + 1;
               disp_var (disp_position) := one_thru_nine (chk_number3) ;
           END IF;
           higher_position := higher_position + 1 ;
       END LOOP;
    higher_position := disp_position ;
    WHILE ( higher_position > 0  )
       LOOP
           total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
           higher_position := higher_position - 1;
       END LOOP;
   RETURN total_var;
END;
0
Comment
Question by:sitaparijat
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 50 total points
ID: 12355471
The function you provided should never work...

Couple of problems, one, the function you have provided is not the function that you are calling in your examples (unless you have specified a synonym!?).  Also,  use_number is a number, so when you do "IF (use_number != ' ') THEN", it will try to implicitly convert the space (' ') to a number, which will fail every time.
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 12355533
Although the original points above are relevant, I'm presuming that you are having a problem with the code posted on your previous question.

The problem is that the input parameter was changed to a VARCHAR2, however, you do not use that in your code, you use input_data.  Simple solution for you (alteration to the code posted in your other question):

CREATE OR REPLACE FUNCTION nsc_num_to_words (
temp_var IN VARCHAR2 )
RETURN VARCHAR2
IS
input_data VARCHAR2(30);   -- This used to be a char...
0
 

Author Comment

by:sitaparijat
ID: 12355534
HI grim_toaster,
 It is not a synonym but the same fucntion with a different name.I am sorry I used a copy of the function so that is the reason for the confusion.

I am not an expert in Oracle programming but the issue is the same code works fine in 8.0.5 and that was what I was showing below that the same procedure works fine in the other version (8..0.5) , even in ver 8.1.7 it works fine for values under 999.
I am not claiming that the script is perfect but some one like you could spot an easy fix to overcome the issue.

Thanks
0
 

Author Comment

by:sitaparijat
ID: 12355559
Hi gt,
i tried but it did not work. can you take a look? I reproduce the output. May be I made some mistake..
Regards,
Srini


SQLWKS> CREATE OR REPLACE FUNCTION newmsr (temp_var IN VARCHAR2 )
     2> RETURN VARCHAR2
     3> IS input_data VARCHAR2(30);
     4> alpha_var CHAR(30);
     5> total_var VARCHAR2(2000);
     6> chk_number1 NUMBER(1);
     7> chk_number2 NUMBER(1);
     8> chk_number3 NUMBER(1);
     9> last_two NUMBER(2);
    10> start_position NUMBER;
    11> array_index NUMBER;
    12> higher_position NUMBER;
    13> disp_position NUMBER;
    14> use_number NUMBER;
    15> TYPE data_text IS TABLE OF CHAR(40)
    16> INDEX BY binary_integer;
    17> one_thru_nine data_text;
    18> ten_thru_ninety data_text;
    19> higher_group data_text;
    20> disp_var data_text;
    21> BEGIN
    22> one_thru_nine (001) := ' ONE';
    23> one_thru_nine (002) := ' TWO';
    24> one_thru_nine (003) := ' THREE';
    25> one_thru_nine (004) := ' FOUR';
    26> one_thru_nine (005) := ' FIVE';
    27> one_thru_nine (006) := ' SIX';
    28> one_thru_nine (007) := ' SEVEN';
    29> one_thru_nine (008) := ' EIGHT';
    30> one_thru_nine (009) := ' NINE';
    31> one_thru_nine (010) := ' TEN';
    32> one_thru_nine (011) := ' ELEVEN';
    33> one_thru_nine (012) := ' TWELVE';
    34> one_thru_nine (013) := ' THIRTEEN';
    35> one_thru_nine (014) := ' FOURTEEN';
    36> one_thru_nine (015) := ' FIFTEEN';
    37> one_thru_nine (016) := ' SIXTEEN';
    38> one_thru_nine (017) := ' SEVENTEEN';
    39> one_thru_nine (018) := ' EIGHTEEN';
    40> one_thru_nine (019) := ' NINETEEN';
    41> ten_thru_ninety (001) := ' TEN';
    42> ten_thru_ninety (002) := ' TWENTY';
    43> ten_thru_ninety (003) := ' THIRTY';
    44> ten_thru_ninety (004) := ' FORTY';
    45> ten_thru_ninety (005) := ' FIFTY';
    46> ten_thru_ninety (006) := ' SIXTY';
    47> ten_thru_ninety (007) := ' SEVENTY';
    48> ten_thru_ninety (008) := ' EIGHTY';
    49> ten_thru_ninety (009) := ' NINETY';
    50> higher_group (001) := ' HUNDRED';
    51> higher_group (002) := ' THOUSAND';
    52> higher_group (003) := ' MILLION';
    53> higher_group (004) := ' BILLION';
    54> higher_group (005) := ' TRILLION';
    55> higher_group (006) := ' QUADRILLION';
    56> higher_group (007) := ' QUINTILLION';
    57> higher_group (008) := ' SEXTILLION';
    58> higher_group (009) := ' SEPTILLION';
    59> higher_group (010) := ' OCTILLION';
    60> input_data := temp_var;
    61> SELECT NVL(LENGTH(temp_var),0)
    62> INTO array_index
    63> FROM DUAL;
    64> higher_position := 1;
    65> disp_position := 0;
    66> start_position := 0;
    67> WHILE ( array_index > start_position )
    68> LOOP
    69> IF ( higher_position > 1 ) THEN
    70> disp_position := disp_position + 1;
    71> disp_var (disp_position) := higher_group (higher_position);
    72> END IF;
    73> use_number := array_index - start_position ;
    74> start_position := start_position + 1;
    75> IF ( use_number != ' ') THEN
    76> chk_number1 := SUBSTR(input_data,use_number,1) ;
    77> ELSE
    78> chk_number1 := 0;
    79> END IF;
    80> use_number := array_index - start_position ;
    81> start_position := start_position + 1;
    82> IF ( use_number != ' ') THEN
    83> chk_number2 := SUBSTR(input_data,use_number,1) ;
    84> ELSE
    85> chk_number2 := 0;
    86> END IF;
    87> last_two := SUBSTR(input_data,use_number,2) ;
    88> use_number := array_index - start_position ;
    89> start_position := start_position + 1;
    90> IF ( use_number != ' ') THEN
    91> chk_number3 := SUBSTR(input_data,use_number,1) ;
    92> ELSE
    93> chk_number3 := 0;
    94> END IF;
    95> IF ( higher_position > 1 AND
    96> chk_number1 = 0 AND
    97> chk_number2 = 0 AND
    98> chk_number3 = 0 ) THEN
    99> disp_position := disp_position - 1;
   100> disp_var (disp_position) := ' ';
   101> END IF;
   102> IF ( chk_number1 <> 0 AND
   103> chk_number1 <> ' ' AND
   104> chk_number2 <> '1' ) THEN
   105> disp_position := disp_position + 1;
   106> disp_var (disp_position) := one_thru_nine (chk_number1);
   107> END IF;
   108> IF ( chk_number2 <> 0 AND
   109> chk_number2 <> ' ' ) THEN
   110> IF ( chk_number1 <> '0' AND
   111> chk_number1 <> ' ' AND
   112> chk_number2 = '1' ) THEN
   113> disp_position := disp_position + 1;
   114> disp_var (disp_position) := one_thru_nine (last_two);
   115> ELSE
   116> disp_position := disp_position + 1;
   117> disp_var (disp_position) := ten_thru_ninety (chk_number2);
   118> END IF;
   119> END IF;
   120> IF ( chk_number3 <> 0 AND
   121> chk_number3 <> ' ' ) THEN
   122> disp_position := disp_position + 1;
   123> disp_var (disp_position) := higher_group (1) ;
   124> disp_position := disp_position + 1;
   125> disp_var (disp_position) := one_thru_nine (chk_number3) ;
   126> END IF;
   127> higher_position := higher_position + 1 ;
   128> END LOOP;
   129> higher_position := disp_position ;
   130> WHILE ( higher_position > 0 )
   131> LOOP
   132> total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
   133> higher_position := higher_position - 1;
   134> END LOOP;
   135> RETURN total_var;
   136> END;
   137>
   138>
Statement processed.
SQLWKS> select newmsr(10000) from dual
     2>
NEWMSR(10000)                                                                  
--------------------------------------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 75
ORA-06512: at line 1
0
 
LVL 8

Accepted Solution

by:
Pierrick LOUBIER earned 250 total points
ID: 12355609
Declare all variables in VARCHAR2 instead of CHAR and it works :

Connected to:
Oracle8i Release 8.1.7.4.1 - Production
JServer Release 8.1.7.4.1 - Production

SQL> select nsc_num_to_words(999) from dual;

NSC_NUM_TO_WORDS(999)
--------------------------------------------------------------------------------
 NINE HUNDRED NINETY NINE

SQL> select nsc_num_to_words(1000) from dual;

NSC_NUM_TO_WORDS(1000)
--------------------------------------------------------------------------------
 ONE THOUSAND


Here is the modified function :

CREATE OR REPLACE FUNCTION nsc_num_to_words (
temp_var IN VARCHAR2 )
RETURN VARCHAR2
IS
input_data VARCHAR2(30);
alpha_var VARCHAR2(30);
total_var VARCHAR2(2000);
chk_number1 NUMBER(1);
chk_number2 NUMBER(1);
chk_number3 NUMBER(1);
last_two NUMBER(2);
start_position NUMBER;
array_index NUMBER;
higher_position NUMBER;
disp_position NUMBER;
use_number NUMBER;
TYPE data_text IS TABLE OF VARCHAR2(40)
INDEX BY binary_integer;
one_thru_nine data_text;
ten_thru_ninety data_text;
higher_group data_text;
disp_var data_text;
BEGIN
one_thru_nine (001) := ' ONE';
one_thru_nine (002) := ' TWO';
one_thru_nine (003) := ' THREE';
one_thru_nine (004) := ' FOUR';
one_thru_nine (005) := ' FIVE';
one_thru_nine (006) := ' SIX';
one_thru_nine (007) := ' SEVEN';
one_thru_nine (008) := ' EIGHT';
one_thru_nine (009) := ' NINE';
one_thru_nine (010) := ' TEN';
one_thru_nine (011) := ' ELEVEN';
one_thru_nine (012) := ' TWELVE';
one_thru_nine (013) := ' THIRTEEN';
one_thru_nine (014) := ' FOURTEEN';
one_thru_nine (015) := ' FIFTEEN';
one_thru_nine (016) := ' SIXTEEN';
one_thru_nine (017) := ' SEVENTEEN';
one_thru_nine (018) := ' EIGHTEEN';
one_thru_nine (019) := ' NINETEEN';
ten_thru_ninety (001) := ' TEN';
ten_thru_ninety (002) := ' TWENTY';
ten_thru_ninety (003) := ' THIRTY';
ten_thru_ninety (004) := ' FORTY';
ten_thru_ninety (005) := ' FIFTY';
ten_thru_ninety (006) := ' SIXTY';
ten_thru_ninety (007) := ' SEVENTY';
ten_thru_ninety (008) := ' EIGHTY';
ten_thru_ninety (009) := ' NINETY';
higher_group (001) := ' HUNDRED';
higher_group (002) := ' THOUSAND';
higher_group (003) := ' MILLION';
higher_group (004) := ' BILLION';
higher_group (005) := ' TRILLION';
higher_group (006) := ' QUADRILLION';
higher_group (007) := ' QUINTILLION';
higher_group (008) := ' SEXTILLION';
higher_group (009) := ' SEPTILLION';
higher_group (010) := ' OCTILLION';

input_data := temp_var;

SELECT NVL(LENGTH(temp_var),0)
INTO array_index
FROM DUAL;

higher_position := 1;
disp_position := 0;
start_position := 0;

WHILE ( array_index > start_position )
LOOP
IF ( higher_position > 1 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (higher_position);
END IF;

use_number := array_index - start_position ;


start_position := start_position + 1;
IF ( use_number <> 0) THEN
chk_number1 := SUBSTR(input_data,use_number,1) ;
ELSE
chk_number1 := 0;
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number <> 0) THEN
chk_number2 := SUBSTR(input_data,use_number,1) ;
ELSE
chk_number2 := 0;
END IF;
last_two := SUBSTR(input_data,use_number,2) ;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number <> 0) THEN
chk_number3 := SUBSTR(input_data,use_number,1) ;
ELSE
chk_number3 := 0;
END IF;
IF ( higher_position > 1 AND
chk_number1 = 0 AND
chk_number2 = 0 AND
chk_number3 = 0 ) THEN
disp_position := disp_position - 1;
disp_var (disp_position) := ' ';
END IF;
IF ( chk_number1 <> 0 AND
chk_number1 <> 0 AND
chk_number2 <> 1 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number1);
END IF;
IF ( chk_number2 <> 0 AND
chk_number2 <> 0 ) THEN
IF ( chk_number1 <> 0 AND
chk_number1 <> 0 AND
chk_number2 = 1 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (last_two);
ELSE
disp_position := disp_position + 1;
disp_var (disp_position) := ten_thru_ninety (chk_number2);
END IF;
END IF;
IF ( chk_number3 <> 0 AND
chk_number3 <> 0 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (1) ;
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number3) ;
END IF;
higher_position := higher_position + 1 ;
END LOOP;
higher_position := disp_position ;
WHILE ( higher_position > 0 )
LOOP
total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
higher_position := higher_position - 1;
END LOOP;
RETURN total_var;


END;
--End
/
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 12355634
Fix line 4 and 15 :

4> alpha_var VARCHAR2(30);
15> TYPE data_text IS TABLE OF VARCHAR2(40)

Bad idea to mix string types...
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.

 

Author Comment

by:sitaparijat
ID: 12356050
Hi Pal,

It is not much of an excuse,  but it is not my code..

I tried your correction but the error changes it fails for all the numbers. Pl take alook.


Th results:
SQLWKS> select newmsr(10000) from dual
     2>
NEWMSR(10000)                                                                  
--------------------------------------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(1000) from dual
     2>
NEWMSR(1000)                                                                    
--------------------------------------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(999) from dual
     2>
NEWMSR(999)                                                                    
--------------------------------------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(1) from dual
     2>
NEWMSR(1)                                                                      
--------------------------------------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1

The code like I compiled.

Pl see below:
CREATE OR REPLACE FUNCTION newmsr (temp_var IN VARCHAR2 )
RETURN VARCHAR2
IS input_data VARCHAR2(30);
alpha_var VARCHAR2(30);

total_var VARCHAR2(2000);
chk_number1 NUMBER(1);
chk_number2 NUMBER(1);
chk_number3 NUMBER(1);
last_two NUMBER(2);
start_position NUMBER;
array_index NUMBER;
higher_position NUMBER;
disp_position NUMBER;
use_number NUMBER;
TYPE data_text IS TABLE OF VARCHAR2(40)
INDEX BY binary_integer;
one_thru_nine data_text;
ten_thru_ninety data_text;
higher_group data_text;
disp_var data_text;
BEGIN
one_thru_nine (001) := ' ONE';
one_thru_nine (002) := ' TWO';
one_thru_nine (003) := ' THREE';
one_thru_nine (004) := ' FOUR';
one_thru_nine (005) := ' FIVE';
one_thru_nine (006) := ' SIX';
one_thru_nine (007) := ' SEVEN';
one_thru_nine (008) := ' EIGHT';
one_thru_nine (009) := ' NINE';
one_thru_nine (010) := ' TEN';
one_thru_nine (011) := ' ELEVEN';
one_thru_nine (012) := ' TWELVE';
one_thru_nine (013) := ' THIRTEEN';
one_thru_nine (014) := ' FOURTEEN';
one_thru_nine (015) := ' FIFTEEN';
one_thru_nine (016) := ' SIXTEEN';
one_thru_nine (017) := ' SEVENTEEN';
one_thru_nine (018) := ' EIGHTEEN';
one_thru_nine (019) := ' NINETEEN';
ten_thru_ninety (001) := ' TEN';
ten_thru_ninety (002) := ' TWENTY';
ten_thru_ninety (003) := ' THIRTY';
ten_thru_ninety (004) := ' FORTY';
ten_thru_ninety (005) := ' FIFTY';
ten_thru_ninety (006) := ' SIXTY';
ten_thru_ninety (007) := ' SEVENTY';
ten_thru_ninety (008) := ' EIGHTY';
ten_thru_ninety (009) := ' NINETY';
higher_group (001) := ' HUNDRED';
higher_group (002) := ' THOUSAND';
higher_group (003) := ' MILLION';
higher_group (004) := ' BILLION';
higher_group (005) := ' TRILLION';
higher_group (006) := ' QUADRILLION';
higher_group (007) := ' QUINTILLION';
higher_group (008) := ' SEXTILLION';
higher_group (009) := ' SEPTILLION';
higher_group (010) := ' OCTILLION';
input_data := temp_var;
SELECT NVL(LENGTH(temp_var),0)
INTO array_index
FROM DUAL;
higher_position := 1;
disp_position := 0;
start_position := 0;
WHILE ( array_index > start_position )
LOOP
IF ( higher_position > 1 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (higher_position);
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number1 := SUBSTR(input_data,use_number,1) ;
ELSE
chk_number1 := 0;
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number2 := SUBSTR(input_data,use_number,1) ;
ELSE
chk_number2 := 0;
END IF;
last_two := SUBSTR(input_data,use_number,2) ;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number3 := SUBSTR(input_data,use_number,1) ;
ELSE
chk_number3 := 0;
END IF;
IF ( higher_position > 1 AND
chk_number1 = 0 AND
chk_number2 = 0 AND
chk_number3 = 0 ) THEN
disp_position := disp_position - 1;
disp_var (disp_position) := ' ';
END IF;
IF ( chk_number1 <> 0 AND
chk_number1 <> ' ' AND
chk_number2 <> '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number1);
END IF;
IF ( chk_number2 <> 0 AND
chk_number2 <> ' ' ) THEN
IF ( chk_number1 <> '0' AND
chk_number1 <> ' ' AND
chk_number2 = '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (last_two);
ELSE
disp_position := disp_position + 1;
disp_var (disp_position) := ten_thru_ninety (chk_number2);
END IF;
END IF;
IF ( chk_number3 <> 0 AND
chk_number3 <> ' ' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (1) ;
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number3) ;
END IF;
higher_position := higher_position + 1 ;
END LOOP;
higher_position := disp_position ;
WHILE ( higher_position > 0 )
LOOP
total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
higher_position := higher_position - 1;
END LOOP;
RETURN total_var;
END;

Statement processed.
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 12356113
Copy / paste my version. It works.
You used an old version (use_number != ' ' instead of use_number<>0).
0
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 50 total points
ID: 12356750
Hi Srini,

Why don't you use an existing facility provided by Oracle, instead of writing a function of your own.

Try the following :

SQL> select TO_CHAR(TO_DATE(100, 'J'), 'JSP') FROM DUAL;

TO_CHAR(TO_
-----------
ONE HUNDRED

SQL> select TO_CHAR(TO_DATE(1000, 'J'), 'JSP') FROM DUAL;

TO_CHAR(TO_D
------------
ONE THOUSAND

SQL> select TO_CHAR(TO_DATE(13450, 'J'), 'JSP') FROM DUAL;

TO_CHAR(TO_DATE(13450,'J'),'JSP')
------------------------------------
THIRTEEN THOUSAND FOUR HUNDRED FIFTY

Hope this helps !
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12356757
You may note that there is a limitation with it though :

SQL> select TO_CHAR(TO_DATE(9999999, 'J'), 'JSP') FROM DUAL;
select TO_CHAR(TO_DATE(9999999, 'J'), 'JSP') FROM DUAL
                       *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484


SQL> select TO_CHAR(TO_DATE(5373484, 'J'), 'JSP') FROM DUAL;

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR

0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 50 total points
ID: 12356769
How about:

tkyte@TKYTE816> create or replace
  2  function spell_number( p_number in number )
  3  return varchar2
  4  as
  5      type myArray is table of varchar2(255);
  6      l_str    myArray := myArray( '',
  7                             ' thousand ', ' million ',
  8                             ' billion ', ' trillion ',
  9                             ' quadrillion ', ' quintillion ',
 10                             ' sextillion ', ' septillion ',
 11                             ' octillion ', ' nonillion ',
 12                             ' decillion ', ' undecillion ',
 13                             ' duodecillion ' );
 14
 15      l_num   varchar2(50) default trunc( p_number );
 16      l_return varchar2(4000);
 17  begin
 18      for i in 1 .. l_str.count
 19      loop
 20          exit when l_num is null;
 21
 22          if ( substr(l_num, length(l_num)-2, 3) <> 0 )
 23          then
 24             l_return := to_char(
 25                             to_date(
 26                              substr(l_num, length(l_num)-2, 3),
 27                                'J' ),
 28                         'Jsp' ) || l_str(i) || l_return;
 29          end if;
 30          l_num := substr( l_num, 1, length(l_num)-3 );
 31      end loop;
 32
 33      return l_return;
 34  end;
 35  /

Function created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> select
  2  spell_number( 12345678901234567890123456789012345678 )
  3  from dual;

SPELL_NUMBER(1234567890123456789012345678901234567
--------------------------------------------------
Twelve undecillion Three Hundred Forty-Five decill
ion Six Hundred Seventy-Eight nonillion Nine Hundr
ed One octillion Two Hundred Thirty-Four septillio
n Five Hundred Sixty-Seven sextillion Eight Hundre
d Ninety quintillion One Hundred Twenty-Three quad
rillion Four Hundred Fifty-Six trillion Seven Hund
red Eighty-Nine billion Twelve million Three Hundr
ed Forty-Five thousand Six Hundred Seventy-Eight


ops$tkyte@DEV816>
ops$tkyte@DEV816> declare
  2      l_num number;
  3      l_str varchar2(255);
  4      l_spelled varchar2(4000);
  5  begin
  6      for i in 1 .. 10
  7      loop
  8          l_num := random.rand() ||
  9                   random.rand() ||
 10                   random.rand();
 11          l_str :=
 12              to_char( l_num, '999,999,999,999,999,999' ) ;
 13          l_spelled := spell_number( l_num );
 14  
 15          dbms_output.put_line
 16          ( l_str ||  ' -- ' || l_spelled );
 17      end loop;
 18  end;
 19  /
312,051,345,415,411 -- Three Hundred Twelve
trillion Fifty-One billion Three Hundred
Forty-Five million Four Hundred Fifteen thousand
Four Hundred Eleven
25,131,160,032,468 -- Twenty-Five trillion One
Hundred Thirty-One billion One Hundred Sixty
million Thirty-Two thousand Four Hundred
Sixty-Eight
210,202,025,424,018 -- Two Hundred Ten trillion
Two Hundred Two billion Twenty-Five million Four
Hundred Twenty-Four thousand Eighteen
28,221,169,384,374 -- Twenty-Eight trillion Two
Hundred Twenty-One billion One Hundred Sixty-Nine
million Three Hundred Eighty-Four thousand Three
Hundred Seventy-Four
148,881,759,131,997 -- One Hundred Forty-Eight
trillion Eight Hundred Eighty-One billion Seven
Hundred Fifty-Nine million One Hundred Thirty-One
thousand Nine Hundred Ninety-Seven
178,761,729,314,960 -- One Hundred Seventy-Eight
trillion Seven Hundred Sixty-One billion Seven
Hundred Twenty-Nine million Three Hundred Fourteen
thousand Nine Hundred Sixty
7,789,111,094,989 -- Seven trillion Seven Hundred
Eighty-Nine billion One Hundred Eleven million
Ninety-Four thousand Nine Hundred Eighty-Nine
137,652,698,329,777 -- One Hundred Thirty-Seven
trillion Six Hundred Fifty-Two billion Six Hundred
Ninety-Eight million Three Hundred Twenty-Nine
thousand Seven Hundred Seventy-Seven
155,311,009,417,668 -- One Hundred Fifty-Five
trillion Three Hundred Eleven billion Nine million
Four Hundred Seventeen thousand Six Hundred
Sixty-Eight
139,292,540,822,249 -- One Hundred Thirty-Nine
trillion Two Hundred Ninety-Two billion Five
Hundred Forty million Eight Hundred Twenty-Two
thousand Two Hundred Forty-Nine

PL/SQL procedure successfully completed.

(from http://asktom.oracle.com/pls/ask/f?p=4950:8:13473455047662199032::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1407603857650, )
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12356867
A post from Tom Kyte has this http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1407603857650

Spell method will not work for the non-existant days of
the non-existant year 0, that is for the numbers between 1721058 and 1721423

select to_char(to_date( '1721423' , 'J'),'JSP') from dual;
                         *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

To spell numbers between 0 and 86399, you can use seconds of the day :

SQL> select to_char(to_date(0, 'SSSSS'),'Ssssssp') from dual;

TO_C
----
Zero
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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

708 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