Change! When Oracle Version Change

ERROR at line 1:
ORA-01722: invalid number

My friend told me, He found error on script/program after he used with 10gR2, But no problem on 10gR1.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0
PL/SQL Release 10.1.0.3.0
CORE 10.1.0.3.0
TNS for Linux: Version 10.1.0.3.0
NLSRTL Version 10.1.0.3.0

SQL> SELECT a.Current_proc, b.Max_proc, ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100, 0) Alert_proc FROM (SELECT COUNT(*)Current_proc FROM sys.v_$process) a,(SELECT value Max_proc FROM sys.v_$parameter WHERE name = 'processes') b WHERE ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100,0) < 15;

no rows selected

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
PL/SQL Release 10.2.0.3.0
CORE 10.2.0.3.0
TNS for Linux: Version 10.2.0.3.0
NLSRTL Version 10.2.0.3.0

SQL> SELECT a.Current_proc, b.Max_proc, ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100, 0) Alert_proc FROM (SELECT COUNT(*)Current_proc FROM sys.v_$process) a,(SELECT value Max_proc FROM sys.v_$parameter WHERE name = 'processes') b WHERE ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100,0) < 15;

SELECT a.Current_proc, b.Max_proc, ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100, 0) Alert_proc FROM (SELECT COUNT(*)Current_proc FROM sys.v_$process) a,(SELECT value Max_proc FROM sys.v_$parameter WHERE name = 'processes') b WHERE ROUND(((b.Max_proc-a.Current_proc)/b.Max_proc)*100,0) < 15
*
ERROR at line 1:
ORA-01722: invalid number

Query is multiplying a column with varchar2 data type with a number column. This may or may not succeed depending on the query plan. Oracle cannot guarantee plans to remain same across database versions. With new features in a new release query plans can always change.

On 10gR1:
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 358 | 72 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 358 | 72 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 345 | 48 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSPPI | 1 | 68 | 24 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSPPCV (ind:2) | 1 | 277 | 24 (0)| 00:00:01 |
|* 5 | VIEW | | 1 | 13 | 24 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 26 | | |
|* 7 | FIXED TABLE FULL | X$KSUPR | 1 | 26 | 24 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("KSPPINM"='processes' AND "X"."INST_ID"=:B1)
4 - filter("X"."INDX"="Y"."INDX" AND (TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%'
OR "KSPPSTDF"='FALSE'))
5 - filter(ROUND((TO_NUMBER("KSPPSTVL")-"A"."CURRENT_PROC")/TO_NUMBER("KSPPSTVL")*1
00,0)<15)>0)

On 10gR2:
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 371 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 371 | 1 (100)| 00:00:01 |
| 2 | NESTED LOOPS | | 5 | 1515 | 0 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 13 | 0 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 26 | | |
|* 5 | FIXED TABLE FULL| X$KSUPR | 1 | 26 | 0 (0)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KSPPCV | 5 | 1450 | 0 (0)| 00:00:01 |
|* 7 | FIXED TABLE FULL | X$KSPPI | 1 | 68 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
"KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
5 - filter("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)
6 - filter(ROUND((TO_NUMBER("KSPPSTVL")-"A"."CURRENT_PROC")/TO_NUMBER(
"KSPPSTVL")*100,0)<15)
7 - filter("KSPPINM"='processes' AND
"X"."INST_ID"=USERENV('INSTANCE') AND TRANSLATE("KSPPINM",'_','#') NOT
LIKE '##%')

That show, SQL PLAN was changed.
we might change SQL or something to make script/program work and (show old result)

SQL>select CURRENT_UTILIZATION Current_proc, INITIAL_ALLOCATION Max_proc, ROUND(((INITIAL_ALLOCATION-CURRENT_UTILIZATION)/INITIAL_ALLOCATION)*100, 0) Alert_proc from v$resource_limit where RESOURCE_NAME='processes' and ROUND(((INITIAL_ALLOCATION-CURRENT_UTILIZATION)/INITIAL_ALLOCATION)*100, 0) < 15;

no rows selected

That worked... Perhaps we can use other Ideas, But My point ... When we change Oracle version, we might necessary to change SQL Statement or something.
:(