Casting error oracle

Question

I have tried both[CAST & TO_NUMBER] method but non of worked to cast values that is returned from another procedure.

SELECT DBMS_LOB.SUBSTR(SUBSTR(DETAIL_LOB, 2300, 4), 2000) 
  INTO RES 
FROM lONG_TO_CLOB_TEMP_TABLE;`

RES VALUE IS 40 returned by above query.

RES datatype is varchar2.

Now I want to convert that RES value into number so that I can do some arithmetic operation with this.

like => 100/RES

Not working code =>

V_TEMP := CAST(RES AS NUMBER)
V_TEMP := TO_NUMBER(RES)

Error Message => "ORA-06502: PL/SQL: numeric or value error"

@KaushikNayak I am searching for a particular value from the DETAIL column that's why using the SUBSTR also. — D.J., Oct 30 '17 at 14:27
Are you certain that the numeric error is on the cast and not the actual division operation you're performing in your code? — abhi, Oct 30 '17 at 14:31
It is highly likely then that you have other characters apart from "40" that is getting fetched, a newline maybe .. OR your `V_TEMP` variable is of shorter length. — Kaushik Nayak, Oct 30 '17 at 14:32
@abhi now I am not performing any division operation just trying to cast after success I will do. — D.J., Oct 30 '17 at 14:34
@KaushikNayak seems you are right. Can you help me how to remove that new line. — D.J., Oct 30 '17 at 14:36
Check this link : https://stackoverflow.com/questions/16407135/oracle-regexp-to-replace-n-r-and-t-with-space — Kaushik Nayak, Oct 30 '17 at 14:41

score 0 · Answer 1 · answered Oct 30 '17 at 18:14

0

Its most likely special character involve in (space or line break)

Try

V_TEM := To_number(regexp_replace(trim(RES), '(^[[:space:]]+)|([[:space:]]+$)',null))

answered Oct 30 '17 at 18:14

Nipun Alahakoon

2,772
5
27
45

score 0 · Answer 2 · answered Oct 31 '17 at 13:21

0

There was a line break causing the problem.

Solved with:-

REPLACE(REPLACE(REPLACE(column_name, CHR(10)), CHR(13)), CHR(9));

--Special thanks to @KaushikNayak

answered Oct 31 '17 at 13:21

D.J.

376
4
16

score -1 · Answer 3 · edited Oct 30 '17 at 18:49

In order to remove a newline character using PL/SQL, use the following:

declare charwithnewline VARCHAR2(10) := CONCAT('190', CHR(13));
begin
dbms_output.put_line('+++++++++++++++++++++++++');
dbms_output.put_line(charwithnewline);
dbms_output.put_line('+++++++++++++++++++++++++');
dbms_output.put_line(REPLACE(charwithnewline,CHR(13),''));
dbms_output.put_line('+++++++++++++++++++++++++');
end;

Note: The newline character could be CHR(13) or CHR(10) or a combination of these two characters. The logic is the same.

Casting error oracle

3 Answers3