ORA-01722: Invalid Number

[junOOni]

Hello,

I am writing a stored procedure. I am selecting a column from a table that contains both alphanumeric data. I have to extract just the numeric data from it and then use it in another query.

I have created a separate sp just for this purpose now. I will include this code in my WHOLE sp.

----

CREATE OR REPLACE Procedure sp_isNumericTest

AS

BEGIN

Declare
strCreatetmptbl long;
strInsert long;
strUpdate long;

Begin

strCreatetmptbl :='CREATE TABLE GROVERADM.tmp_tbl_results (
DOC_ID numeric (18, 0),
Result varchar2 (255)
)';

execute immediate strCreatetmptbl;

-- getting only numeric including "," and "."--
strInsert := 'Insert into tmp_tbl_results
(Select doc_id, result_txt from doc_details where
detail_cd = 100 and result_txt is NOT NULL and LENGTH(TRIM(TRANSLATE(result_txt,'' .,0123456789'','' ''))) is null
)';
execute immediate strInsert;

-- getting rid of extra spaces --
strUpdate := 'Update tmp_tbl_results
Set result = Trim(result)
where instr(result,'' '') <> 0
';
execute immediate strUpdate;

-- Making sure there is no ',' in result column. Converting ',' to '.' --

strUpdate := 'Update tmp_tbl_results
Set result = REPLACE(result,'','',''.'')
where INSTR(result,'','') <> 0
';
execute immediate strUpdate;

-- COMMENT for board: At this point i would like to get data where resullt is < 4.0. THe data in 'result' can be 4 or 4.0. I first tried getting records less than 4 on tmp_tbl_results but was getting INvalid number error here.
SO i created another table:

strCreatetmptbl :='CREATE TABLE GROVERADM.tmp_tbl_results2 (
ACR_ID numeric (18, 0),
Result numeric (18,2)
)';

execute immediate strCreatetmptbl;

strInsert := 'Insert into tmp_tbl_results2
(Select * from tmp_tbl_results
)';
execute immediate strInsert;

end;
end;

-----------

COMMENT FOR BOARD: I still get the same error when inserting.

When i do a query like:

Select * from tmp_tbl_results where results = 8.5
I get 60 records then it fails with the same invalid number error.

I can't do < > at all.

I am not really sure where the problem is in the data. I have about 40000 records so its a bit hard to find.

Any ideas pointers?
Thanks!!

[stecal]

If one of your results is 8.5, then how will you account for numbers that fail to meet the precision of DOC_ID numeric (18, 0)?

[junOOni]

doc_id doesn't get any decimal values. 8.5 will be in the results column, which is either a varchar2 (255) or numeric (18,2).