Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: is number
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';
rem qry_ex_table.sql
column results format a10 trunc
select id
, value , is_number(id) results
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
![]() |
![]() |