Re: is number

From: Eric Givler <egivler_at_flash.net>
Date: Thu, 14 Sep 2000 13:48:30 GMT
Message-ID: <OG4w5.6708$oc3.415894@news.flash.net>

How about?

create or replace function is_number( in_value in varchar2 ) return varchar2 is

   retval varchar2(5);
   local_number number;
begin

    begin

       --select to_number( in_value )
       --  into local_number
       --  from dual;
       local_number := to_number( in_value );
       retval := 'TRUE';
       exception
       when invalid_number then /* raised by ora-1722 if we use a sql
statement */
           retval := 'FALSE';
       when value_Error then /* raised by pl/sql errors */
           retval := 'FALSE';

    end;
    return (Retval);
end is_number;
/

rem qry_ex_table.sql
column results format a10 trunc
select id

     , value
     , is_number(id) results

from ex_Table
/

select max(to_number(id)) + 1
from ex_table
where is_number( id ) = 'TRUE'
/

<eastking_at_my-deja.com> wrote in message news:8ppq47$nrm$1_at_nnrp1.deja.com...
> hello , everyone here
>
> I have a table like following:
>
> Ex_table
>
> id value
> 01 aaa
> hh AAA
> 02 bbb
> ab ccc
>
>
> I'd like to insert a row whose id is max(to_number(id))+1 into this
> table . In this case , the id should be 3. But to_number('ab') will
> cause a error.How can I achieve it by only a SQL command.
>
> Thanks in advance.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Sep 14 2000 - 08:48:30 CDT