I am working on Oracle 8.0.5 version

I have an fe_asset table.

There is an asset_description column which contains both the description of asset and the quantity.
sometimes the description is as under:

My Current code takes care of this:
PART A
2 tables -- 2 refers the quantity
58 computer books -- 58 refers the computer books.
Insurance books -- Here quantity is implied as 1.

My current code does not take care of these following things:
PART B
Fans (8) -- Here 8 refers quanity
Electroux754 -- Here quanity is 1 , 754 is the brand name
tables and chairs x 48 -- Here 48 refers to quantity.

This is just a sample of the quanity in the asset description column. My work is to pick the quantity and use the quantity to divide the purcahse cost. ie, purchase_cost/quantity. If the per unit cost of an aset is less than 1000 then I have to consider all those assets. This table contains more than 1million records.

I have written a code. Can I use some other code or modify mode my code to include PART B too?

Also, is there any way to find whether it is a char or a number?

***************************
CODE

Declare

Cursor c1 is
select asset_id, asset_description, purchase_cost
from fe.fe_asset;

v_char number(10);
v_char1 number(10);


Begin
delete from padfe4;
for x in c1 loop

Begin


select nvl(to_number(substr(ASSET_DESCRIPTION,1,INSTR (ASSET_DESCRIPTION,' ',1, 1)-1)),1)
into v_char
from fe.fe_asset where asset_id = x.asset_id;


If substr(x.ASSET_DESCRIPTION,1,INSTR (x.ASSET_DESCRIPTION,' ',1, 1)-1) = '0' then

v_char := 1;

If ((x.purchase_cost/v_char) < 1000) or (x.purchase_cost < 1000) then

insert into padfe4(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);

End if;

elsif ((x.purchase_cost/v_char) < 1000) or (x.purchase_cost < 1000) then

insert into padfe4(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);

End if;



commit;

Exception

when INVALID_NUMBER then



v_char := 1;

If (x.purchase_cost/v_char) < 1000 then
insert into padfe4(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,1);
end if;
commit;

WHEN value_error THEN
dbms_output.put_line('This is a value_error in inner exception'||x.asset_id);
dbms_output.put_line(sqlcode||' '||sqlerrm);

when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);


End;

End loop;
commit;

Exception
when INVALID_NUMBER then
dbms_output.put_line('This is a char in outer loop');

WHEN value_error THEN
dbms_output.put_line('This is a VALUE_ERROR IN outer loop');

when others then
dbms_output.put_line('This is the last alter in outer loop');

End;

**************************************
2nd piece of code:

I also tried using ascii and working out this way but somewhere I am getting stuck to find a solution for PART B

Declare

Cursor c1 is
select asset_id, asset_description, purchase_cost
from fe_asset;

v_char number(10);
v_char1 number(10);
v_len number(10);
v_ret varchar2(15);
v_num number(10);

p_char varchar2(2);

v_char10 number(10);
v_char11 number(10);
v_char12 number(10);
v_char13 number(10);
v_char14 number(10);
v_char15 number(10);
v_char16 number(10);
v_char17 number(10);
v_char18 number(10);

Begin
delete from padsm;
for x in c1 loop

Begin

If x.purchase_cost < 1000 then

insert into padsm(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,20000);

else


select nvl(to_number(substr(ASSET_DESCRIPTION,1,INSTR (ASSET_DESCRIPTION,' ',1, 1)-1)),1)
into v_char
from fe_asset where asset_id = x.asset_id;

If ((x.purchase_cost/v_char) < 1000) then --or (x.purchase_cost < 1000) then


insert into padsm(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);

elsif substr(x.ASSET_DESCRIPTION,1,INSTR (x.ASSET_DESCRIPTION,' ',1, 1)-1) = '0' then

v_char := 1;

If ((x.purchase_cost/v_char) < 1000) THEN--or (x.purchase_cost < 1000) then

insert into padsm(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);

End if;

End if;


END IF;
commit;

Exception

when INVALID_NUMBER then

select length(x.ASSET_DESCRIPTION) into v_len from fe_asset
where asset_id = x.asset_id;

for ctr in 1..v_len loop

v_num := ascii(substr(x.ASSET_DESCRIPTION,ctr,1));

if v_num = 48 then

v_char := 0;
elsif v_num = 49 then
v_char10 := 1;
elsif v_num = 50 then
v_char11 := 2;
elsif v_num = 51 then
v_char12 := 3;
elsif v_num = 52 then
v_char13 := 4;
elsif v_num = 53 then
v_char14 := 5;
elsif v_num = 54 then
v_char15 := 6;
elsif v_num = 55 then
v_char16 := 7;
elsif v_num = 56 then
dbms_output.put_line('This is for checking v_char'||v_char||x.asset_id);
v_char17 := 8;
elsif v_num = 57 then
v_char18 := 9;
else p_char := 'C';

end if;

--v_char:= to_number(v_char||v_char10||v_char11||v_char12||v_char13||v_char14||v_char15||v_char16||v_char16||v_ char17);

end loop;

v_char:= to_number(v_char||v_char10||v_char11||v_char12||v_char13||v_char14||v_char15||v_char16||v_char16||v_ char17);

If p_char = 'C' then

v_char := 1;

end if;


If (x.purchase_cost/v_char) < 1000 then
insert into padsm(asset_id,ASSET_description,purchase_cost,count) values
(x.asset_id,x.asset_description,x.purchase_cost,v_char);
end if;
commit;

WHEN value_error THEN
dbms_output.put_line('This is a value_error in inner exception');

when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);


End;

End loop;
commit;

Exception
when INVALID_NUMBER then
dbms_output.put_line('This is a char in outer loop');

WHEN value_error THEN
dbms_output.put_line('This is a VALUE_ERROR IN outer loop');

when others then
dbms_output.put_line('This is the last alter in outer loop');

End;

******************************