Using WHEN OTHERS has the undesired effect of masking other errors, so a better exception handling condition, that is, one which specifically catches "invalid number" conditions, would be ideal. Fortunately, there happens to be a built-in predefined handler named INVALID_NUMBER. If all you are familiar with is the OTHERS condition, you owe it to yourself to see what Oracle (specifically, PL/SQL) has to offer in its list of Predefined PL/SQL Exceptions. 数据挖掘研究院
So far, the results have been simply output to the user/terminal. Instead of using a select statement (by itself), the result set can be steered into one of two tables via an "insert into other_table select …" statement.
Approach 3: Using a Date Function
An infrequently used date format option is Julian date. A simple example of using the Julian date format to convert numbers to words is shown below. 数据挖掘研究院
SQL> select to_char(to_date(009341,′J′), ′JSP′) "WORDS" from dual;
WORDS
-------------------------------------
NINE THOUSAND THREE HUNDRED FORTY-ONE
数据挖掘研究院
What happens when you try to convert A12345 into words using this approach? I will use the same type of PL/SQL block as before, but toss in an extra exception handling condition. 数据挖掘研究院
SQL> declare
2 v_result varchar2(300);
3 cursor c is
4 select acctnum
5 from account_test;
6 begin
7 for r in c loop
8 begin
9 select to_char(to_date(r.acctnum ,′J′), ′JSP′) into v_result from dual;
10 dbms_output.put_line(r.acctnum||′ is a number′);
11 exception
12 when value_error then
13 dbms_output.put_line(r.acctnum||′ is a NOT number′);
14 when others then
15 dbms_output.put_line(sqlerrm);
16 end;
17 end loop;
18 end;
19 /
ORA-01858: a non-numeric character was found where a numeric was expected
009341 is a number
783652 is a number
PL/SQL procedure successfully completed.
