GeekInterview.com
Series: Subject: Topic:
Question: 4 of 165

How to get number in words in Oracle 10g Reports

How to get number in words
we are using Oracle 10g software
and in reports, i am trying to give as function code and return value as placeholder, but i am getting an error as foll.
REP:1401 'cf_1formula': Fatal PL/SQL error occurred.

Thanks in advance
Krishnan R
Asked by: krishvim99 | Member Since Jun-2011 | Asked on: Jun 5th, 2011

View all questions by krishvim99

Showing Answers 1 - 2 of 2 Answers
Shital Mistry

Answered On : Aug 11th, 2012

Question is not clear to me. Specify some details what exactly you want to do?

  
Login to rate this answer.
asifinet

Answered On : Feb 15th, 2013

View all answers by asifinet

Code
  1. SET Serveroutput ON 1000000
  2. Declare
  3.   V_Input NUMBER := &TESTED;
  4.   FUNCTION Numbertowords(P_Number IN Out Number) RETURN Varchar2 IS
  5.     V_Words Varchar2(32767) :=  ;
  6.     V_Temp Number;    
  7.     Type Unitsmap IS TABLE Of Varchar2(250) INDEX BY Binary_Integer;
  8.     Type Tensmap IS TABLE Of Varchar2(250) INDEX BY Binary_Integer;    
  9.     V_Unitsmap Unitsmap ;
  10.     V_Tensmap Tensmap   ;
  11.   Begin
  12.     V_Unitsmap(0) := Zero;
  13.     V_Unitsmap(1) := One;
  14.     V_Unitsmap(2) := Two;
  15.     V_Unitsmap(3) := Three;
  16.     V_Unitsmap(4) := Four;
  17.     V_Unitsmap(5) := Five;
  18.     V_Unitsmap(6) := Six;
  19.     V_Unitsmap(7) := Seven;
  20.     V_Unitsmap(8) := Eight;
  21.     V_Unitsmap(9) := Nine;
  22.     V_Unitsmap(10) := Ten;
  23.     V_Unitsmap(11) := Eleven;
  24.     V_Unitsmap(12) := Twelve;
  25.     V_Unitsmap(13) := Thirteen;
  26.     V_Unitsmap(14) := Fourteen;
  27.     V_Unitsmap(15) := Fifteen;
  28.     V_Unitsmap(16) := Sixteen;
  29.     V_Unitsmap(17) := Seventeen;
  30.     V_Unitsmap(18) := Eighteen;
  31.     V_Unitsmap(19) := Nineteen;
  32.     V_Tensmap(2)  := Twenty;
  33.     V_Tensmap(3)  := Thirty;
  34.     V_Tensmap(4)  := Forty;
  35.     V_Tensmap(5)  := Fifty;
  36.     V_Tensmap(6)  := Sixty;
  37.     V_Tensmap(7)  := Seventy;
  38.     V_Tensmap(8)  := Eighty;
  39.     V_Tensmap(9)  := Ninety;
  40.     IF (P_Number = 0) Then
  41.       RETURN Zero;
  42.     End IF;
  43.     IF (P_Number < 0) Then
  44.       V_Temp := Abs(P_Number);      
  45.       RETURN Minus  || Numbertowords(V_Temp);
  46.     End IF;
  47.     V_Temp := TRUNC(P_Number / 1000000);
  48.     IF ( V_Temp > 0) Then
  49.       V_Words := V_WORDS || Numbertowords(V_Temp) ||  Million;
  50.       P_Number := Mod(P_Number,1000000);
  51.     End IF;
  52.     V_Temp := TRUNC(P_Number / 1000);
  53.     IF ( V_Temp > 0)Then
  54.       V_Words := V_Words || Numbertowords(V_Temp) ||  Thousand;
  55.       P_Number := Mod(P_Number,1000);
  56.     End IF;
  57.     V_Temp := Trunc(P_Number / 100);
  58.     IF ( V_Temp > 0) Then
  59.       V_Words := V_Words ||  Numbertowords(V_Temp) ||  Hundred ;
  60.       P_Number := Mod(P_Number,100);
  61.     End IF;
  62.     V_Temp := P_Number;
  63.     IF (V_Temp > 0) Then
  64.       IF (V_Words !=  ) Then
  65.         V_Words := V_Words || AND ;
  66.        End IF;
  67.       IF (V_Temp < 20) Then
  68.         V_Words := V_Words || V_Unitsmap(V_Temp);
  69.         RETURN V_Words;
  70.       Else
  71.        V_Temp := TRUNC(P_Number/ 10);
  72.         V_Words := V_Words || V_Tensmap(V_Temp);
  73.         IF ((Mod(P_Number ,10)) > 0) Then
  74.           V_Words :=V_Words|| - ||V_Unitsmap(Mod(P_Number,10));
  75.         End IF;
  76.       End IF;        
  77.     End IF;
  78.     RETURN V_Words;
  79.   End;
  80. Begin    
  81.   Dbms_Output.Put_Line(&TESTED|| =||Numbertowords(V_Input));
  82. End;

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.