Link to home
Start Free TrialLog in
Avatar of sitaparijat
sitaparijat

asked on

Oracle function works in 8.0.5 but fails in Oracle 8.1.7

This should be some issue with the version of the Oracle with the later versions being more unforgiving with the issues in syntax. I have the function which converts numbers to words the following should be self explanatory:  

Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.5.0.0 - Production
SQLWKS> select nsc_num_to_words(100) from dual
     2>
NSC_NUM_TO_WORDS(100)                                                          
--------------------------------------------------------------------------------
 ONE HUNDRED                                                                    
1 row selected.

But the same thing when I execute in the other environment I get an error.
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
SQLWKS> select nsc_num_to_words(100) from dual
    ....
 ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NSC_NUM_TO_WORDS", line 76
ORA-06512: at line 1


The text of the function is provided for your reference:
--Start
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;
--End
ASKER CERTIFIED SOLUTION
Avatar of catchmeifuwant
catchmeifuwant

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sitaparijat
sitaparijat

ASKER

Thanks catchmeifuwant..

I appreciate the quick response..
I know it would be a piece of cake for the experts..

Regards, Srini
Pleasure to be of Help ...Good Luck