Prev: Oracle 10g on Windows connection problem - ORA-12505
Next: ORA 1801 - Dateformat too long for internal buffer
From: DA Morgan on 27 Mar 2006 14:32
mr. ambastha wrote:
> i just have a doubt. till date i was writing a pl sql function for
> checking whether the entered number is numeric or not. can anyone tell
> whether there is any built in function to check this? Earlier i was
> using isdigit() and isnumeric() in other languages. is this kind of
> functions available here?
> thanks in advance,
> regards,
> -ambastha

You must write your own function.
--
Daniel A. Morgan
http://www.psoug.org
damorgan(a)x.washington.edu
(replace x with u to respond)
From: Chris ( Val ) on 27 Mar 2006 18:33

Malcolm Dew-Jones wrote:
> Arto Viitanen (arto.viitanen(a)csc.fi) wrote:
> : mr. ambastha wrote:

[ snip ]

> Regular expressions, very powerful, everyone should own one, but they are
> also tricky, and easy to get wrong.

[ snip ]

I agree.

> exception
> -- This should check just the expected error
> -- but I haven't bothered to look it up.
> when others then
> return NULL;

[ snip ]

What you're looking for is: "INVALID_NUMBER"

Cheers,
Chris Val

From: Ubiquitous on 24 Apr 2006 13:49
chrisval(a)gmail.com wrote:
>Malcolm Dew-Jones wrote:

>> -- untested
>> function is_a_number( s varchar2)
>> return number
>> is
>> begin
>> return to_number(s);
>> exception
>> -- This should check just the expected error
>> -- but I haven't bothered to look it up.
>> when others then
>> return NULL;
> end is_a_number;
>
>What you're looking for is: "INVALID_NUMBER"

I thinkthis throws a VALUE_ERROR exception. What's the difference?

Not to be pedantic, but shouldn't this function return a BOOLEAN
instead of a NUMBER, so you have something like:

FUNCTION is_numeric(s VARCHAR2)
RETURN BOOLEAN
IS
return_value BOOLEAN := FALSE ;
v_numeric NUMBER := 0;
BEGIN
v_numeric := TO_NUMBER(s);
return_value := TRUE ;
RETURN return_value;
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN return_value;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Unexpected EXCEPTION!');
RETURN return_value;
END is_numeric;

From: Malcolm Dew-Jones on 24 Apr 2006 16:03
Ubiquitous (weberm(a)polaris.net) wrote:
: chrisval(a)gmail.com wrote:
: >Malcolm Dew-Jones wrote:

: >> -- untested
: >> function is_a_number( s varchar2)
: >> return number
: >> is
: >> begin
: >> return to_number(s);
: >> exception
: >> -- This should check just the expected error
: >> -- but I haven't bothered to look it up.
: >> when others then
: >> return NULL;
: > end is_a_number;
: >
: >What you're looking for is: "INVALID_NUMBER"

: I thinkthis throws a VALUE_ERROR exception. What's the difference?

: Not to be pedantic, but shouldn't this function return a BOOLEAN
: instead of a NUMBER,

Yes, based on the name of the function, but I assume if you want the
number then you want the number, so I return the number.

The solution would be to change the name, not the function, perhaps
"validated_number", or such like, but good names are hard, so I didn't try
to find one.

First  |  Prev  | 
Pages: 1 2
Prev: Oracle 10g on Windows connection problem - ORA-12505
Next: ORA 1801 - Dateformat too long for internal buffer