Re: PL/SQL Problem
Date: 1996/05/27
Message-ID: <4ob9c0$230k_at_mule1.mindspring.com>#1/1
(gwen jenkins) wrote:
>I am using Oracle 6 in Unisys 6000 unix machine. Now, I am writing the application that
>need to check the char field which is numeric or character.
>char field is numeric ==> contains 0 - 9 and space
>char field is character ==> contains a-z, A-Z and space
>Would you tell me how to code it in PL/SQL?
Use to_number function. I am giving an example by creating a table.
SQL> describe test
Name Null? Type ------------------------------- -------- ---- NUM_CHAR VARCHAR2(10)
SQL> select * from test;
NUM_CHAR
10
ab
1a
SQL> select to_number(num_char) from test;
ERROR:
ORA-01722: invalid number
no rows selected
SQL> select to_number(num_char) from test 2 where num_char = '10';
TO_NUMBER(NUM_CHAR)
10
When to_number function tries to convert 'ab' or '1a' string to number, it will raise an exception 0RA-01722. Using Pragma Exception_Init feature in PL/SQL, trap this exception and handle accordingly. If you do not want to terminate your program just because of this exception, handle this exception as shown below:
DECLARE /* Your main block */
...
BEGIN /* Your main block */
...
declare
wrong_conversion exception;
pragma exception_init(wrong_conversion, -1722)
begin
to_number(test.num_char);
...
exception
when wrong_conversion then
<Handle in the way you want>
end;
EXCEPTION /* Your main block */
...
END; /* Your main block */
In this way, your program will not be terminated because of this small problem and you can take care of the rest of the logic.
Hope this helps.
Moorthy
(moorthy_at_mindspring.com)
Received on Mon May 27 1996 - 00:00:00 CEST